Friday Purchase III - Problem
You are given two tables: Purchases and Users.
The Purchases table contains information about user purchases in November 2023, with columns for user_id, purchase_date, and amount_spend.
The Users table contains user membership information with user_id and membership type ('Standard', 'Premium', 'VIP').
Task: Calculate the total spending by Premium and VIP members on each Friday of every week in November 2023.
Requirements:
- Only include Premium and VIP members (exclude Standard)
- Only include purchases made on Fridays
- Group results by week of month and membership type
- Show 0 for weeks/membership combinations with no purchases
- Order by week_of_month and membership (ascending)
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
Users
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | User identifier |
membership
|
enum | Membership type: 'Standard', 'Premium', or 'VIP' |
Primary Key: user_id
Input & Output
Example 1 — November 2023 Friday Purchases
Input Tables:
Purchases
| 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
| 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 |
💡 Note:
The solution filters for Friday purchases by Premium/VIP members across November 2023 weeks:
- Week 1 (Nov 3): Premium member spent $1,126, no VIP purchases
- Week 2 (Nov 10): VIP member spent $7,473, no Premium purchases
- Week 3 (Nov 17): Only Standard member purchase (excluded)
- Week 4 (Nov 24): Premium: $5,117, VIP: $9,692 + $5,241 = $14,933
Example 2 — No Premium/VIP Friday Purchases
Input Tables:
Purchases
| user_id | purchase_date | amount_spend |
|---|---|---|
| 17 | 2023-11-03 | 1000 |
| 10 | 2023-11-10 | 2000 |
Users
| user_id | membership |
|---|---|
| 17 | Standard |
| 10 | Standard |
Output:
| week_of_month | membership | total_amount |
|---|---|---|
| 1 | Premium | 0 |
| 1 | VIP | 0 |
| 2 | Premium | 0 |
| 2 | VIP | 0 |
| 3 | Premium | 0 |
| 3 | VIP | 0 |
| 4 | Premium | 0 |
| 4 | VIP | 0 |
💡 Note:
When only Standard members make Friday purchases, all Premium and VIP totals show 0. The cross join approach ensures all 8 week-membership combinations appear in results.
Constraints
-
1 ≤ user_id ≤ 1000 -
purchase_dateis between'2023-11-01'and'2023-11-30' -
1 ≤ amount_spend ≤ 100000 -
membershipis one of('Standard', 'Premium', 'VIP')
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code