题解 | SQL 171. 零食类商品中复购率top3高的商品

WITH t1 AS (
    SELECT
        a.product_id,
        a.cnt,
        b.uid,
        DATE(b.event_time) AS dt
    FROM tb_order_detail a
    LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
    LEFT JOIN tb_product_info c ON a.product_id = c.product_id
    WHERE b.status = 1 AND c.tag = '零食'
),
t2 AS (
    -- 时间维度表
    SELECT
    max(dt) AS max_dt,
    DATE_ADD(max(dt),INTERVAL -89 DAY) AS min_dt
    FROM t1
),
t3 AS (
    SELECT
    *
    FROM t1
    JOIN t2
    WHERE dt BETWEEN min_dt AND max_dt
),
t4 AS (
    SELECT product_id,
           uid,
           count(uid) AS uid_count,
           count(uid) OVER(PARTITION BY product_id) AS pro_count
    FROM t3
    GROUP BY product_id,uid
    ORDER BY product_id,uid
),
t5 AS (
    SELECT product_id,
           SUM(CASE
               WHEN uid_count >= 2 then 1
               ELSE 0 END
            ) AS flag_sum,
           MAX(pro_count) AS max_pro_count
    FROM t4
    GROUP BY product_id
)
SELECT product_id,
       ROUND(flag_sum / max_pro_count,3) AS repurchase_rate
       FROM t5
ORDER BY repurchase_rate DESC,product_id ASC
LIMIT 3;

# 关键在于top3 记得！