0/304

00:00:00

Description

Editorial

Friday Membership Weekly Spend Matrix

MEDIUM20 pts

A commerce analytics team needs a compact Friday revenue matrix segmented by customer tier.

You are given two tables:

Purchases

user_id: customer identifier
purchase_date: transaction date
amount_spend: transaction amount

Users

user_id: customer identifier
membership: one of "Standard", "Premium", "VIP"

Task: For November 2023, report total spending made on Fridays only, but only for membership tiers Premium and VIP.

Use this week bucket definition:

week_of_month = CEIL(day_of_month / 7)

Required output matrix:

week_of_month in {1, 2, 3, 4}
membership in {"Premium", "VIP"}
one row for every combination (4 x 2 = 8 rows)

If a combination has no qualifying purchases, return total_amount = 0.

Output columns in exact order:

week_of_month
membership
total_amount

Sort by week_of_month ascending, then membership ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

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

Users:
| user_id | membership |
|---------|------------|
| 11      | Premium    |
| 15      | VIP        |
| 17      | Standard   |
| 12      | VIP        |
| 8       | Premium    |
| 1       | VIP        |
| 10      | Standard   |
| 13      | Premium    |

Output

[
  {"week_of_month":1,"membership":"Premium","total_amount":1126},
  {"week_of_month":1,"membership":"VIP","total_amount":0},
  {"week_of_month":2,"membership":"Premium","total_amount":0},
  {"week_of_month":2,"membership":"VIP","total_amount":7473},
  {"week_of_month":3,"membership":"Premium","total_amount":0},
  {"week_of_month":3,"membership":"VIP","total_amount":0},
  {"week_of_month":4,"membership":"Premium","total_amount":5117},
  {"week_of_month":4,"membership":"VIP","total_amount":14933}
]

Explanation

Only Friday purchases from Premium and VIP are counted. Standard rows and non-Friday rows are ignored. Missing matrix cells are zero-filled.

Example

Input

Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 101     | 2023-11-03    | 200          |
| 201     | 2023-11-03    | 300          |
| 101     | 2023-11-10    | 400          |
| 201     | 2023-11-10    | 500          |
| 101     | 2023-11-17    | 600          |
| 201     | 2023-11-17    | 700          |
| 101     | 2023-11-24    | 800          |
| 201     | 2023-11-24    | 900          |
| 301     | 2023-11-24    | 999          |

Users:
| user_id | membership |
|---------|------------|
| 101     | Premium    |
| 201     | VIP        |
| 301     | Standard   |

Output

[
  {"week_of_month":1,"membership":"Premium","total_amount":200},
  {"week_of_month":1,"membership":"VIP","total_amount":300},
  {"week_of_month":2,"membership":"Premium","total_amount":400},
  {"week_of_month":2,"membership":"VIP","total_amount":500},
  {"week_of_month":3,"membership":"Premium","total_amount":600},
  {"week_of_month":3,"membership":"VIP","total_amount":700},
  {"week_of_month":4,"membership":"Premium","total_amount":800},
  {"week_of_month":4,"membership":"VIP","total_amount":900}
]

Explanation

Each week has both target memberships. Standard membership purchases are excluded even when they happen on Fridays.

Example

Input

Purchases:
| user_id | purchase_date | amount_spend |
|---------|---------------|--------------|
| 1001    | 2023-11-01    | 110          |
| 1002    | 2023-11-02    | 120          |
| 1003    | 2023-11-04    | 130          |
| 1004    | 2023-11-30    | 140          |

Users:
| user_id | membership |
|---------|------------|
| 1001    | Premium    |
| 1002    | VIP        |
| 1003    | Premium    |
| 1004    | VIP        |

Output

[
  {"week_of_month":1,"membership":"Premium","total_amount":0},
  {"week_of_month":1,"membership":"VIP","total_amount":0},
  {"week_of_month":2,"membership":"Premium","total_amount":0},
  {"week_of_month":2,"membership":"VIP","total_amount":0},
  {"week_of_month":3,"membership":"Premium","total_amount":0},
  {"week_of_month":3,"membership":"VIP","total_amount":0},
  {"week_of_month":4,"membership":"Premium","total_amount":0},
  {"week_of_month":4,"membership":"VIP","total_amount":0}
]

Explanation

No purchases occur on Fridays, so every matrix cell is present with zero total.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Purchases <= 200000
1 <= number of rows in Users <= 100000
1 <= user_id <= 1000000000
2023-11-01 <= purchase_date <= 2023-11-30
1 <= amount_spend <= 50000
Purchases has primary key (user_id, purchase_date, amount_spend)
Users has primary key user_id
membership is one of Standard, Premium, VIP
Count only Friday purchases
Count only Premium and VIP memberships
Return all 8 required (week_of_month, membership) combinations
Output columns must be exactly [week_of_month, membership, total_amount]
Sort output by week_of_month ASC, membership ASC
SQL submissions are evaluated with SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with columns [week_of_month, membership, total_amount]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Users =

[{"user_id":11,"membership":"Premium"},{"user_id":15,"membership":"VIP"},{"user_id":17,"membership":"Standard"},{"user_id":12,"membership":"VIP"},{"user_id":8,"membership":"Premium"},{"user_id":1,"membership":"VIP"},{"user_id":10,"membership":"Standard"},{"user_id":13,"membership":"Premium"}]

Purchases =

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

Friday Membership Weekly Spend Matrix

Hints

Friday Membership Weekly Spend Matrix

Hints