LeetCode 1127.User Purchase Platform

+-------------+---------+
| 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‘).

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 ;