题解 | #每天的日活数及新用户占比#

select
    dt,
    count(*),
    round(avg(s), 2)
from
    (
        select
            a1.uid,
            a1.dt,
            if (a1.dt = a2.dc, 1, 0) as s
        from
            (
                select
                    uid,
                    dt,
                    if (
                        datediff (
                            lead (dt, 1, 0) over (
                                partition by
                                    uid
                                order by
                                    dt
                            ),
                            dt
                        ) = 1,
                        1,
                        0
                    ) as df
                from
                    (
                        select
                            uid,
                            dt
                        from
                            (
                                select
                                    uid,
                                    artical_id,
                                    date (in_time) as dt
                                from
                                    tb_user_log
                                union all
                                select
                                    uid,
                                    artical_id,
                                    date (out_time) as dt
                                from
                                    tb_user_log
                            ) a
                        group by
                            dt,
                            uid
                    ) b
            ) a1
            left join (
                select
                    uid,
                    min(date (in_time)) as dc
                from
                    tb_user_log
                group by
                    uid
            ) a2 on a1.uid = a2.uid
    ) va
group by
    dt