电商数仓-最近7日内连续3日下单用户数

方案一：

select
    count(distinct(user_id))
from
(
    select
        user_id,
        datediff(lead2,dt)diff
    from
    (
        select
        user_id,
        dt,
        lead(dt,2,'9999-12-31') over (partition by user_id order by dt)lead2
        from dws_trade_user_order_1d
        where dt>=date_sub('2020-06-14',6)
    )t1
)t2
where diff=2;

方案二：

select
    count(distinct(user_id))
from
(
    select
        user_id,
        diff,
        count(*) cnt
    from
    (
        select
            user_id,
            date_sub(date_id,rk) diff
        from
        (
            select
                user_id,
                date_id,
                rank() over (partition by user_id order by date_id) rk
            from
            (
                select
                    user_id,
                    date_id
                from dwd_trade_order_detail_inc
                where dt>=date_sub('2020-06-14',6)
                group by user_id, date_id
            )t1
        )t2
    )t3
    group by user_id, diff
)t4
where cnt>=3;

 方案三：

select
    count(*)
from
(
        select
        user_id,
        sum(num) s
    from
    (
        select
            user_id,
            case date_id
                when '2020-06-14' then 1000000
                when '2020-06-13' then 100000
                when '2020-06-12' then 10000
                when '2020-06-11' then 1000
                when '2020-06-10' then 100
                when '2020-06-09' then 10
                when '2020-06-08' then 1
            end num
        from
        (
            select
                user_id,
                date_id
            from dwd_trade_order_detail_inc
            where dt>=date_sub('2020-06-14',6)
            group by user_id, date_id
        )t1
    )t2
    group by user_id
)t3
where cast(s as string)like '%111%';