Coding questions · Meta
Find customer with max rentals in consecutive weeks
The question evaluates temporal data manipulation and aggregation competencies in SQL/Python, focusing on ISO week alignment, consecutive-week logic, and the ability to handle tied top results.
You are given a table purchases(customer_id INT, purchase_date DATE, rented_copies INT). Consider only dates in calendar year 2024. Define a full week as an ISO week starting Monday and ending Sunday. Identify customers who made at least one purchase in two consecutive full weeks. For each such consecutive-week pair per customer, compute the total rented_copies in the first week of the pair. Return the customer_id(s) with the highest total_rented_copies in that first week, along with the week_start_date (Monday) and total_rented_copies. If there is a tie, return all ties. Write a single SQL query; you may use EXTRACT/DATE_TRUNC or equivalent.
Find customer with max rentals in consecutive ISO weeks
You are given a table purchases(customer_id INT, purchase_date DATE, rented_copies INT).
Consider only rows where purchase_date is in the calendar year 2024 (from '2024-01-01' to '2024-12-31'). Define a full week as an ISO week starting Monday and ending Sunday.
For each customer, identify all pairs of consecutive full weeks (i.e., weeks whose Monday start dates are exactly 7 days apart) in which the customer made at least one purchase in each of the two weeks. For every such consecutive-week pair, compute the total rented_copies in the first week of the pair.
From all these first-week totals (across all customers and week pairs), return the customer_id(s) with the highest total_rented_copies in that first week, along with the corresponding week_start_date (the Monday of that first week) and total_rented_copies. If there is a tie for the maximum total, return all tied rows.
Write a single SQL query to produce this result. You may use functions such as EXTRACT and DATE_TRUNC (or their equivalents) to work with weeks and week start dates.
Table purchases
| column | type | constraints |
|---|---|---|
| customer_id | INT | NOT NULL |
| purchase_date | DATE | NOT NULL |
| rented_copies | INT | NOT NULL |
Sample data:
| customer_id | purchase_date | rented_copies |
|---|---|---|
| 1 | 2024-01-01 | 1 |
| 1 | 2024-01-03 | 2 |
| 1 | 2024-01-09 | 4 |
| 1 | 2024-02-20 | 2 |
| 2 | 2024-01-08 | 2 |
| 2 | 2024-01-10 | 3 |
| 2 | 2024-01-17 | 1 |
| 2 | 2024-03-01 | 5 |
| 3 | 2024-02-05 | 4 |
| 3 | 2024-02-06 | 5 |
| 3 | 2024-02-12 | 1 |
| 3 | 2024-02-27 | 2 |
| 3 | 2024-03-05 | 3 |
| 4 | 2024-04-01 | 7 |
Expected output
The output lists the customer(s) who have the maximum total_rented_copies in the FIRST week of any consecutive-week pair where they made purchases in both weeks. Each row shows the customer_id, the Monday week_start_date of that first week, and the total_rented_copies in that week. Based on the sample data, customer 3 has the highest total (9) in the week starting 2024-02-05, which is followed by another week (starting 2024-02-12) in which the same customer also has purchases.
| customer_id | total_rented_copies | week_start_date |
|---|---|---|
| 3 | 9 | 2024-02-05 |
Hints
- First aggregate purchases by customer and ISO week (using a week-starting-Monday date such as DATE_TRUNC('week', purchase_date)).
- Use a window function like LEAD over each customer’s weeks to detect when the next week_start_date is exactly 7 days later, then filter to those pairs and take the maximum of the first-week totals.