Watch 2 video solutions for User Purchase Platform, a hard level problem involving Database. This walkthrough by Ankit Bansal has 34,546 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Spending
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| spend_date | date |
| platform | enum |
| amount | int |
+-------------+---------+
The table logs the history of the spending of users that make purchases from an online shopping website that has a desktop and a mobile application.
(user_id, spend_date, platform) is the primary key (combination of columns with unique values) of this table.
The platform column is an ENUM (category) type of ('desktop', 'mobile').
Write a solution to find the total number of users and the total amount spent using the mobile only, the desktop only, and both mobile and desktop together for each date.
Return the result table in any order.
The result format is in the following example.
Example 1:
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_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 | +------------+----------+--------------+-------------+ Explanation: On 2019-07-01, user 1 purchased using both desktop and mobile, user 2 purchased using mobile only and user 3 purchased using desktop only. On 2019-07-02, user 2 purchased using mobile only, user 3 purchased using desktop only and no one purchased using both platforms.
Problem Overview: You receive a Spending table containing user_id, spend_date, platform (mobile or desktop), and amount. For each date, compute total users and total amount for three categories: mobile only, desktop only, and both platforms. A user who spends on both platforms on the same day must be grouped under both, not counted separately in mobile or desktop.
Approach 1: Direct Aggregation with Conditional Classification (O(n) time, O(n) space)
Group transactions by user_id and spend_date first. This step consolidates all purchases a user made on a specific day. While aggregating, compute the total spend and detect whether the user used mobile, desktop, or both. The classification can be done with conditional aggregation such as COUNT(DISTINCT platform) or boolean flags derived from CASE expressions.
Once each user-day pair is labeled as mobile, desktop, or both, perform a second aggregation step grouped by spend_date and the derived platform category. Sum the purchase amounts and count distinct users in each category. Because the table is scanned once for aggregation and once for grouping, the overall runtime is linear relative to the number of rows.
This approach works well because the classification happens before the final aggregation. Without the intermediate grouping step, users who purchased on multiple platforms would be counted incorrectly. The intermediate result acts like a normalized dataset where each user-day pair has a single platform label.
In SQL interviews, this pattern appears frequently: first aggregate at the entity level (user per day), then aggregate again at the reporting level (date per category). The technique relies heavily on GROUP BY, conditional logic, and derived columns. These concepts are common in SQL, database analytics queries, and aggregation problems.
Recommended for interviews: The two-stage aggregation approach is the expected solution. A naive attempt that groups directly by platform leads to incorrect counts because it cannot detect users who used both platforms in the same day. Showing the intermediate user-day aggregation demonstrates strong SQL reasoning and mirrors how production analytics queries are typically written.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Naive aggregation by platform | O(n) | O(1) | Quick exploration, but incorrect when users purchase on multiple platforms the same day |
| Two-stage aggregation (user-day classification) | O(n) | O(n) | Correct analytical solution for production queries and SQL interviews |