Daily User Platform Spend Segmentation

HARD30 pts

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:
1. total_amount: sum of spend amount for users assigned to that bucket and date
2. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

spending =

[{"user_id":1,"spend_date":"2019-07-01","platform":"mobile","amount":120},{"user_id":1,"spend_date":"2019-07-01","platform":"desktop","amount":80},{"user_id":2,"spend_date":"2019-07-01","platform":"mobile","amount":40},{"user_id":3,"spend_date":"2019-07-01","platform":"desktop","amount":60},{"user_id":2,"spend_date":"2019-07-02","platform":"mobile","amount":70},{"user_id":3,"spend_date":"2019-07-02","platform":"desktop","amount":30}]

Daily User Platform Spend Segmentation

Hints

Daily User Platform Spend Segmentation

Hints