Meta·Software Engineer·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Meta data engineer technical screen, one question, all SQL. The problem looked manageable at first glance but the ISO week logic and consecutive-week pairing made it a lot messier than a typical aggregation query.

Questions Asked (1)

Given a purchases table with customer_id, purchase_date, and rented_copies, write a single SQL query that: filters to calendar year 2024, uses ISO weeks (Monday to Sunday), finds customers who purchased in at least two consecutive full weeks, and for each such consecutive-week pair returns the customer with the highest total rented_copies in the first week of the pair, including ties.

Data ModelingAlgorithms & Data StructuresTechnical Trade-offs

Author's notes

The part that tripped me up was defining 'consecutive' cleanly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into stages: first aggregate purchases by customer and ISO week, then identify consecutive week pairs using window functions, and finally rank customers within each pair's first week by total rented_copies. Use a single query with CTEs to keep the logic modular and readable.

Pro tip: Explicitly state your assumptions about ISO week handling (e.g., using EXTRACT(ISOYEAR FROM date) and EXTRACT(WEEK FROM date)) and clarify that 'full weeks' means weeks entirely within 2024. This shows attention to edge cases and prevents ambiguity.

1. Filter and aggregate by ISO week

Filter purchases to calendar year 2024 and compute ISO year and week for each purchase_date. Aggregate total rented_copies per customer per ISO week.

2. Identify consecutive week pairs

Use window functions like LAG to compare each week to the previous week for the same customer, checking if the week numbers are consecutive (handling year boundaries).

3. Rank customers within first week of each pair

For each consecutive-week pair, rank customers by their total rented_copies in the first week using RANK() or DENSE_RANK() to include ties.

4. Select top customers and output

Filter to rows where the rank is 1, returning customer_id, the first week's start date (or ISO week), and the total rented_copies for that week.

Key Points to Mention

Use of EXTRACT(ISOYEAR FROM purchase_date) and EXTRACT(WEEK FROM purchase_date) to correctly handle ISO weeks, especially around year boundaries.
Aggregation with SUM(rented_copies) grouped by customer and ISO week.
Window functions (LAG) to detect consecutive weeks, ensuring the pair consists of full weeks within 2024.
Ranking with RANK() or DENSE_RANK() to include ties for the highest total rented_copies in the first week.
Use of CTEs to structure the query logically and improve readability.
Consideration of edge cases: weeks spanning year boundaries, customers with multiple purchases in a week, and ties in rented_copies.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.