题解 | #构造一个触发器audit_log#

select `date`,round(if(fm!=0,fz/fm,0),3)as p
from(
select `date`,count(distinct case when diff=1 and min_date=`date` then user_id else null end)as fz,count(distinct case when min_date=`date` then user_id else null end)as fm
from(
SELECT*,min(`date`) over(partition by user_id order by `date`)as min_date
from(
select a.user_id,a.date,b.date-a.date as diff
from login a left join login b on a.user_id=b.user_id)a)b
group by `date`)c