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