【SQL高频练习带刷】day4：聚合查询补充习题

题目一：每月交易I

题目要求：

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        本题虽然是中等难度，但核心考察点主要在于case when的使用，能熟练使用的话就比较简单了。首先是需要使用字符串截取（或者其他）函数将年份和月份截取出来，之后就是两对计算数量的列了。

运行代码示例：

select substring(trans_date,1,7) as month,
country,
count(amount) AS trans_count,
sum(case when state  = "approved" then 1 else 0 end) as approved_count,
sum(amount) as trans_total_amount,
sum(case when state  = "approved" then amount else 0 end) as approved_total_amount
from Transactions
group by country,month

题目二：即时实物配送II

题目要求：

如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。

表结构：

 运行结果示例：

思路：

        这道题的难点在于需要根据客户id分组，找到两个日期相同且是首次的订单。我们可以直接考虑使用子查询来解决，也就是根据客户id分组先筛选出来每个客户的首次订单表，再从该子表中寻找即时订单。

        （这道题解法很多，比如使用连接查询、使用rank（）函数等等，甚至子查询的写法也有很多种，答案不唯一。）

运行代码示例：

select round(sum(order_date = customer_pref_delivery_date)*100 / count(distinct customer_id)  ,2) as immediate_percentage
from( 
    select customer_id,
        min(order_date) as order_date,
        min(customer_pref_delivery_date ) as customer_pref_delivery_date 
    from Delivery 
    group by customer_id) as a

题目三：游戏玩法分析

题目要求：

编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

表结构：

 运行结果示例：

思路：

        注意题目要求，寻找的分子是首次登录的玩家

运行代码示例：

SELECT ROUND(AVG(a2.event_date IS NOT NULL),2) fraction
FROM(
    SELECT player_id, MIN(event_date) login
    FROM activity
    GROUP BY player_id) a1
LEFT JOIN activity a2
ON a1.player_id = a2.player_id
AND a2.event_date = DATE_ADD(a1.login,interval 1 day)