Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1127.html
1127. User Purchase Platform
Level
Hard
Description
Table: Spending
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| spend_date | date |
| platform | enum |
| amount | int |
+-------------+---------+
The table logs the spendings history of users that make purchases from an online shopping website which has a desktop and a mobile application.
(user_id, spend_date, platform) is the primary key of this table.
The platform column is an ENUM type of ('desktop', 'mobile').
Write an SQL query to find the total number of users and the total amount spent using mobile only, desktop only and both mobile and desktop together for each date.
The query result format is in the following example:
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 |
+---------+------------+----------+--------+
Result table:
+------------+----------+--------------+-------------+
| 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 |
+------------+----------+--------------+-------------+
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
First, select the columns’ values from table Spending, where the sum of amounts are calculated and platform has values 'desktop', 'mobile' and 'both'.
Next, obtain the four columns in the result table.
Finally, use ifnull to make the results of 0 appear in the result table.
# Write your MySQL query statement below
select s1.spend_date, s1.platform, ifnull(s3.total_amount, 0) as total_amount, ifnull(s3.total_users, 0) as total_users
from (
select distinct spend_date, 'both' as platform from Spending union all
select distinct spend_date, 'mobile' as platform from Spending union all
select distinct spend_date, 'desktop' as platform from Spending
) s1 left join (
select spend_date, platform, sum(amount) as total_amount, count(*) as total_users from (
select spend_date, user_id, sum(amount) as amount,
case when count(distinct platform) > 1 then 'both' else platform end as platform
from Spending group by spend_date, user_id
) s2 group by spend_date, platform
) s3 on s1.spend_date = s3.spend_date and s1.platform = s3.platform;