Loading problem...
A retail analytics group needs a weekly Friday spending snapshot for month-end reporting.
You are given the table Purchases:
All purchase_date values fall within November 2023.
Task: For every Friday in November 2023, return total customer spending on that date. If no purchases happened on a Friday, include that Friday with total_amount = 0.
Use this week definition:
Output columns (exact order):
Sort by week_of_month in ascending order.
Supported submission environments:
Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 8 | 2023-11-03 | 5117 |
| 11 | 2023-11-07 | 1126 |
| 10 | 2023-11-12 | 8266 |
| 12 | 2023-11-24 | 9692 |
| 13 | 2023-11-24 | 12000 |
| 15 | 2023-11-30 | 7473 |[
{"week_of_month":1,"purchase_date":"2023-11-03","total_amount":5117},
{"week_of_month":2,"purchase_date":"2023-11-10","total_amount":0},
{"week_of_month":3,"purchase_date":"2023-11-17","total_amount":0},
{"week_of_month":4,"purchase_date":"2023-11-24","total_amount":21692}
]Explanation
Only 2023-11-03 and 2023-11-24 have Friday purchases. The other two Fridays in the month must still appear with zero totals.
Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 1 | 2023-11-01 | 100 |
| 2 | 2023-11-02 | 200 |
| 3 | 2023-11-04 | 300 |
| 4 | 2023-11-30 | 400 |[
{"week_of_month":1,"purchase_date":"2023-11-03","total_amount":0},
{"week_of_month":2,"purchase_date":"2023-11-10","total_amount":0},
{"week_of_month":3,"purchase_date":"2023-11-17","total_amount":0},
{"week_of_month":4,"purchase_date":"2023-11-24","total_amount":0}
]Explanation
There are no Friday transactions, so every Friday row is present with total 0.
Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 101 | 2023-11-03 | 2500 |
| 101 | 2023-11-03 | 1800 |
| 202 | 2023-11-10 | 700 |
| 303 | 2023-11-10 | 1300 |
| 404 | 2023-11-17 | 4500 |
| 505 | 2023-11-24 | 999 |
| 606 | 2023-11-24 | 1 |[
{"week_of_month":1,"purchase_date":"2023-11-03","total_amount":4300},
{"week_of_month":2,"purchase_date":"2023-11-10","total_amount":2000},
{"week_of_month":3,"purchase_date":"2023-11-17","total_amount":4500},
{"week_of_month":4,"purchase_date":"2023-11-24","total_amount":1000}
]Explanation
All rows on the same Friday are summed across users and transactions.
Constraints