Skip to main content
Talentd

Friday Purchases II - Solution & Explanation

HardPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

We can generate a table T that contains all dates in November 2023 using recursion, then use a left join to connect T and the Purchases table by date. Finally, group and sum according to the requirements of the problem.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Recursive CTE + LEFT JOIN + Date FunctionsO(n + f)O(f)Best general solution when you must include Fridays with zero purchases
Direct Aggregation by Date FilterO(n)O(1)Works only when you do not need missing Fridays in the output

Video Solution

Leetcode HARD 2994 - RECURSIVE Common Table Expressions in SQL Explained | Everyday Data Science • Everyday Data Science • 328 views views

Frequently Asked Questions

Is Friday Purchases II easy or hard?
Friday Purchases II is classified as a Hard database problem. The difficulty comes from generating missing calendar rows using recursion and combining them with aggregated results through LEFT JOIN operations.
Friday Purchases II Python/Java solution
This is primarily a SQL database problem, so Python or Java implementations usually rely on executing the SQL query directly against the database. The core logic—generating Fridays and joining aggregated purchase totals—is handled inside the SQL query.
How to solve Friday Purchases II in O(n)?
Aggregate purchase totals by date while filtering rows that fall on Friday using a function like DAYOFWEEK(). However, this O(n) approach only returns Fridays that actually contain purchases. To include missing Fridays, you must generate the date series using a recursive CTE and perform a LEFT JOIN.
Is Friday Purchases II asked at Google/Amazon/Meta?
Database reporting questions like this commonly appear in SQL interviews at companies such as Amazon, Meta, and analytics-focused roles at Google. They test your ability to generate date sequences, aggregate data, and handle missing rows using joins.
What is the best approach for Friday Purchases II ?
The most reliable approach uses a recursive CTE to generate all Fridays in the required date range and then joins purchase totals using a LEFT JOIN. This ensures Fridays with no purchases still appear in the result. The overall complexity is O(n + f), where n is the number of purchase records and f is the number of Fridays generated.
What data structure is used in Friday Purchases II ?
The problem relies on relational database structures and SQL query constructs rather than traditional data structures. Key techniques include recursive CTEs, aggregation with GROUP BY, LEFT JOIN operations, and built-in date functions.
What is the time complexity of Friday Purchases II ?
The optimal SQL solution runs in O(n + f) time. The purchase table is scanned once for aggregation (O(n)), while the recursive CTE generates each Friday exactly once (O(f)). The join between these datasets is efficient because the Friday sequence is small.

Ready to solve this problem?

Practice Friday Purchases II with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary