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.
Quick Answer: 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.