Friday Spend Snapshot by Week

Easy

An analytics team tracks customer spending transactions in a single table. Table: Purchases user_id: integer customer identifier purchase_date: transaction date amount_spend: integer amount spent in that transaction Primary key: (user_id, purchase_date, amount_spend) Task: Build a weekly Friday spending report for November 2023. For each Friday in November 2023: Sum amount_spend across all rows on that date. Return the week_of_month for that Friday. Keep only Fridays that actually have at least one purchase row. Definitions: week_of_month is based on calendar day buckets: days 1-7 -> week 1 days 8-14 -> week 2 days 15-21 -> week 3 days 22-28 -> week 4 days 29-31 -> week 5 Output requirements: Return columns exactly in this 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 |
|---------|---------------|--------------|
| 11      | 2023-11-07    | 1126         |
| 15      | 2023-11-30    | 7473         |
| 17      | 2023-11-14    | 2414         |
| 12      | 2023-11-24    | 9692         |
| 8       | 2023-11-03    | 5117         |
| 1       | 2023-11-16    | 5241         |
| 10      | 2023-11-12    | 8266         |
| 13      | 2023-11-24    | 12000        |

Output

[
  {"week_of_month":1,"purchase_date":"2023-11-03","total_amount":5117},
  {"week_of_month":4,"purchase_date":"2023-11-24","total_amount":21692}
]

Explanation

Only 2023-11-03 and 2023-11-24 are Fridays with transactions. The two rows on 2023-11-24 are aggregated.

Example 2

Input

Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 2       | 2023-11-03    | 100          |
| 3       | 2023-11-10    | 350          |
| 4       | 2023-11-17    | 900          |
| 5       | 2023-11-24    | 50           |

Output

[
  {"week_of_month":1,"purchase_date":"2023-11-03","total_amount":100},
  {"week_of_month":2,"purchase_date":"2023-11-10","total_amount":350},
  {"week_of_month":3,"purchase_date":"2023-11-17","total_amount":900},
  {"week_of_month":4,"purchase_date":"2023-11-24","total_amount":50}
]

Explanation

Each Friday appears once, so each total is the single row value for that date.

Example 3

Input

Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 7       | 2023-11-06    | 120          |
| 9       | 2023-11-11    | 450          |
| 10      | 2023-11-15    | 300          |

Output

[]

Explanation

No Friday rows are present, so no report rows are returned.

Constraints

1 <= number of rows in Purchases <= 100000
1 <= user_id <= 1000000000
purchase_date is a valid date
1 <= amount_spend <= 1000000000
(user_id, purchase_date, amount_spend) is unique
Primary reporting window is November 2023
Return exactly: week_of_month, purchase_date, total_amount
Include only Fridays with at least one purchase
Sort output by week_of_month ascending
For SQL, use SQLite 3.27.2 semantics
For Pandas, return a DataFrame with exact required columns

Purchases: | user_id | purchase_date | amount_spend | |---------|---------------|--------------| | 11 | 2023-11-07 | 1126 | | 15 | 2023-11-30 | 7473 | | 17 | 2023-11-14 | 2414 | | 12 | 2023-11-24 | 9692 | | 8 | 2023-11-03 | 5117 | | 1 | 2023-11-16 | 5241 | | 10 | 2023-11-12 | 8266 | | 13 | 2023-11-24 | 12000 |

Purchases: | user_id | purchase_date | amount_spend | |---------|---------------|--------------| | 2 | 2023-11-03 | 100 | | 3 | 2023-11-10 | 350 | | 4 | 2023-11-17 | 900 | | 5 | 2023-11-24 | 50 |

[ {"week_of_month":1,"purchase_date":"2023-11-03","total_amount":100}, {"week_of_month":2,"purchase_date":"2023-11-10","total_amount":350}, {"week_of_month":3,"purchase_date":"2023-11-17","total_amount":900}, {"week_of_month":4,"purchase_date":"2023-11-24","total_amount":50} ]

Purchases: | user_id | purchase_date | amount_spend | |---------|---------------|--------------| | 7 | 2023-11-06 | 120 | | 9 | 2023-11-11 | 450 | | 10 | 2023-11-15 | 300 |