Description

Table: Spending

Column NameType
user_idint
spend_datedate
platformenum
amountint
  • 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’).

Problem Statement

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_idspend_dateplatformamount
12019-07-01mobile100
12019-07-01desktop100
22019-07-01mobile100
22019-07-02mobile100
32019-07-01desktop100
32019-07-02desktop100

Output:

spend_dateplatformtotal_amounttotal_users
2019-07-01desktop1001
2019-07-01mobile1001
2019-07-01both2001
2019-07-02desktop1001
2019-07-02mobile1001
2019-07-02both00

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

 1WITH user_spending_per_day AS (
 2    SELECT user_id, spend_date,
 3        SUM(CASE WHEN platform = 'mobile' THEN amount ELSE 0 END) mobile_spend,
 4        SUM(CASE WHEN platform = 'desktop' THEN amount ELSE 0 END) desktop_spend
 5        FROM Spending
 6        GROUP BY user_id, spend_date
 7) SELECT spend_date, 'desktop' AS platform,
 8    SUM(CASE WHEN desktop_spend > 0 AND mobile_spend = 0  THEN desktop_spend ELSE 0 END) total_amount,
 9    SUM(CASE WHEN desktop_spend > 0 AND mobile_spend = 0 THEN 1 ELSE 0 END) total_users
10    FROM user_spending_per_day
11    GROUP BY spend_date
12UNION ALL
13SELECT spend_date, 'mobile' platform,
14    SUM(CASE WHEN mobile_spend > 0 AND desktop_spend = 0 THEN mobile_spend ELSE 0 END) total_amount,
15    SUM(CASE WHEN mobile_spend > 0 AND desktop_spend = 0 THEN 1 ELSE 0 END) total_users
16    FROM user_spending_per_day
17    GROUP BY spend_date
18UNION ALL
19SELECT spend_date, 'both' platform,
20    SUM(CASE WHEN desktop_spend > 0 AND mobile_spend > 0 THEN mobile_spend + desktop_spend ELSE 0 END) total_amount,
21    SUM(CASE WHEN desktop_spend > 0 AND mobile_spend > 0 THEN 1 ELSE 0 END) total_users
22    FROM user_spending_per_day
23    GROUP BY spend_date;