LeetCode 1127.User Purchase Platform

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.

 

# 建表语句

CREATE TABLE Spending ( user_id INT, spend_date DATE, platform ENUM ('desktop', 'mobile'), amount INT ) ;
# 插入测试数据
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (1, '2019-07-01', 'mobile', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (1, '2019-07-01', 'desktop', 100) ;
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (2, '2019-07-01', 'mobile', 100) ;
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (2, '2019-07-02', 'mobile', 100) ;
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (3, '2019-07-01', 'desktop', 100) ;
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (3, '2019-07-02', 'desktop', 100) ;
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (3, '2019-07-03', 'desktop', 100) ;

#查询语句
SELECT a.spend_date, a.platform, CONVERT( IF( ISNULL(total_amount), 0, total_amount ), DECIMAL ) AS total_amount, IF(ISNULL(total_users), 0, total_users) total_users FROM (SELECT * FROM (SELECT DISTINCT spend_date FROM Spending) t1, (SELECT DISTINCT platform FROM Spending) t2) a LEFT JOIN (SELECT spend_date, platform, SUM(amount) total_amount, COUNT(DISTINCT user_id) total_users FROM (SELECT t1.*, t2.`platform` AS t2_platform FROM Spending t1 LEFT JOIN Spending t2 ON t1.`spend_date` = t2.`spend_date` AND t1.`user_id` = t2.`user_id` AND t1.`platform` != t2.`platform`) temp WHERE t2_platform IS NULL GROUP BY spend_date, platform) b ON a.spend_date = b.spend_date AND a.platform = b.platform UNION SELECT c.spend_date, c.platform, IF( ISNULL(total_amount), 0, total_amount ) AS total_amount, IF(ISNULL(total_users), 0, total_users) total_users FROM (SELECT DISTINCT spend_date, 'both' AS platform FROM Spending) c LEFT JOIN (SELECT t1.spend_date, SUM(t1.amount) total_amount, COUNT(DISTINCT t1.user_id) total_users FROM Spending t1, Spending t2 WHERE t1.`spend_date` = t2.`spend_date` AND t1.`user_id` = t2.`user_id` AND t1.`platform` != t2.`platform` GROUP BY t1.spend_date) d ON c.spend_date = d.spend_date ORDER BY spend_date ASC, LENGTH(platform) DESC ;

 

上一篇:GD32实战7__中断


下一篇:GD32实战5__状态机