题解 | 25年得物-潮鞋新品发售后 N 日复购留存矩阵

with
    t_order as (
        select distinct
            user_id,
            series_id,
            date(pay_time) as pay_time
        from
            t_order
    )
select
    series_id,
    series_name,
    first_buyer_cnt,
    round(cnt1 / first_buyer_cnt, 2) as d1_rate,
    round(cnt2 / first_buyer_cnt, 2) as d3_rate,
    round(cnt3 / first_buyer_cnt, 2) as d7_rate,
    round(cnt4 / first_buyer_cnt, 2) as d14_rate
from
    (
        select
            series_id,
            series_name,
            count(distinct user_id) as first_buyer_cnt,
            sum(if(pay_time = d1, 1, 0)) as cnt1,
            sum(if(pay_time = d2, 1, 0)) as cnt2,
            sum(if(pay_time = d3, 1, 0)) as cnt3,
            sum(if(pay_time = d4, 1, 0)) as cnt4
        from
            (
                select
                    series_id,
                    series_name,
                    user_id,
                    pay_time,
                    date_add(d0, interval 1 day) d1,
                    date_add(d0, interval 3 day) d2,
                    date_add(d0, interval 7 day) d3,
                    date_add(d0, interval 14 day) d4
                from
                    (
                        select
                            series_id,
                            series_name,
                            user_id,
                            pay_time,
                            min(pay_time) over (
                                partition by
                                    series_id,
                                    user_id
                            ) as d0
                        from
                            t_series
                            join t_order using (series_id)
                    ) as t1
            ) as t2
        group by
            1,
            2
    ) as temp1
order by
    d7_rate desc,
    first_buyer_cnt desc,
    series_id;