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.
Table: Users
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| membership | enum |
+-------------+------+
user_id is the primary key for this table.
membership is an ENUM (category) type of ('Standard', 'Premium', 'VIP').
Each row of this table indicates the user_id, membership type.
Write a solution to calculate the total spending by Premium and VIP members on each Friday of every week in November 2023. If there are no purchases on a particular Friday by Premium or VIP members, it should be considered as 0.
Return the result table ordered by week of the month, and membership in ascending order.
The result format is in the following example.
Example:
Input:
Purchases table:
+---------+---------------+--------------+ | user_id | purchase_date | amount_spend | +---------+---------------+--------------+ | 11 | 2023-11-03 | 1126 | | 15 | 2023-11-10 | 7473 | | 17 | 2023-11-17 | 2414 | | 12 | 2023-11-24 | 9692 | | 8 | 2023-11-24 | 5117 | | 1 | 2023-11-24 | 5241 | | 10 | 2023-11-22 | 8266 | | 13 | 2023-11-21 | 12000 | +---------+---------------+--------------+
Users table:
+---------+------------+ | user_id | membership | +---------+------------+ | 11 | Premium | | 15 | VIP | | 17 | Standard | | 12 | VIP | | 8 | Premium | | 1 | VIP | | 10 | Standard | | 13 | Premium | +---------+------------+
Output:
+---------------+-------------+--------------+
| week_of_month | membership | total_amount |
+---------------+-------------+--------------+
| 1 | Premium | 1126 |
| 1 | VIP | 0 |
| 2 | Premium | 0 |
| 2 | VIP | 7473 |
| 3 | Premium | 0 |
| 3 | VIP | 0 |
| 4 | Premium | 5117 |
| 4 | VIP | 14933 |
+---------------+-------------+--------------+
Explanation:
Note: The output table is ordered by week_of_month and membership in ascending order.
Problem Overview: You need to report purchase totals for Fridays using SQL. The challenge is that some Fridays may have no purchases, so they still must appear in the output. The solution requires generating all relevant Fridays and joining them with the purchase data.
Approach 1: Recursion + Join (O(F + P) time, O(F) space)
This approach uses a recursive Common Table Expression (CTE) to generate every Friday in the target date range. The recursion repeatedly adds 7 days to the previous Friday until the upper bound is reached. Once the list of Fridays is created, a LEFT JOIN connects these generated dates with the purchases table. This ensures Fridays without purchases still appear in the result set.
The key insight is separating date generation from purchase aggregation. The recursive CTE builds a deterministic sequence of Fridays, while the join performs the aggregation with SUM(). Using a LEFT JOIN keeps missing purchase rows as NULL, which can be converted to zero with COALESCE(). This pattern is common in SQL analytics problems where missing time buckets must still be shown.
Conceptually, the process works in three steps. First, the recursive query generates all Friday dates. Second, the query joins these dates with purchase records that fall on the same day. Third, aggregation groups by the generated Friday and sums the purchase amount. The result is a continuous timeline of Fridays with correct totals.
This technique relies heavily on SQL recursion and relational joins. If you're reviewing similar database interview problems, study patterns involving recursion, SQL joins, and database aggregation. Many reporting-style SQL questions follow the same structure: generate a time dimension, join transactional data, and aggregate.
Recommended for interviews: The recursive CTE + join approach is the expected solution. It demonstrates that you understand time-series generation, outer joins, and aggregation in SQL. A naive approach that only groups existing purchase rows misses Fridays with zero purchases, while the recursive solution guarantees a complete timeline.
First, we create a recursive table T that includes a week_of_month column, representing the week of the month. Then we create a table M that includes a membership column, representing the type of membership, with values 'Premium' and 'VIP'.
Next, we create a table P that includes week_of_month, membership, and amount_spend columns, filtering out the amount spent by each member on Fridays of each week of the month. Finally, we join tables T and M, then left join table P, and group by week_of_month and membership columns to calculate the total spending of each type of member each week.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Group By Existing Purchases | O(P) | O(1) | When only Fridays with purchases need to be shown |
| Recursive CTE + LEFT JOIN | O(F + P) | O(F) | When every Friday must appear even if no purchases exist |
Leetcode MEDIUM 3118 - RECURSIVE CTE Concept in SQL - Friday Purchases 3 | Everyday Data Science • Everyday Data Science • 288 views views
Practice Friday Purchase III with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor