题解 | #店铺901国庆期间的7日动销率和滞销率#

店铺901国庆期间的7日动销率和滞销率

https://www.nowcoder.com/practice/e7837f66e8fb4b45b694d24ea61f0dc9

该题目的难点是题意的理解，本人的思路如下：

1. 取出10月1日到10月2日的时间，作为临时表time_table.

## 10-01~10-03的时间
    SELECT
        DISTINCT DATE(event_time) event_time
    FROM    
        tb_order_overall
    WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'

2. 10-01~03每一天901号店的前7日销售商品数量,作为临时表day_sell。

	# 10-01~03每一天901号店的前7日销售商品数量
	SELECT 
			tmp1.event_time, COUNT(DISTINCT tmp2.product_id) day_cnt
	FROM 
		time_table tmp1
	LEFT JOIN (
		SELECT 
			DATE(t3.event_time) event_time,t1.product_id
		FROM 
			tb_product_info t1
		JOIN 
			tb_order_detail t2
		ON t1.product_id = t2.product_id 
		JOIN	
			tb_order_overall t3
		ON t2.order_id = t3.order_id
		WHERE t1.shop_id = '901'
	)tmp2 
	ON DATEDIFF(tmp1.event_time,tmp2.event_time) <7 AND DATEDIFF(tmp1.event_time,tmp2.event_time) >= 0 	
	GROUP BY  tmp1.event_time

3. 10月01~03每一日901号店的上架商品数量，作为临时表release_table。

	## 10月01~03每一日901号店的上架商品数量
	SELECT
		tmp1.event_time, COUNT(DISTINCT tmp2.product_id) total
	FROM 
		time_table tmp1
	JOIN 
		tb_product_info tmp2
	ON DATEDIFF(tmp1.event_time,tmp2.release_time) >= 0
	WHERE tmp2.shop_id = '901'
	GROUP BY tmp1.event_time

4. 按照要求，关联以上三张临时表，求出题目中的指标数据。

整体的sql如下：

WITH time_table AS(
## 10-01~10-03的时间
    SELECT
        DISTINCT DATE(event_time) event_time
    FROM    
        tb_order_overall
    WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
),
day_sell AS(
	# 10-01~03每一天901号店的前7日销售商品数量
	SELECT 
			tmp1.event_time, COUNT(DISTINCT tmp2.product_id) day_cnt
	FROM 
		time_table tmp1
	LEFT JOIN (
		SELECT 
			DATE(t3.event_time) event_time,t1.product_id
		FROM 
			tb_product_info t1
		JOIN 
			tb_order_detail t2
		ON t1.product_id = t2.product_id 
		JOIN	
			tb_order_overall t3
		ON t2.order_id = t3.order_id
		WHERE t1.shop_id = '901'
	)tmp2 
	ON DATEDIFF(tmp1.event_time,tmp2.event_time) <7 AND DATEDIFF(tmp1.event_time,tmp2.event_time) >= 0 	
	GROUP BY  tmp1.event_time
),
release_table AS(
	## 10月01~03每一日901号店的上架商品数量
	SELECT
		tmp1.event_time, COUNT(DISTINCT tmp2.product_id) total
	FROM 
		time_table tmp1
	JOIN 
		tb_product_info tmp2
	ON DATEDIFF(tmp1.event_time,tmp2.release_time) >= 0
	WHERE tmp2.shop_id = '901'
	GROUP BY tmp1.event_time
)
SELECT
	tmp1.event_time,ROUND(tmp1.day_cnt / tmp2.total,3) sale_rate,
	1 - ROUND(tmp1.day_cnt / tmp2.total,3) unsale_rate
FROM 
	day_sell tmp1
JOIN 
	release_table tmp2
USING(event_time)
ORDER BY tmp1.event_time;