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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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