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

WITH
	tt AS
	(
		SELECT
			a.product_id,
			shop_id,
			b.order_id,
			event_time,
			total
		FROM
			(
				SELECT
					*,
					COUNT(product_id) over(partition BY shop_id) total
				FROM
					tb_product_info
			)
			a
		LEFT JOIN tb_order_detail b
		ON
			a.product_id = b.product_id
		INNER JOIN tb_order_overall c
		ON
			b.order_id = c.order_id
	)
SELECT
	a.dts dt,
	ROUND(COALESCE(COUNT(DISTINCT product_id) / MAX(total), 0), 3) sale_rate,
	ROUND(COALESCE(1 - COUNT(DISTINCT product_id) / MAX(total), 1), 3) unsale_rate
FROM
	(
	(
		SELECT '2021-10-01' dts
	)

UNION
	(
		SELECT '2021-10-02' dts
	)

UNION
	(
		SELECT '2021-10-03' dts
	)) a INNER JOIN
	(
		SELECT
			SUBSTR(event_time, 1, 10) dts
		FROM
			tt
		GROUP BY
			SUBSTR(event_time, 1, 10)
	)
	b
ON
	a.dts = b.dts
LEFT JOIN
	(
		SELECT * FROM tt WHERE shop_id = '901'
	)
	c on
    DATEDIFF(a.dts, SUBSTR(c.event_time, 1, 10)) BETWEEN 0 AND 6
GROUP BY
	a.dts