November Friday Spending Rollup

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Purchases =

[{"user_id":8,"purchase_date":"2023-11-03","amount_spend":5117},{"user_id":11,"purchase_date":"2023-11-07","amount_spend":1126},{"user_id":10,"purchase_date":"2023-11-12","amount_spend":8266},{"user_id":12,"purchase_date":"2023-11-24","amount_spend":9692},{"user_id":13,"purchase_date":"2023-11-24","amount_spend":12000},{"user_id":15,"purchase_date":"2023-11-30","amount_spend":7473}]

November Friday Spending Rollup

Hints

November Friday Spending Rollup

Hints