题解 | 未下单用户登陆渠道统计

#不使用group by和join而是窗口函数的写法

select
    channel,
    cnt
from
    (
        select
            channel,
            cnt,
            row_number() over(order by cnt desc, channel) as ranking
        from
            (
                select
                    channel,
                    count(*) over(partition by channel) as cnt
                from
                    (
                        select
                            channel
                        from
                            user_info
                        where not exists(
                            select 1
                            from order_log
                            where user_info.uid=order_log.uid
                        )
                    ) as channel
            ) as tmp1
    ) as tmp2
where ranking=1
limit 1