题解 | 查询连续入住多晚的客户信息？

with
t1 as(
    select
        user_id,
        ct.room_id as room_id,
        gt.room_type as room_type,
        checkin_time,
        checkout_time,
        timestampdiff(day,checkin_time,checkout_time) +
            case
                when timestampdiff(second,checkin_time,checkout_time)%86400>0 then 1
                else 0
            end as days
    from
        checkin_tb as ct left join guestroom_tb as gt using(room_id)
)
,t2 as(
    select
        user_id,
        room_id,
        room_type,
        days,
        rank()over(partition by room_id order by days desc) as drank
    from
        t1
    where
        days>=2
)
,t3 as(
    select
        user_id,
        room_id,
        room_type,
        days
    from
        t2
    where
        drank=1
    order by
        days,
        room_id,
        user_id desc
)


select * from t3