Daily User Platform Spend Segmentation

Hard

A commerce analytics warehouse tracks user spend events in one table: spending columns: user_id: buyer identifier spend_date: purchase date in YYYY-MM-DD platform: purchase channel ('desktop' or 'mobile') amount: spend amount for that user/date/platform row Data guarantees: (user_id, spend_date, platform) is unique. A user can appear at most once per platform per day. Daily bucket definition (computed per user and date): both: user has at least one desktop row and one mobile row on the same spend_date desktop: user has desktop activity only on that spend_date mobile: user has mobile activity only on that spend_date Task: For every spend_date present in spending, return exactly three rows: desktop, mobile, both For each returned row compute: total_amount: sum of spend amount for users assigned to that bucket and date total_users: number of users assigned to that bucket and date If a bucket has no users on a date, return total_amount = 0 and total_users = 0 Output requirements: Return columns in this exact order: spend_date, platform, total_amount, total_users Sort by spend_date ascending and platform order: desktop, mobile, both Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

spending:
| user_id | spend_date  | platform | amount |
|---------|-------------|----------|--------|
| 1       | 2019-07-01  | mobile   | 120    |
| 1       | 2019-07-01  | desktop  | 80     |
| 2       | 2019-07-01  | mobile   | 40     |
| 3       | 2019-07-01  | desktop  | 60     |
| 2       | 2019-07-02  | mobile   | 70     |
| 3       | 2019-07-02  | desktop  | 30     |

Output

[
  {"spend_date":"2019-07-01","platform":"desktop","total_amount":60,"total_users":1},
  {"spend_date":"2019-07-01","platform":"mobile","total_amount":40,"total_users":1},
  {"spend_date":"2019-07-01","platform":"both","total_amount":200,"total_users":1},
  {"spend_date":"2019-07-02","platform":"desktop","total_amount":30,"total_users":1},
  {"spend_date":"2019-07-02","platform":"mobile","total_amount":70,"total_users":1},
  {"spend_date":"2019-07-02","platform":"both","total_amount":0,"total_users":0}
]

Explanation

On 2019-07-01, user 1 used both channels and contributes 200 to the both bucket. On 2019-07-02 there is no both user, so that row is still returned with zeros.

Example 2

Input

spending:
| user_id | spend_date  | platform | amount |
|---------|-------------|----------|--------|
| 10      | 2020-03-10  | desktop  | 300    |
| 11      | 2020-03-10  | desktop  | 70     |
| 12      | 2020-03-10  | desktop  | 30     |
| 10      | 2020-03-11  | mobile   | 90     |
| 11      | 2020-03-11  | mobile   | 60     |
| 12      | 2020-03-11  | desktop  | 50     |
| 12      | 2020-03-11  | mobile   | 150    |

Output

[
  {"spend_date":"2020-03-10","platform":"desktop","total_amount":400,"total_users":3},
  {"spend_date":"2020-03-10","platform":"mobile","total_amount":0,"total_users":0},
  {"spend_date":"2020-03-10","platform":"both","total_amount":0,"total_users":0},
  {"spend_date":"2020-03-11","platform":"desktop","total_amount":0,"total_users":0},
  {"spend_date":"2020-03-11","platform":"mobile","total_amount":150,"total_users":2},
  {"spend_date":"2020-03-11","platform":"both","total_amount":200,"total_users":1}
]

Explanation

A user's bucket is determined per day. User 12 is desktop-only on 2020-03-10, but both on 2020-03-11.

Example 3

Input

spending:
| user_id | spend_date  | platform | amount |
|---------|-------------|----------|--------|
| 101     | 2021-12-01  | mobile   | 25     |
| 102     | 2021-12-01  | desktop  | 75     |
| 103     | 2021-12-02  | mobile   | 20     |
| 103     | 2021-12-02  | desktop  | 30     |
| 104     | 2021-12-02  | mobile   | 45     |
| 105     | 2021-12-03  | desktop  | 60     |

Output

