1、根据得分表使用rank开窗函数进行分组排序，获取前三名的学生信息。
with w_a as
(
select d.*,rank() over(partition by testid order by d.score desc) num from testattempt d
)
select t.name test ,s.name student,a.score from w_a a,student s,test t where a.num<=3 and a.studentid=s.id and a.testid=t.id
;
2、使用开窗函数lead将最近的两条数据合并，日期相减如果<=7，即说明为一周内访问了两次，dense_rank用于统计排名，其max的值可认为是总的用户数，因此，SQL如下：
with w_lead AS
(
select d.*,row_number() over(partition by d.player_id order by d.`event_date` ) rnum,
lead(d.event_date) over(partition by d.player_id order by d.`event_date` ) lead_data ,
DENSE_RANK() over(order by d.`player_id`) dank
from `activity` d
)
select count(1)/(select max(dank) from w_lead) from w_lead l where l.lead_data is not null and l.rnum=1 and l.lead_data-l.event_date<=7 order by 1;
3、不会。
