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

WITH temp AS (
    SELECT
        series_id,
        series_name,
        COUNT(DISTINCT user_id) total
    FROM
        t_series ts RIGHT JOIN t_order to2 USING(series_id)
    GROUP BY
        series_id, series_name
),

temp2 AS(
SELECT
    series_id,
    SUM(DISTINCT d1) d1,
    SUM(DISTINCT d3) d3,
    SUM(DISTINCT d7) d7,
    SUM(DISTINCT d14) d14
FROM
(
SELECT
    user_id,
    series_id,
    pay_time,
    TIMESTAMPDIFF(DAY, first_pay_time, pay_time) t_diff,
    IF(TIMESTAMPDIFF(DAY, first_pay_time, pay_time)=1, 1, 0) d1,
    IF(TIMESTAMPDIFF(DAY, first_pay_time, pay_time)=3, 1, 0) d3,
    IF(TIMESTAMPDIFF(DAY, first_pay_time, pay_time)=7, 1, 0) d7,
    IF(TIMESTAMPDIFF(DAY, first_pay_time, pay_time)=14, 1, 0) d14
FROM
(
SELECT
    user_id,
    series_id,
    DATE(pay_time) pay_time,
    MIN(DATE(pay_time)) OVER(PARTITION BY user_id, series_id) first_pay_time
FROM
    t_order to2
) t1
) t2
GROUP BY
    series_id

)

SELECT
    t.series_id,
    series_name,
    total first_buyer_cnt,
    ROUND(d1 / total, 2) d1_rate,
    ROUND(d3 / total, 2) d3_rate,
    ROUND(d7 / total, 2) d7_rate,
    ROUND(d14 / total, 2) d14_rate
FROM
    temp t JOIN temp2 t2 USING(series_id)
ORDER BY
    d7_rate DESC, first_buyer_cnt DESC, series_id ASC