mysql 数据库习题练习3 ：按题目出现频率（高到低）

1097. 游戏玩法分析 V

题目（链接）：

解析：
以子查询中的first_date作为group by 的分组，
其中:
1.first_date 作为 install_dt,
2.对当天有记录的player_id 计数，作为 installs ，注意 一个id有多条记录，我们是在每条记录后面都添加了 first_date , 所以要使用 distinct 去重，才能得到当天的的实际安装数
3.判断 每个first_date 对应的 event_date 是不是注册的第二天，如果是，累加第二天的天数，作为分子，installs (当天有记录的player_id 数)

select 
    first_date as `install_dt`,
    count(distinct player_id) as `installs` ,
    round(sum(if(date_add(first_date,interval 1 day) = event_date,1,0))/count(distinct player_id),2) as `Day1_retention`
from (
    select player_id,event_date,
        min(event_date) over(partition by player_id order by event_date) as first_date
        from Activity 
) as t
group by first_date;

1988. 找出每所学校最低分数要求

题目：

题目解析：需要连表取出学校id 和 对应的score，链接条件是学校的学生容量 >= 今年考试的实际容量

代码

子查询：

select school_id,e.score from Schools as s
left join Exam as e 
on s.capacity >= e.student_count;
# 为啥这里要用 '>='呢，因为这里容纳能力（capacity）指的是学校能够接纳学生的最大数量。student_count则是当年实际情况。

方法一：左外连接 + ifnull 处理空值

select school_id,ifnull(min(score),-1) as `score` from(
    select school_id,e.score from Schools as s
    left join Exam as e 
    on s.capacity >= e.student_count 
)temp
group by school_id
order by school_id;

方法二：左外连接 + coalesce 处理空值

coalesce()解释：返回参数中的第一个非空表达式（从左向右依次类推）；

select school_id,coalesce(min(score),-1) as `score` from(
    select school_id,e.score from Schools as s
    left join Exam as e 
    on s.capacity >= e.student_count 
)temp
group by school_id
order by school_id;

577. 员工奖金(左外连接)

题目：

题解：
**这里判断是否为null ，不能用 ‘=’ 要用 ‘or’ **

select e.name,b.bonus from Employee as e left join Bonus as b 
on 
    b.empId = e.empId
where  
    b.bonus < 1000 or b.bonus is null;

2026. 低质量问题（简单的喜欢比率的计算）

题目

题解：直接输出喜欢比率likes/(likes+dislikes)小于0.6的problem_id

select problem_id
from Problems
where likes/(likes+dislikes) < 0.6
order by problem_id;

1141. 查询近30天活跃用户数

题目： 链接

解法：datediff()

datediff 算两个日期差多少天。包含27号，那近2天就是26，27两天，datediff 是1。近30天就是6月28到7月27，datediff 是29 ＜30

# datediff 算两个日期差多少天。包含27号，那近2天就是26，27两天，datediff 是1。近30天就是6月28到7月27，datediff 是29 ＜30

select activity_date as `day`,
       count(distinct(user_id)) as `active_users`
from Activity
group by activity_date
having datediff('2019-07-27',activity_date) <30;

1581.进店却未支付的顾客

题目：题目链接

题解 ：left join

思路：
Visits 表（as v）是顾客浏览表，储存了每一次浏览的顾客id
Transactions 表(as t)是顾客购买表，只储存发生购买行为的顾客id
要求的是未购买顾客的浏览次数
Visist 和 Transaction 左连表
连接条件是 v.visit_id = t.visit_id
where 条件为取没有购物的项：t.visit_id = null
以costomer_id 分组

select customer_id,count(customer_id) count_no_trans
from Visits v
left join Transactions t
on v.visit_id = t.visit_id
where t.visit_id is null
group by customer_id
order by count_no_trans desc;

1212. 查询球队积分

题解：left join + sum

分析：每场比赛主客队得分求出来
对 match_id 分组之后分别计算 host_scores , guest_scores

select host_team,guest_team,
    sum(case 
            when host_goals > guest_goals then 3
            when host_goals = guest_goals then 1
            else  0
        end   
    ) as host_scores,        
    sum(case 
            when host_goals < guest_goals then 3
            when host_goals = guest_goals then 1
            else  0
        end   
    ) as guest_scores
from Matches
group by match_id;

以 team_id 分组，上面的查询作为临时表，进行子查询，

select team_id,team_name,
ifnull(sum(case 
                when team_id = host_team then host_scores
                when team_id = guest_team then guest_scores
           end 
),0) as num_points
from Teams as t
left join
    (
    select host_team,guest_team,
        sum(case 
                when host_goals > guest_goals then 3
                when host_goals = guest_goals then 1
                else  0
            end   
        ) as host_scores,        
        sum(case 
                when host_goals < guest_goals then 3
                when host_goals = guest_goals then 1
                else  0
            end   
        ) as guest_scores
        from Matches
        group by match_id
)temp
on t.team_id  = temp.host_team or t.team_id = temp.guest_team
group by team_id
order by num_points desc,team_id;