Friday Purchases II - Problem
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_dateis between'2023-11-01'and'2023-11-30' -
1 ≤ amount_spend ≤ 5000
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code