Friday Purchases II - Practice Coding Problems

Friday Purchases II - Problem

Database Hard

Given a table Purchases with user purchases for November 2023, calculate the total spending by users on each Friday of every week in November 2023.

Key requirements:

Focus only on Friday purchases for each week
If no purchases on a Friday, return 0 for that week
Return results ordered by week of month ascending
Purchase dates range from November 1-30, 2023

Table Schema

Purchases

Column Name	Type	Description
`user_id` PK	int	User identifier
`purchase_date` PK	date	Date of purchase (November 1-30, 2023)
`amount_spend` PK	int	Amount spent on purchase

Primary Key: (user_id, purchase_date, amount_spend)

Note: Composite primary key ensures unique purchase records

Input & Output

Example 1 — Basic Friday Purchases

Input Table:

user_id	purchase_date	amount_spend
11	2023-11-01	1126
1	2023-11-02	1152
15	2023-11-03	1201
17	2023-11-10	1110
12	2023-11-10	1503
1	2023-11-17	1188

Output:

week_of_month	total_amount
1	1201
2	2613
3	1188
4	0

💡 Note:

November 2023 Fridays are: 3rd (week 1), 10th (week 2), 17th (week 3), 24th (week 4). Week 1 has one purchase ($1201), week 2 has two purchases ($1110 + $1503 = $2613), week 3 has one purchase ($1188), and week 4 has no purchases (0).

Example 2 — No Friday Purchases

Input Table:

user_id	purchase_date	amount_spend
1	2023-11-01	500
2	2023-11-02	300
3	2023-11-04	200

Output:

week_of_month	total_amount
1	0
2	0
3	0
4	0

💡 Note:

All purchases are on non-Friday dates, so each Friday week shows 0 total spending. This demonstrates the requirement to show 0 for weeks without Friday purchases.

Constraints

1 ≤ user_id ≤ 100
purchase_date is between '2023-11-01' and '2023-11-30'
1 ≤ amount_spend ≤ 5000

Visualization

Tap to expand

Understanding the Visualization

Generate

Create all Friday dates for November

Join

LEFT JOIN with purchase data

Aggregate

Sum spending by week, handle NULLs

Key Takeaway

🎯 Key Insight: Use date generation with LEFT JOIN to ensure complete weekly coverage, even for periods with no data

Asked in

A Amazon 12 M Microsoft 8 G Google 6

Use CTE to generate Friday dates in November 2023, then LEFT JOIN with purchases to ensure weeks without Friday sales show 0. Use COALESCE to handle NULL sums.

Table Schema

Purchases

Column Name	Type	Description
`user_id` PK	int	User identifier
`purchase_date` PK	date	Date of purchase (November 1-30, 2023)
`amount_spend` PK	int	Amount spent on purchase

Primary Key: (user_id, purchase_date, amount_spend)

Note: Composite primary key ensures unique purchase records

Common Approaches

Approach	Time	Space	Notes
✓ CTE with Date Generation	O(n)	O(1)	Generate all November Fridays using CTE, then LEFT JOIN with purchases

CTE with Date Generation — Algorithm Steps

Step 1: Generate all Friday dates in November 2023 using CTE
Step 2: LEFT JOIN with Purchases to include weeks with no sales
Step 3: Sum spending per week and order by week number

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate

Create all Friday dates

Join

LEFT JOIN with purchases

Sum

Calculate weekly totals

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through purchases with constant Friday dates

✓ Linear Growth

Space Complexity

O(1)

Only storing 4-5 Friday dates for November

✓ Linear Space

18.5K Views

Medium Frequency

~20 min Avg. Time

485 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with Date Generation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler