Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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.

Solution

Method 1 – Group By and Conditional Aggregation

Intuition

We need to classify users for each date into three categories: mobile only, desktop only, and both. We can use conditional aggregation and grouping to achieve this.

Approach

  1. For each (user_id, spend_date), collect the platforms used and sum the amounts per platform.
  2. Classify each user-date as ‘mobile’, ‘desktop’, or ‘both’ based on the platforms used.
  3. For each date and category, sum the total amount and count the users.
  4. Output all three categories for each date, even if the count is zero.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

WITH user_platforms AS (
  SELECT user_id, spend_date,
    SUM(CASE WHEN platform = 'mobile' THEN amount ELSE 0 END) AS mobile_amt,
    SUM(CASE WHEN platform = 'desktop' THEN amount ELSE 0 END) AS desktop_amt,
    COUNT(DISTINCT platform) AS platform_count
  FROM Spending
  GROUP BY user_id, spend_date
),
user_types AS (
  SELECT user_id, spend_date,
    CASE
      WHEN platform_count = 2 THEN 'both'
      WHEN mobile_amt > 0 THEN 'mobile'
      ELSE 'desktop'
    END AS platform_type,
    (mobile_amt + desktop_amt) AS total_amt
  FROM user_platforms
)
SELECT spend_date, platform_type AS platform,
       SUM(total_amt) AS total_amount,
       COUNT(user_id) AS total_users
FROM user_types
GROUP BY spend_date, platform_type
UNION ALL
SELECT d.spend_date, p.platform, 0, 0
FROM (SELECT DISTINCT spend_date FROM Spending) d
CROSS JOIN (SELECT 'mobile' AS platform UNION SELECT 'desktop' UNION SELECT 'both') p
WHERE NOT EXISTS (
  SELECT 1 FROM user_types u
  WHERE u.spend_date = d.spend_date AND u.platform_type = p.platform
);
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

WITH user_platforms AS (
  SELECT user_id, spend_date,
    SUM(CASE WHEN platform = 'mobile' THEN amount ELSE 0 END) AS mobile_amt,
    SUM(CASE WHEN platform = 'desktop' THEN amount ELSE 0 END) AS desktop_amt,
    COUNT(DISTINCT platform) AS platform_count
  FROM Spending
  GROUP BY user_id, spend_date
),
user_types AS (
  SELECT user_id, spend_date,
    CASE
      WHEN platform_count = 2 THEN 'both'
      WHEN mobile_amt > 0 THEN 'mobile'
      ELSE 'desktop'
    END AS platform_type,
    (mobile_amt + desktop_amt) AS total_amt
  FROM user_platforms
)
SELECT spend_date, platform_type AS platform,
       SUM(total_amt) AS total_amount,
       COUNT(user_id) AS total_users
FROM user_types
GROUP BY spend_date, platform_type
UNION ALL
SELECT d.spend_date, p.platform, 0, 0
FROM (SELECT DISTINCT spend_date FROM Spending) d
CROSS JOIN (VALUES ('mobile'), ('desktop'), ('both')) p(platform)
WHERE NOT EXISTS (
  SELECT 1 FROM user_types u
  WHERE u.spend_date = d.spend_date AND u.platform_type = p.platform
);

Complexity

  • ⏰ Time complexity: O(n) — Each row is processed a constant number of times.
  • 🧺 Space complexity: O(n) — For intermediate groupings.