快手数据分析面经｜快手电商实习

SQL，小白尝试，直接求主播打赏的总金额大于100000，然后，利用窗口函数排序即可。 小白瞎写，请大佬多多指教，想的比较简单 select t1.author_id //主播ID， t1.py //主播收入总金额 from( select author_id, py, dense_rank()over(order by py desc) as m //打赏总金额排序(where 筛出大于100000的人后排名) from( select author_id, sum(total_cost) as py,//打赏主播总金额 from dashangbiao group by author_id ) as t where t.py>100000 //筛出大于100000的主播 ） as t1 where t1.m<=3

求一个BG

同sql小白来交流一下答案，说下思路：首先按user_id, author_id分组计算SUM(total_cost) 重命名为cost，并按user_id, author_id分组对sum(total_cost)排序，排序记为rank，记为表t。从表t中按筛选user_id分组，计算SUM(cost)，再用having筛选出SUM(cost)≥100000并且rank≤3的行，筛选列包括：user_id, author_id, SUM(cost) ua_cost，结果按照user_id升序，ua_cost降序。

请问一般当面写代码会给多少时间啊 可以像牛客一样自测嘛 感谢！

小白求解：with cte as( select user_id from rewards group by user_id having sum(total_cost) > 10000 ) select author_id,sum_total_cost from( select author_id,sum_total_cost,dense_rank() over (order by sum_total_cost desc) as rk from( select author_id,sum(total_cost) as sum_total_cost from cte left join rewards using(user_id) group by author_id ) t1 ) t2 where rk <= 3;