User Purchase Platform - Practice Coding Problems

User Purchase Platform - Problem

Database Hard

You're analyzing user spending patterns for a major e-commerce platform that offers both desktop and mobile shopping experiences. Given a table of user purchase history, you need to categorize users based on their platform usage patterns and calculate spending analytics.

For each date, determine:
• Users who shop only on mobile and their total spending
• Users who shop only on desktop and their total spending
• Users who shop on both platforms and their total spending

The Spending table contains:
• user_id: Unique identifier for each user
• spend_date: Date of purchase
• platform: Either 'mobile' or 'desktop'
• amount: Money spent in that transaction

Note: A user can make multiple purchases on the same platform on the same day, but each (user_id, spend_date, platform) combination is unique.

Input & Output

example_1.sql — Basic Platform Usage

$ Input: Spending 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: On 2019-07-01: User 1 used both platforms (total $200), User 2 used only mobile ($100), User 3 used only desktop ($100). On 2019-07-02: Users 2 and 3 each used only one platform, so 'both' has 0 users.

example_2.sql — Multiple Transactions

$ Input: Spending table: | user_id | spend_date | platform | amount | |---------|------------|----------|--------| | 1 | 2019-07-01 | mobile | 50 | | 1 | 2019-07-01 | mobile | 50 | | 1 | 2019-07-01 | desktop | 100 | | 2 | 2019-07-01 | desktop | 200 |

💡 Note: User 1 made multiple mobile transactions ($100 total) plus desktop ($100), so counted as 'both' with $200 total. User 2 only used desktop ($200). No users used mobile only.

example_3.sql — Single Platform Day

$ Input: Spending table: | user_id | spend_date | platform | amount | |---------|------------|----------|--------| | 1 | 2019-07-01 | mobile | 100 | | 2 | 2019-07-01 | mobile | 150 | | 3 | 2019-07-01 | mobile | 200 |

› Output: | spend_date | platform | total_users | total_amount | |------------|----------|-------------|---------------| | 2019-07-01| both | 0 | 0 | | 2019-07-01| desktop | 0 | 0 | | 2019-07-01| mobile | 3 | 450 |

💡 Note: All users only used mobile platform on this date. The 'both' and 'desktop' categories show 0 users and $0 amount, demonstrating how the query ensures all platform combinations are represented.

Constraints

1 ≤ number of rows in Spending table ≤ 10⁵
1 ≤ user_id ≤ 10⁹
spend_date is a valid date in format YYYY-MM-DD
platform is either 'mobile' or 'desktop'
1 ≤ amount ≤ 10⁴
Each (user_id, spend_date, platform) combination is unique

Asked in

The optimal solution uses window functions to efficiently categorize user platform usage patterns in a single pass. By leveraging COUNT DISTINCT OVER partitions, we determine if each user uses one or both platforms per date, then aggregate the results with proper CROSS JOIN coverage to ensure all platform combinations appear with zero values when no users match.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(n)	Separate queries for each platform combination with manual date handling
Two-Pass Aggregation	O(n log n)	O(n)	First aggregate user platform usage per date, then categorize and sum
One-Pass Window Function (Optimal)	O(n log n)	O(n)	Use window functions to determine platform usage and aggregate in single pass

Brute Force (Multiple Subqueries) — Algorithm Steps

Get all distinct dates from the spending table
For each date, find users who only used mobile platform
For each date, find users who only used desktop platform
For each date, find users who used both platforms
Calculate user counts and total amounts for each category
Combine results with UNION ALL

Visualization

Tap to expand

Step-by-Step Walkthrough

Mobile-Only Query

First query scans for users who only used mobile platform

Desktop-Only Query

Second query scans for users who only used desktop platform

Both Platforms Query

Third query scans for users who used both platforms

Union Results

Combine all three result sets

Code -

solution.c — C

// Complete working SQL solution (brute force approach)
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sql[] = 
    "WITH dates AS (" 
    "    SELECT DISTINCT spend_date FROM Spending" 
    ")," 
    "mobile_only AS (" 
    "    SELECT " 
    "        spend_date," 
    "        'mobile' as platform," 
    "        COUNT(DISTINCT user_id) as total_users," 
    "        SUM(amount) as total_amount" 
    "    FROM Spending s1" 
    "    WHERE platform = 'mobile'" 
    "    AND NOT EXISTS (" 
    "        SELECT 1 FROM Spending s2 " 
    "        WHERE s2.user_id = s1.user_id " 
    "        AND s2.spend_date = s1.spend_date " 
    "        AND s2.platform = 'desktop'" 
    "    )" 
    "    GROUP BY spend_date" 
    ")," 
    "desktop_only AS (" 
    "    SELECT " 
    "        spend_date," 
    "        'desktop' as platform," 
    "        COUNT(DISTINCT user_id) as total_users," 
    "        SUM(amount) as total_amount" 
    "    FROM Spending s1" 
    "    WHERE platform = 'desktop'" 
    "    AND NOT EXISTS (" 
    "        SELECT 1 FROM Spending s2 " 
    "        WHERE s2.user_id = s1.user_id " 
    "        AND s2.spend_date = s1.spend_date " 
    "        AND s2.platform = 'mobile'" 
    "    )" 
    "    GROUP BY spend_date" 
    ")," 
    "both_platforms AS (" 
    "    SELECT " 
    "        spend_date," 
    "        'both' as platform," 
    "        COUNT(DISTINCT user_id) as total_users," 
    "        SUM(amount) as total_amount" 
    "    FROM Spending s1" 
    "    WHERE EXISTS (" 
    "        SELECT 1 FROM Spending s2 " 
    "        WHERE s2.user_id = s1.user_id " 
    "        AND s2.spend_date = s1.spend_date " 
    "        AND s2.platform != s1.platform" 
    "    )" 
    "    GROUP BY spend_date" 
    ")" 
    "SELECT * FROM mobile_only " 
    "UNION ALL " 
    "SELECT * FROM desktop_only " 
    "UNION ALL " 
    "SELECT * FROM both_platforms " 
    "ORDER BY spend_date, platform";
    return sql;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes through the data for each platform combination

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for intermediate subquery results

⚡ Linearithmic Space

25.0K Views

Medium Frequency

~15 min Avg. Time

850 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