题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
http://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
连续天数技巧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
查看24道真题和解析