User Purchase Platform

User Purchase Platform - Problem

Database Hard

You are given a table Spending that logs the history of user purchases from an online shopping website with both desktop and mobile applications.

Table: Spending

Column Name	Type
user_id	int
spend_date	date
platform	enum
amount	int

The combination (user_id, spend_date, platform) is the primary key of this table. The platform column is an ENUM type with values ('desktop', 'mobile').

Task: Write a solution to find the total number of users and the total amount spent using:

mobile only - users who spent on mobile platform exclusively on that date
desktop only - users who spent on desktop platform exclusively on that date
both - users who spent on both mobile and desktop platforms on that date

Return the result for each date in any order.

Table Schema

Spending

Column Name	Type	Description
`user_id` PK	int	User identifier
`spend_date` PK	date	Date of the purchase
`platform` PK	enum	Platform used: 'desktop' or 'mobile'
`amount`	int	Amount spent in the transaction

Primary Key: (user_id, spend_date, platform)

Note: Each row represents a unique user purchase on a specific platform and date

Input & Output

Example 1 — Mixed Platform Usage

Input Table:

user_id	spend_date	platform	amount
1	2019-07-01	mobile	100
1	2019-07-01	desktop	100
2	2019-07-01	mobile	100
2	2019-07-02	mobile	100
3	2019-07-01	desktop	100
3	2019-07-02	desktop	100

Output:

spend_date	platform	total_users	total_amount
2019-07-01	both	1	200
2019-07-01	desktop	1	100
2019-07-01	mobile	1	100
2019-07-02	both	0	0
2019-07-02	desktop	1	100
2019-07-02	mobile	1	100

💡 Note:

For 2019-07-01: User 1 spent on both platforms (both: 1 user, $200), User 2 spent only on mobile (mobile: 1 user, $100), User 3 spent only on desktop (desktop: 1 user, $100). For 2019-07-02: User 2 spent only on mobile and User 3 spent only on desktop, with no users using both platforms.

Example 2 — Single Platform Day

Input Table:

user_id	spend_date	platform	amount
1	2019-07-01	mobile	50
2	2019-07-01	mobile	75

Output:

spend_date	platform	total_users	total_amount
2019-07-01	both	0	0
2019-07-01	desktop	0	0
2019-07-01	mobile	2	125

💡 Note:

All users only used mobile platform on this date. The cross join ensures that all three platform categories appear in the result, with desktop and both showing zero counts.

Constraints

1 ≤ user_id ≤ 1000
spend_date is a valid date
platform is either 'desktop' or 'mobile'
amount ≥ 0

Visualization

Tap to expand

Asked in

A Amazon 23 F Facebook 18 G Google 15

Use CTE with conditional aggregation to classify users by platform usage, then CROSS JOIN with all platform categories to ensure complete results with zero counts for missing combinations.

Table Schema

Spending

Column Name	Type	Description
`user_id` PK	int	User identifier
`spend_date` PK	date	Date of the purchase
`platform` PK	enum	Platform used: 'desktop' or 'mobile'
`amount`	int	Amount spent in the transaction

Primary Key: (user_id, spend_date, platform)

Note: Each row represents a unique user purchase on a specific platform and date

Common Approaches

✓ CTE with Cross Join and Conditional Aggregation

⏱️ Time: O(n) Space: O(d × p)

First create a CTE to get user platform usage per date, then cross join all dates with all platform categories to ensure complete results, finally aggregate using conditional logic.

CTE with Cross Join and Conditional Aggregation — Algorithm Steps

Step 1: Create CTE to classify each user's platform usage per date
Step 2: Cross join all dates with platform categories ('mobile', 'desktop', 'both')
Step 3: Aggregate users and amounts using conditional SUM and COUNT

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by User

Classify each user's platform usage per date

Cross Join

Create all date-platform combinations

Aggregate

Count users and sum amounts per category

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through data with grouping

✓ Linear Growth

Space Complexity

O(d × p)

Storage for all date-platform combinations

✓ Linear Space

34.5K Views

Medium Frequency

~25 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with Cross Join and Conditional Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler