LeetCode Problem 1127

Difficulty: 🔴 Hard
Topics: Database

Solution

LeetCode 1127, User Purchase Platform

Problem Understanding

The Spending table records purchases made by users on an e-commerce platform. Every row represents one user's spending on a specific date using either the mobile platform or the desktop platform.

The table has four columns:

Column	Meaning
user_id	The user making the purchase
spend_date	The date of the purchase
platform	Either mobile or desktop
amount	Amount spent

The primary key is (user_id, spend_date, platform), which means a user can have at most one row per platform per day.

The task is to generate a summary for every date. For each date, we must classify users into exactly one of three categories:

Category	Meaning
mobile	User used only mobile on that day
desktop	User used only desktop on that day
both	User used both mobile and desktop on that day

For each category and each date, we must compute:

1. total_amount, the sum of spending
2. total_users, the number of users in that category

An important detail is that the result must always contain all three platform categories for every date, even if the count and amount are zero.

For example, if nobody used both platforms on a certain day, we still need:

| date | both | 0 | 0 |

The input size is not explicitly stated, but database problems generally expect solutions that minimize unnecessary joins and repeated scans. Since aggregation is central to the problem, the ideal solution should group users efficiently before computing totals.

Several edge cases can cause bugs in naive implementations:

• A user may appear only once on a day, meaning they belong to either mobile or desktop.
• A user may appear twice on a day, once for each platform, meaning they belong to both.
• Some dates may not have users in one or more categories, but those rows must still appear with zero values.
• Multiple users can belong to the same category on the same day, so aggregation must happen after classification.

The key challenge is correctly classifying each user per date before aggregating totals.

Approaches

Brute Force Approach

A brute force strategy would examine every date independently and repeatedly scan the table to determine which users used which platforms.

For each (user_id, spend_date) pair, we could:

1. Search all rows for that user and date.
2. Determine whether the user used:

• only mobile
• only desktop
• both

3. Aggregate totals into separate groups.

This works because every user's platform usage can eventually be determined by checking all matching rows.

However, this approach is inefficient because it repeatedly scans the table for the same information. If there are n rows, repeated lookups can easily lead to quadratic behavior.

Optimal Approach

The key observation is that classification should happen once per (user_id, spend_date) pair.

Instead of repeatedly scanning the table, we first aggregate each user's activity for a given day:

• Sum the user's total spending for the day.
• Count how many distinct platforms they used.

Since the platform can only be mobile or desktop:

• COUNT(DISTINCT platform) = 2 means the user used both.
• Otherwise, the single platform value tells us whether the user used only mobile or only desktop.

After classifying users, we perform a second aggregation step:

• Group by spend_date and category
• Sum amounts
• Count users

Finally, we generate all (date, platform) combinations so missing categories can be filled with zeros using a LEFT JOIN.

This approach avoids repeated scans and performs aggregation in a structured way.

Approach Comparison

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n²)	O(n)	Repeatedly scans rows to classify users
Optimal	O(n)	O(n)	Uses grouped aggregation and classification

Algorithm Walkthrough

1. First, group the table by (user_id, spend_date).

This step consolidates all activity for a user on a specific day. We calculate:

• total spending for that user on that day
• how many distinct platforms were used

2. Determine the user's category.

If the distinct platform count is 2, the category is both.

Otherwise, the user used exactly one platform, so we can use MAX(platform) or MIN(platform) to retrieve the platform name. 3. Store the classified result.

After classification, each user-day pair becomes a single row containing:

• spend_date
• category (mobile, desktop, or both)
• total amount spent

4. Aggregate classified rows.

Group by:

• spend_date
• category

Then compute:

• total spending
• total number of users

5. Generate all platform categories for every date.

Create a small derived table containing:

• mobile
• desktop
• both

Cross join this with all distinct dates. 6. Left join aggregated results.

This ensures missing categories still appear.

If no matching row exists, use COALESCE to replace NULL values with 0.

Why it works

The algorithm works because every user on a given date belongs to exactly one mutually exclusive category:

• only mobile
• only desktop
• both

The first aggregation step guarantees correct classification by examining all platforms used by that user on that date. The second aggregation step correctly combines users within the same category. Finally, the cross join guarantees complete output coverage for every platform category on every date.

Python Solution

# Write your MySQL query statement below

WITH user_summary AS (
    SELECT
        user_id,
        spend_date,
        CASE
            WHEN COUNT(DISTINCT platform) = 2 THEN 'both'
            ELSE MAX(platform)
        END AS platform_type,
        SUM(amount) AS total_amount
    FROM Spending
    GROUP BY user_id, spend_date
),
aggregated AS (
    SELECT
        spend_date,
        platform_type,
        SUM(total_amount) AS total_amount,
        COUNT(*) AS total_users
    FROM user_summary
    GROUP BY spend_date, platform_type
),
platforms AS (
    SELECT 'mobile' AS platform
    UNION ALL
    SELECT 'desktop'
    UNION ALL
    SELECT 'both'
),
dates AS (
    SELECT DISTINCT spend_date
    FROM Spending
)

SELECT
    d.spend_date,
    p.platform,
    COALESCE(a.total_amount, 0) AS total_amount,
    COALESCE(a.total_users, 0) AS total_users
FROM dates d
CROSS JOIN platforms p
LEFT JOIN aggregated a
    ON d.spend_date = a.spend_date
   AND p.platform = a.platform_type;

The solution uses Common Table Expressions to organize the logic into clear stages.

The user_summary CTE performs the most important step, classifying each user per date. The COUNT(DISTINCT platform) determines whether the user belongs to the both category. Otherwise, MAX(platform) retrieves the single platform used.

The aggregated CTE then combines users belonging to the same category and date. At this point, the final totals are computed.

The platforms CTE creates the required platform categories explicitly. This is important because some categories may not exist naturally in the aggregated data.

The dates CTE extracts every distinct spending date.

Finally, the query cross joins dates and platforms to create all required combinations, then left joins the aggregated results. COALESCE converts missing values into zeros.

Go Solution

// This problem is a SQL-only LeetCode problem.
// No Go implementation is required because the expected
// submission format is a MySQL query.

Unlike algorithmic LeetCode problems, this database problem expects a SQL query submission rather than executable Go code. Therefore, there is no actual Go implementation required.

Worked Examples

Example 1

Input:

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

Step 1, Build user_summary

Group by (user_id, spend_date).

user_id	spend_date	platforms used	category	total_amount
1	2019-07-01	mobile + desktop	both	200
2	2019-07-01	mobile	mobile	100
2	2019-07-02	mobile	mobile	100
3	2019-07-01	desktop	desktop	100
3	2019-07-02	desktop	desktop	100

Step 2, Aggregate by date and category

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

Step 3, Generate all category combinations

For each date:

spend_date	platform
2019-07-01	mobile
2019-07-01	desktop
2019-07-01	both
2019-07-02	mobile
2019-07-02	desktop
2019-07-02	both

Step 4, Left join aggregated data

Missing rows become zero:

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

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n)	Each row is processed through grouped aggregations
Space	O(n)	Intermediate grouped results may store all user-date pairs

The query performs several aggregation operations, but each one scans grouped data rather than repeatedly rescanning the original table. Since grouping and joins are linear relative to the number of rows and grouped records, the overall complexity is effectively linear.

Test Cases

# Example 1 from problem statement
# Validates normal behavior with all three categories
input_data = [
    (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),
]

# Single user using only mobile
# Tests mobile-only classification
input_data = [
    (1, "2020-01-01", "mobile", 50),
]

# Single user using only desktop
# Tests desktop-only classification
input_data = [
    (1, "2020-01-01", "desktop", 75),
]

# Single user using both platforms
# Tests both-category logic
input_data = [
    (1, "2020-01-01", "mobile", 100),
    (1, "2020-01-01", "desktop", 200),
]

# Multiple users in same category
# Tests aggregation correctness
input_data = [
    (1, "2020-01-01", "mobile", 100),
    (2, "2020-01-01", "mobile", 200),
    (3, "2020-01-01", "mobile", 300),
]

# Multiple dates with missing categories
# Ensures zero rows appear correctly
input_data = [
    (1, "2020-01-01", "mobile", 100),
    (2, "2020-01-02", "desktop", 200),
]

# User spends different amounts on both platforms
# Verifies total summation
input_data = [
    (1, "2020-01-01", "mobile", 120),
    (1, "2020-01-01", "desktop", 80),
]

# Multiple users using both platforms
# Tests aggregation of both-category users
input_data = [
    (1, "2020-01-01", "mobile", 100),
    (1, "2020-01-01", "desktop", 100),
    (2, "2020-01-01", "mobile", 50),
    (2, "2020-01-01", "desktop", 50),
]

Test Case Summary

Test	Why
Example input	Validates standard scenario
Single mobile row	Verifies mobile-only classification
Single desktop row	Verifies desktop-only classification
Single both-platform user	Verifies both-category detection
Multiple mobile users	Tests aggregation correctness
Missing categories	Ensures zero rows are generated
Uneven spending across platforms	Verifies total summation logic
Multiple both users	Tests aggregation for combined users

Edge Cases

Users Appearing on Both Platforms

A user may have both a mobile row and a desktop row on the same date. A naive implementation might count the user twice, once in each category.

The solution avoids this by grouping first by (user_id, spend_date) before classification. Once grouped, the user becomes a single logical entity for that day, ensuring they are placed exclusively into the both category.

Missing Categories for a Date

Some dates may not naturally contain all three categories. For example, a day might contain only mobile purchases.

A naive aggregation would completely omit missing categories from the result. However, the problem explicitly requires every category to appear.

The solution handles this by cross joining all dates with all three platform labels, then left joining aggregated results. Missing values are replaced with zeros using COALESCE.

Multiple Users in the Same Category

Several users may belong to the same category on the same day. A naive implementation could incorrectly overwrite totals instead of accumulating them.

The second aggregation step correctly groups by (spend_date, platform_type) and computes:

• SUM(total_amount)
• COUNT(*)

This guarantees all users contribute correctly to the final totals.