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

连续天数技巧dense_rank()加date_sub(N.sales_date, INTERVAL inv DAY)嘿嘿

select
  N.user_id,
  count(N.user_id)
from
  (
    select
      st.sales_date,
      st.user_id,
      dense_rank() OVER(
        PARTITION BY user_id
        order by
          sales_date
      ) inv
    from
      sales_tb st
    group by
      st.user_id,
      st.sales_date
  ) N
group by
  N.user_id,
  date_sub(N.sales_date, INTERVAL inv DAY)
having
  count(N.user_id) > 1