题解 | 最受欢迎的top3课程

with
    details as (
        select
            t.cid,
            pv,
            time_len,
            row_number() over ( /*窗口函数只能出现在子查询，分布查询或者order by 里*/
            /*这里千万不能按照课程id分组，因为目标是符合条件的每个课程输出为一行，加上partition by 就是在相同的cid中排序，这就错了*/
                order by
                    pv desc,
                    time_len desc
            ) as rk
        from
            (
                select
                    p.cid,
                    count(*) as pv,
                    round(
                        sum(timestampdiff(minute, start_time, end_time)),
                        3
                    ) as time_len
                from
                    play_record_tb p
                    join course_info_tb c on p.cid = c.cid
                where /*SQL不支持连续比较符，需要用and连接（有时可以用between---and---)*/
                    release_date <= date(start_time) 
                    and date(start_time) < release_date + interval "7" day  
/*也可以用dateadd(release_date,interval "7" day)，注意是day不是days*/
                    and start_time < end_time
                group by
                    p.cid
                having avg(score) >= 3
                /*avg()也是聚合函数，不能用在where里*/
            ) t
    )

select details.cid
,pv
,time_len
from details
where rk <= 3
order by rk