错题丨复购率&多少时间内、间隔多少时间处理
零食类商品中复购率top3高的商品
https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3?tpId=268&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268
复购率中的复购人数就用开窗用产品和uid分组就好了， 但是时间间隔在90天内
就不要用datediff条件函数来算间隔条件，因为它里面是后者时间减前者时间，顺序有可能放错
但是你用DATE_SUB就不会有错误，因为是直接用时间间隔算的，
你是在3天内就是2天的间隔，90天内就是89天的间隔当天也是算的
详细如： https://blog.nowcoder.net/n/06ddcb4a6b17451aab8ecf99f863750d
SELECT
product_id,ROUND(sum(CASE WHEN ranking>1 THEN 1 ELSE 0 END)/COUNT(DISTINCT uid),3)repurchase_rate
from (
SELECT
product_id,
uid,
ROW_number() over(PARTITION by product_id,uid ORDER BY event_time) ranking
FROM tb_product_info
LEFT JOIN tb_order_detail USING(product_id)
LEFT JOIN tb_order_overall USING(order_id)
WHERE tag='零食'
AND status=1
AND event_time>=DATE_SUB((select max(event_time) from tb_order_overall),INTERVAL 89 day))t
GROUP BY product_id
ORDER BY repurchase_rate DESC,product_id
limit 3
查看4道真题和解析