题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
http://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
优化了一下日期比较
SELECT
M.product_id,
round( sum( CASE WHEN M.num > 1 THEN 1 ELSE 0 END ) / count( 1 ), 3 ) repurchase_rate
FROM
(
SELECT
tpi.product_id,
count( uid ) num
FROM
tb_product_info tpi
INNER JOIN tb_order_detail tod ON tpi.product_id = tod.product_id
INNER JOIN tb_order_overall too ON too.order_id = tod.order_id
WHERE
tpi.tag = "零食"
AND too.event_time > ( SELECT DATE_SUB(MAX( t.event_time ),INTERVAL 90 DAY) FROM tb_order_overall t )
GROUP BY
tpi.product_id,
too.uid
) M
GROUP BY
M.product_id
ORDER BY
repurchase_rate DESC,
M.product_id
LIMIT 3
