题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

SELECT 
user_id,
MAX(rk2) AS days_count

    FROM(
	  SELECT
	  *,
	  DENSE_RANK() OVER(PARTITION BY user_id, DATE_SUB(sales_date, INTERVAL rk1 DAY)  ORDER BY sales_date) AS rk2
	      FROM(
			SELECT
			*,
			DENSE_RANK() OVER(PARTITION BY user_id ORDER BY sales_date) AS rk1
			FROM sales_tb

			WHERE LEFT(sales_date, 7) = '2021-11') AS t1) AS t2

    WHERE rk2 >= '2'

GROUP BY user_id