OneNoughtOne
0/308
Loading content...
A commerce analytics team needs a compact Friday revenue matrix segmented by customer tier.
You are given two tables:
Purchases
Users
Task: For November 2023, report total spending made on Fridays only, but only for membership tiers Premium and VIP.
Use this week bucket definition:
Required output matrix:
If a combination has no qualifying purchases, return total_amount = 0.
Output columns in exact order:
Sort by week_of_month ascending, then membership ascending.
Supported submission environments:
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 |[
{"week_of_month":1,"membership":"Premium","total_amount":1126},
{"week_of_month":1,"membership":"VIP","total_amount":0},
{"week_of_month":2,"membership":"Premium","total_amount":0},
{"week_of_month":2,"membership":"VIP","total_amount":7473},
{"week_of_month":3,"membership":"Premium","total_amount":0},
{"week_of_month":3,"membership":"VIP","total_amount":0},
{"week_of_month":4,"membership":"Premium","total_amount":5117},
{"week_of_month":4,"membership":"VIP","total_amount":14933}
]Explanation
Only Friday purchases from Premium and VIP are counted. Standard rows and non-Friday rows are ignored. Missing matrix cells are zero-filled.
Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 101 | 2023-11-03 | 200 |
| 201 | 2023-11-03 | 300 |
| 101 | 2023-11-10 | 400 |
| 201 | 2023-11-10 | 500 |
| 101 | 2023-11-17 | 600 |
| 201 | 2023-11-17 | 700 |
| 101 | 2023-11-24 | 800 |
| 201 | 2023-11-24 | 900 |
| 301 | 2023-11-24 | 999 |
Users:
| user_id | membership |
|---------|------------|
| 101 | Premium |
| 201 | VIP |
| 301 | Standard |[
{"week_of_month":1,"membership":"Premium","total_amount":200},
{"week_of_month":1,"membership":"VIP","total_amount":300},
{"week_of_month":2,"membership":"Premium","total_amount":400},
{"week_of_month":2,"membership":"VIP","total_amount":500},
{"week_of_month":3,"membership":"Premium","total_amount":600},
{"week_of_month":3,"membership":"VIP","total_amount":700},
{"week_of_month":4,"membership":"Premium","total_amount":800},
{"week_of_month":4,"membership":"VIP","total_amount":900}
]Explanation
Each week has both target memberships. Standard membership purchases are excluded even when they happen on Fridays.
Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 1001 | 2023-11-01 | 110 |
| 1002 | 2023-11-02 | 120 |
| 1003 | 2023-11-04 | 130 |
| 1004 | 2023-11-30 | 140 |
Users:
| user_id | membership |
|---------|------------|
| 1001 | Premium |
| 1002 | VIP |
| 1003 | Premium |
| 1004 | VIP |[
{"week_of_month":1,"membership":"Premium","total_amount":0},
{"week_of_month":1,"membership":"VIP","total_amount":0},
{"week_of_month":2,"membership":"Premium","total_amount":0},
{"week_of_month":2,"membership":"VIP","total_amount":0},
{"week_of_month":3,"membership":"Premium","total_amount":0},
{"week_of_month":3,"membership":"VIP","total_amount":0},
{"week_of_month":4,"membership":"Premium","total_amount":0},
{"week_of_month":4,"membership":"VIP","total_amount":0}
]Explanation
No purchases occur on Fridays, so every matrix cell is present with zero total.
Constraints