November Friday Spending Rollup

Hard

A retail analytics group needs a weekly Friday spending snapshot for month-end reporting. You are given the table Purchases: user_id: customer identifier purchase_date: transaction date amount_spend: transaction amount 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: week_of_month = CEIL(day_of_month / 7) Output columns (exact order): week_of_month purchase_date total_amount Sort by week_of_month in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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         |

Output

[
  {"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.

Example 2

Input

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          |

Output

[
  {"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.

Example 3

Input

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            |

Output

[
  {"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

1 <= number of rows in Purchases <= 200000
1 <= user_id <= 1000000000
2023-11-01 <= purchase_date <= 2023-11-30
1 <= amount_spend <= 20000
(user_id, purchase_date, amount_spend) is unique per row
Return exactly one row for each Friday in November 2023
Use week_of_month = CEIL(day_of_month / 7)
SQL submissions are evaluated with SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with columns [week_of_month, purchase_date, total_amount]

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} ]

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} ]

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} ]