Friday Purchases II - Practice Coding Problems

Friday Purchases II - Problem

Database Hard

Friday Purchases II - Master SQL Window Functions & Date Analysis

You're analyzing shopping patterns for Black Friday preparation! Given a Purchases table containing user transactions throughout November 2023, your mission is to calculate total spending on each Friday of every week.

Table Schema:
Purchases
+---------------+------+
| Column Name | Type |
+---------------+------+
| user_id | int |
| purchase_date | date |
| amount_spend | int |
+---------------+------+

Key Requirements:
• Focus only on Fridays in November 2023
• Calculate total spending per Friday by week
• If no purchases occurred on a Friday, show 0
• Return results ordered by week of month (ascending)

This problem tests your ability to work with date functions, conditional aggregation, and generating missing data points - essential skills for real-world analytics!

Input & Output

example_1.sql — Basic Friday Spending

$ Input: Purchases: | user_id | purchase_date | amount_spend | |---------|---------------|---------------| | 11 | 2023-11-03 | 200 | | 15 | 2023-11-10 | 500 | | 11 | 2023-11-03 | 300 | | 17 | 2023-11-17 | 100 |

› Output: | week_of_month | total_amount | |---------------|---------------| | 1 | 500 | | 2 | 500 | | 3 | 100 | | 4 | 0 |

💡 Note: Week 1 (Nov 3): Users 11 spent $200 + $300 = $500. Week 2 (Nov 10): User 15 spent $500. Week 3 (Nov 17): User 17 spent $100. Week 4 (Nov 24): No purchases, so $0.

example_2.sql — Missing Friday Weeks

$ Input: Purchases: | user_id | purchase_date | amount_spend | |---------|---------------|---------------| | 12 | 2023-11-01 | 400 | | 13 | 2023-11-03 | 150 | | 14 | 2023-11-24 | 250 |

› Output: | week_of_month | total_amount | |---------------|---------------| | 1 | 150 | | 2 | 0 | | 3 | 0 | | 4 | 250 |

💡 Note: Only Nov 3 (Friday, Week 1) and Nov 24 (Friday, Week 4) had purchases. Nov 1 was a Wednesday, so it's filtered out. Weeks 2 and 3 had no Friday purchases.

example_3.sql — Edge Case: All Empty Fridays

$ Input: Purchases: | user_id | purchase_date | amount_spend | |---------|---------------|---------------| | 20 | 2023-11-01 | 300 | | 21 | 2023-11-02 | 400 | | 22 | 2023-11-06 | 500 |

› Output: | week_of_month | total_amount | |---------------|---------------| | 1 | 0 | | 2 | 0 | | 3 | 0 | | 4 | 0 |

💡 Note: None of the purchase dates (Nov 1, 2, 6) fall on Fridays. All Friday weeks show $0 spending, demonstrating the importance of handling missing data.

Constraints

purchase_date ranges from November 1, 2023 to November 30, 2023 inclusive
1 ≤ user_id ≤ 10³
1 ≤ amount_spend ≤ 10⁶
The table may contain multiple purchases by the same user on the same date
Results must be ordered by week_of_month in ascending order

Visualization

Tap to expand

Understanding the Visualization

Create Friday Calendar

Generate all 4 Friday dates in November 2023

Calculate Week Numbers

Use mathematical formula to determine week positions

Aggregate Real Data

Sum all Friday purchases by date

Join & Fill Gaps

LEFT JOIN calendar with data, COALESCE NULLs to 0

Key Takeaway

🎯 Key Insight: Generate all Friday dates upfront using CTEs, then LEFT JOIN with purchase data to guarantee complete week coverage with automatic zero-filling for missing data.

Asked in

a Amazon 45 f Meta 38 G Google 32 ⊞ Microsoft 28

The optimal solution uses Common Table Expressions (CTEs) to generate all November Fridays first, then performs a LEFT JOIN with aggregated purchase data. Key insights: Use FLOOR((DAY(date)-1)/7)+1 for week calculation and COALESCE to handle missing Friday weeks with zeros.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Row-by-Row Processing)	O(n²)	O(n)	Process each transaction individually and manually group by Friday weeks
Optimized CTE with Date Generation (Optimal)	O(n)	O(1)	Generate all November Fridays first, then LEFT JOIN with aggregated purchase data

Brute Force (Row-by-Row Processing) — Algorithm Steps

Scan each purchase record in the table
Check if purchase_date falls on a Friday using DAYOFWEEK()
Calculate which week of November using complex date arithmetic
Group results and sum amounts
Manually insert zeros for missing Friday weeks

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Records

Examine each purchase record individually

Check Friday

Use DAYOFWEEK() to verify if date is Friday

Calculate Week

Determine which week of November using CASE statements

Aggregate

Group by week and sum amounts

Fill Gaps

Manually add zeros for missing Friday weeks

Code -

solution.c — C

// Brute Force SQL Approach
const char* query = "\
SELECT \
    week_of_month, \
    COALESCE(total_amount, 0) as total_amount \
FROM ( \
    SELECT 1 as week_of_month UNION ALL \
    SELECT 2 UNION ALL SELECT 3 UNION ALL \
    SELECT 4 UNION ALL SELECT 5 \
) weeks \
LEFT JOIN ( \
    SELECT \
        CASE \
            WHEN purchase_date <= '2023-11-03' THEN 1 \
            WHEN purchase_date <= '2023-11-10' THEN 2 \
            WHEN purchase_date <= '2023-11-17' THEN 3 \
            WHEN purchase_date <= '2023-11-24' THEN 4 \
            ELSE 5 \
        END as week_of_month, \
        SUM(amount_spend) as total_amount \
    FROM Purchases \
    WHERE DAYOFWEEK(purchase_date) = 6 \
      AND purchase_date BETWEEN '2023-11-01' AND '2023-11-30' \
    GROUP BY week_of_month \
) friday_sales ON weeks.week_of_month = friday_sales.week_of_month \
ORDER BY week_of_month;";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Each row requires date calculations plus grouping operations

⚠ Quadratic Growth

Space Complexity

O(n)

Stores intermediate results for each processed transaction

⚡ Linearithmic Space

34.4K Views

High Frequency

~25 min Avg. Time

1.5K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Row-by-Row Processing) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler