Watch the video solution for Friday Purchases II , a hard level problem involving Database. This walkthrough by Everyday Data Science has 328 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Purchases
+---------------+------+ | Column Name | Type | +---------------+------+ | user_id | int | | purchase_date | date | | amount_spend | int | +---------------+------+ (user_id, purchase_date, amount_spend) is the primary key (combination of columns with unique values) for this table. purchase_date will range from November 1, 2023, to November 30, 2023, inclusive of both dates. Each row contains user id, purchase date, and amount spend.
Write a solution to calculate the total spending by users on each Friday of every week in November 2023. If there are no purchases on a particular Friday of a week, it will be considered as 0.
Return the result table ordered by week of month in ascending order.
The result format is in the following example.
Example 1:
Input: Purchases table: +---------+---------------+--------------+ | user_id | purchase_date | amount_spend | +---------+---------------+--------------+ | 11 | 2023-11-07 | 1126 | | 15 | 2023-11-30 | 7473 | | 17 | 2023-11-14 | 2414 | | 12 | 2023-11-24 | 9692 | | 8 | 2023-11-03 | 5117 | | 1 | 2023-11-16 | 5241 | | 10 | 2023-11-12 | 8266 | | 13 | 2023-11-24 | 12000 | +---------+---------------+--------------+ Output: +---------------+---------------+--------------+ | week_of_month | purchase_date | total_amount | +---------------+---------------+--------------+ | 1 | 2023-11-03 | 5117 | | 2 | 2023-11-10 | 0 | | 3 | 2023-11-17 | 0 | | 4 | 2023-11-24 | 21692 | +---------------+---------------+--------------+ Explanation: - During the first week of November 2023, transactions amounting to $5,117 occurred on Friday, 2023-11-03. - For the second week of November 2023, there were no transactions on Friday, 2023-11-10, resulting in a value of 0 in the output table for that day. - Similarly, during the third week of November 2023, there were no transactions on Friday, 2023-11-17, reflected as 0 in the output table for that specific day. - In the fourth week of November 2023, two transactions took place on Friday, 2023-11-24, amounting to $12,000 and $9,692 respectively, summing up to a total of $21,692. Output table is ordered by week_of_month in ascending order.
Problem Overview: You need to report purchase totals for every Friday in a given range, even when no purchases happened on that day. The tricky part is generating the complete set of Fridays first, then attaching purchase data to them.
Approach 1: Recursion + LEFT JOIN + Date Functions (O(n + f) time, O(f) space)
This solution generates all Fridays using a recursive CTE, then attaches purchase totals using a LEFT JOIN. The recursion starts from the first Friday in the required date range and repeatedly adds INTERVAL 7 DAY until the end boundary. This produces a continuous sequence of Fridays, including weeks with no purchases.
Next, aggregate purchases by date using DATE() or similar functions and join that dataset with the generated Friday sequence. The LEFT JOIN guarantees that missing purchase days still appear in the result set. Use COALESCE() to convert NULL totals into zero.
Date functions such as DAYOFWEEK(), DATE_ADD(), and DATE_SUB() help compute the first valid Friday and move forward weekly. The recursive CTE only produces the required Friday rows, so the join remains efficient. The query scans the purchase table once for aggregation and performs lightweight joins afterward.
This pattern is common in database problems where the output requires a complete calendar series. Instead of relying on existing rows, you explicitly generate the timeline and then attach aggregated results.
Recommended for interviews: The recursive CTE + LEFT JOIN approach is the expected solution. It demonstrates understanding of SQL aggregation, calendar generation, and handling missing rows with joins. Interviewers often look for this pattern in reporting-style questions, especially when dealing with weekly intervals and date functions.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Recursive CTE + LEFT JOIN + Date Functions | O(n + f) | O(f) | Best general solution when you must include Fridays with zero purchases |
| Direct Aggregation by Date Filter | O(n) | O(1) | Works only when you do not need missing Fridays in the output |
