00:00:00

Description

Editorial

Friday Spend Snapshot by Week

EASY10 pts

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:
1. week_of_month
2. purchase_date
3. 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 |
|---------|---------------|--------------|
| 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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Purchases =

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

Loading problem...

101

00:00:00

Description

Editorial

Friday Spend Snapshot by Week

EASY10 pts

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:
1. week_of_month
2. purchase_date
3. 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 |
|---------|---------------|--------------|
| 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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Purchases =

Friday Spend Snapshot by Week

Hints

Friday Spend Snapshot by Week

Hints