Friday Purchases I - Problem
You are given a table Purchases containing user purchase data for November 2023.
Write a solution to calculate the total spending by users on each Friday of every week in November 2023.
Requirements:
- Output only weeks that include at least one purchase on a Friday
- Return results ordered by week of month in ascending order
- Show week number and total Friday spending
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: Each row represents a unique purchase transaction
Input & Output
Example 1 — Multiple Friday Purchases
Input Table:
| user_id | purchase_date | amount_spend |
|---|---|---|
| 13 | 2023-11-03 | 20 |
| 10 | 2023-11-10 | 30 |
| 17 | 2023-11-17 | 40 |
| 12 | 2023-11-01 | 50 |
| 15 | 2023-11-24 | 80 |
Output:
| week_of_month | total_amount |
|---|---|
| 1 | 20 |
| 2 | 30 |
| 3 | 40 |
| 4 | 80 |
💡 Note:
November 2023 Fridays are: 3rd (week 1), 10th (week 2), 17th (week 3), 24th (week 4). Each Friday's purchases are summed by week. Non-Friday purchases (like Nov 1st) are excluded.
Example 2 — Some Weeks Without Friday Purchases
Input Table:
| user_id | purchase_date | amount_spend |
|---|---|---|
| 11 | 2023-11-03 | 100 |
| 13 | 2023-11-03 | 200 |
| 12 | 2023-11-17 | 50 |
| 14 | 2023-11-02 | 75 |
| 15 | 2023-11-15 | 25 |
Output:
| week_of_month | total_amount |
|---|---|
| 1 | 300 |
| 3 | 50 |
💡 Note:
Only weeks 1 and 3 have Friday purchases. Week 1 has two Friday purchases totaling 300 (100+200), week 3 has one purchase of 50. Weeks 2 and 4 have no Friday purchases so are excluded from output.
Constraints
-
purchase_dateranges from November 1, 2023, to November 30, 2023 -
1 ≤ user_id ≤ 1000 -
1 ≤ amount_spend ≤ 1000
Visualization
Tap to expand
Understanding the Visualization
1
Filter
Extract only Friday purchases
2
Calculate
Determine week of month
3
Aggregate
Sum spending by week
Key Takeaway
🎯 Key Insight: Use date extraction functions to identify specific weekdays and calculate running weekly metrics
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code