阿里云sql挑战赛

题目链接

第一题

with cte1 AS 
(
  select  `studentId` , `testId` ,max(`score`)  as score 
  from `testattempt` 
  GROUP BY  `studentId`, `testId` 
)
,cte2 AS 
(
select studentid,testid,`score` ,
dense_rank() over(PARTITION  by  testid  order by `score`  desc ) as rn
from cte1
)
select a.name,b.name,c.score 
from cte2 as c
inner join `student` a on c.studentid = a.`id` 
inner join `test` b on c.testid = b.`id` 
where c.rn <=3
ORDER BY  `testId`,score desc 

第一题思路
第一步求出每位学生每科的最好成绩
第二步求出每科的排行
第三步取每科排行的前三

第二题

with cte1 as
(
select player_id,min(event_date) as first_date
from activity 
group by player_id
)
,cte2 as
(
select count(*) allcnt from  cte1
)
select  round(count(*) / (select allcnt from cte2 ),2) from cte1 as a
where exists(select 1 from  activity b where  a.player_id = b.player_id and b.event_date <> a.first_date and DATEDIFF(b.event_date,a.first_date) <=7 )

第二题思路
第一步求出每个玩家的最早登陆时间
第二步求出所有玩家总数
第三步求出第二次登陆与第一次登陆小于7天的玩家 除以总玩家数

第三题

select c.id,
round(sqrt( power((a.y-o.y)*(b.z - o.z)-(a.z-o.z)*(b.y-o.y),2) +power((a.z-o.z)*(b.x-o.x) -(a.x - o.x)*(b.z - o.z),2) + power((a.x - o.x)*(b.y  - o.y)- (a.y-o.y)*(b.x - o.x),2)) /2,2) as Area
from Triangle as c
inner join Point a on c.pointid2 = a.id
inner join Point b on c.pointid3  = b.id
inner join point o on c.pointid1  = o.id

第三题思路
关联坐标表，根据向量叉求面积