牛客sql练习记录：sql28,29

SQL28 计算用户8月每天的练题数量

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

 思考：在于是否会截取日期

 _牛客网

正确答案：

select day(date) as day,
       count(question_id) as question_cnt
from question_practice_detail
where year(date) = 2021 and month(date) = 08
group by day

 SQL29 计算用户的平均次日留存率

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

好难TT

 思路：第二天还会来刷题—> 第一天也刷了—> date不同，date相差1，device_id相同

次日留存率=（去重的数据表中符合次日留存的条目数目）/（去重的数据表中所有条目数目）

(这个公式百度的)

去重的数据表中符合次日留存的条目数目 = 第二天也来参加的device_id

—> 查询日期增加1天，但是device_id相同（由于可能会存在一个id在一天内刷了2次题记录，需要去重）

—> xx.device_id =xx.device_id and  datediff(q2.date, q1.date) = 1

—> xx需要不同，不然是同样的值进行查询没有意义,可是只有一个表?

—> 使用自联结，将device_id和date插入当成q2.device_id和q2.date

—> from question_practice_detail as q1,question_practice_detail as q2 

       where q1.device_id = q2.device_id and datediff(q2.date, q1.date) = 1

—> 所以！第二天也来参加的device_id是q2.device_id 

去重的数据表中所有条目数目 = count（distinct q1.device_id）

疑问：count里面可以包裹distinct吗？

可以，count（distinct XXXX）

得出 次日留存率 = count（distinct q2.device_id）/ count( distinct q1.device_id) as avg_ret

select
    count(distinct q2.device_id,q2.date) / count(distinct q1.device_id,q1.date) as avg_ret
from
    question_practice_detail as q1,
    question_practice_detail as q2
where
    q1.device_id = q2.device_id
    and datediff(q2.date, q1.date) = 1

运行错误，查看别人的代码，发现是用left join

正确答案：

select  count(distinct q2.device_id,q2.date) / count(distinct q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 
left join  question_practice_detail as q2
    on  q1.device_id=q2.device_id  and datediff(q2.date,q1.date)=1