[
  {"spend_date":"2021-12-01","platform":"desktop","total_amount":75,"total_users":1},
  {"spend_date":"2021-12-01","platform":"mobile","total_amount":25,"total_users":1},
  {"spend_date":"2021-12-01","platform":"both","total_amount":0,"total_users":0},
  {"spend_date":"2021-12-02","platform":"desktop","total_amount":0,"total_users":0},
  {"spend_date":"2021-12-02","platform":"mobile","total_amount":45,"total_users":1},
  {"spend_date":"2021-12-02","platform":"both","total_amount":50,"total_users":1},
  {"spend_date":"2021-12-03","platform":"desktop","total_amount":60,"total_users":1},
  {"spend_date":"2021-12-03","platform":"mobile","total_amount":0,"total_users":0},
  {"spend_date":"2021-12-03","platform":"both","total_amount":0,"total_users":0}
]

Explanation

Every input date must produce three output rows. Missing buckets are explicitly represented with zeros.

Constraints

1 <= number of rows in spending <= 10,000
1 <= user_id <= 1,000,000,000
1 <= amount <= 1,000,000,000
platform is either 'desktop' or 'mobile'
spend_date is a valid date in YYYY-MM-DD format
(user_id, spend_date, platform) is unique
A user can contribute to exactly one bucket per date
Return exactly three platform rows per spend_date in the input
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: spend_date, platform, total_amount, total_users

spending: | user_id | spend_date | platform | amount | |---------|-------------|----------|--------| | 1 | 2019-07-01 | mobile | 120 | | 1 | 2019-07-01 | desktop | 80 | | 2 | 2019-07-01 | mobile | 40 | | 3 | 2019-07-01 | desktop | 60 | | 2 | 2019-07-02 | mobile | 70 | | 3 | 2019-07-02 | desktop | 30 |

[ {"spend_date":"2019-07-01","platform":"desktop","total_amount":60,"total_users":1}, {"spend_date":"2019-07-01","platform":"mobile","total_amount":40,"total_users":1}, {"spend_date":"2019-07-01","platform":"both","total_amount":200,"total_users":1}, {"spend_date":"2019-07-02","platform":"desktop","total_amount":30,"total_users":1}, {"spend_date":"2019-07-02","platform":"mobile","total_amount":70,"total_users":1}, {"spend_date":"2019-07-02","platform":"both","total_amount":0,"total_users":0} ]

spending: | user_id | spend_date | platform | amount | |---------|-------------|----------|--------| | 10 | 2020-03-10 | desktop | 300 | | 11 | 2020-03-10 | desktop | 70 | | 12 | 2020-03-10 | desktop | 30 | | 10 | 2020-03-11 | mobile | 90 | | 11 | 2020-03-11 | mobile | 60 | | 12 | 2020-03-11 | desktop | 50 | | 12 | 2020-03-11 | mobile | 150 |

[ {"spend_date":"2020-03-10","platform":"desktop","total_amount":400,"total_users":3}, {"spend_date":"2020-03-10","platform":"mobile","total_amount":0,"total_users":0}, {"spend_date":"2020-03-10","platform":"both","total_amount":0,"total_users":0}, {"spend_date":"2020-03-11","platform":"desktop","total_amount":0,"total_users":0}, {"spend_date":"2020-03-11","platform":"mobile","total_amount":150,"total_users":2}, {"spend_date":"2020-03-11","platform":"both","total_amount":200,"total_users":1} ]

spending: | user_id | spend_date | platform | amount | |---------|-------------|----------|--------| | 101 | 2021-12-01 | mobile | 25 | | 102 | 2021-12-01 | desktop | 75 | | 103 | 2021-12-02 | mobile | 20 | | 103 | 2021-12-02 | desktop | 30 | | 104 | 2021-12-02 | mobile | 45 | | 105 | 2021-12-03 | desktop | 60 |

[ {"spend_date":"2021-12-01","platform":"desktop","total_amount":75,"total_users":1}, {"spend_date":"2021-12-01","platform":"mobile","total_amount":25,"total_users":1}, {"spend_date":"2021-12-01","platform":"both","total_amount":0,"total_users":0}, {"spend_date":"2021-12-02","platform":"desktop","total_amount":0,"total_users":0}, {"spend_date":"2021-12-02","platform":"mobile","total_amount":45,"total_users":1}, {"spend_date":"2021-12-02","platform":"both","total_amount":50,"total_users":1}, {"spend_date":"2021-12-03","platform":"desktop","total_amount":60,"total_users":1}, {"spend_date":"2021-12-03","platform":"mobile","total_amount":0,"total_users":0}, {"spend_date":"2021-12-03","platform":"both","total_amount":0,"total_users":0} ]