Friday Membership Weekly Spend Matrix

Medium

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 1

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 2

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 3

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.

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]

Loading problem...

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

Friday Membership Weekly Spend Matrix

Medium

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 1

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 2

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 3

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.

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]