| comments | difficulty | edit_url | tags | |
|---|---|---|---|---|
true |
困难 |
|
支出表: Spending
+-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | spend_date | date | | platform | enum | | amount | int | +-------------+---------+ 这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。 (user_id, spend_date, platform) 是这张表的主键(具有唯一值的列的组合)。 平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。
编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
以 任意顺序 返回结果表。
返回结果格式如下例所示：
示例 1：
输入：
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 |
+---------+------------+----------+--------+
输出：
+------------+----------+--------------+-------------+
| 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 |
+------------+----------+--------------+-------------+
解释：
在 2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买，而用户3 仅 使用了桌面端购买。
在 2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。# Write your MySQL query statement below
WITH
P AS (
SELECT DISTINCT spend_date, 'desktop' AS platform FROM Spending
UNION
SELECT DISTINCT spend_date, 'mobile' FROM Spending
UNION
SELECT DISTINCT spend_date, 'both' FROM Spending
),
T AS (
SELECT
user_id,
spend_date,
SUM(amount) AS amount,
IF(COUNT(platform) = 1, platform, 'both') AS platform
FROM Spending
GROUP BY 1, 2
)
SELECT
p.*,
IFNULL(SUM(amount), 0) AS total_amount,
COUNT(t.user_id) AS total_users
FROM
P AS p
LEFT JOIN T AS t USING (spend_date, platform)
GROUP BY 1, 2;