Find customer with max rentals in consecutive weeks
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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 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.
Tables
purchases(customer_id INT, purchase_date DATE, rented_copies INT)
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.
Community answers
Answer by ginb
with weekly_purchases as ( select customer_id, date_trunc('week', purchase_date)::date as week,sum(rented_copies) as total_rented_copiesfrom purchaseswhere extract(year from purchase_date)=2024group by 1,2)
, lead_weekly_purchases as ( select customer_id, week,lead(week) over ( partition by customer_id order by week) as next_week,total_rented_copiesfrom weekly_purchases)
-- select * from lead_weekly_purchasesselect customer_id, week as week_start_date, total_rented_copies from lead_weekly_purchaseswhere next_week-week=7order by 3 desclimit 1
Answer by ginb
with weekly_purchases as (
select customer_id,
date_trunc('week', purchase_date)::date as week,
sum(rented_copies) as total_rented_copies
from purchases
where extract(year from purchase_date)=2024
group by 1,2
)
, lead_weekly_purchases as (
select customer_id,
week,
lead(week) over (
partition by customer_id order by week) as next_week,
total_rented_copies
from weekly_purchases
)
select customer_id,
week as week_start_date,
total_rented_copies from lead_weekly_purchases
where next_week-week=7
order by 3 desc
limit 1