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 ;