User Purchase Platform - Problem

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 NameType
user_idint
spend_datedate
platformenum
amountint

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
User Purchase Platform Analysis OverviewRaw Purchase DataUser 1: Mobile + DesktopUser 2: Mobile onlyUser 3: Desktop onlySame date: 2019-07-01CategorizeUsersPlatform CategoriesBoth: 1 user ($200)Mobile: 1 user ($100)Desktop: 1 user ($100)Cross JoinCompleteFinal Result2019-07-01both: 1 users, $200mobile: 1 users, $100desktop: 1 users, $100All categories includedeven with zero countsKey SQL Operations1. GROUP BY user + date2. CASE for platform classification3. CROSS JOIN for completeness4. COALESCE for zerosEnsures every date has all three platform categories in results
Understanding the Visualization
1
Input Analysis
Users can purchase on mobile, desktop, or both
2
Classification
Group users by platform usage per date
3
Complete Results
Cross join ensures all categories appear
Key Takeaway
🎯 Key Insight: Use CROSS JOIN with platform categories to ensure complete results, preventing missing combinations in the output

Related Problems

Asked in
Amazon 23 Facebook 18 Google 15
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