阿里云瑶池数据库SQL大赛获奖名单及参考答案-阿里云开发者社区

阿里云瑶池数据库SQL大赛获奖名单及参考答案

2023-06-14 714

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 阿里云瑶池数据库SQL大赛获奖名单及参考答案

阿里云瑶池数据库SQL挑战赛于6月19日正式落下帷幕。自5月份开赛以来，我们收到了100多份优秀的参赛作品，感谢各位社区朋友的参与！参赛作品评审采用“初评+终评”的方式，前期评审小组初评，终评技术专家审核的方式完成评审工作。最终评选出最佳 SQL 奖（ 3位）、人气 SQL 奖（4位）、优质 SQL 奖（70位）现将获奖名单以及参考答案进行公布！

活动规则链接：https://developer.aliyun.com/mission/activity/sql

1.最佳SQL奖

评奖规则：挑战三个赛题并给出正确结果截图+SQL 源码+解题思路的参与者可参与评选，根据提交时间、 SQL 写法、SQL 性能、解题思路综合评选3 位获奖者

恭喜以下用户获得Beats Studio Buds 耳机，我们将在三个工作日内通过阿里云开发者社区站内信收集获奖地址，请大家注意查收！

获奖人	赛题答案（点击超链接查看详细内容）	综合用时	专家组评价
ug4nypj2jl7cm	玩法平台-阿里云数据库SQL挑战赛-任务组的测评	120ms	思路清晰，性能优越，考虑周全，解题思路也很清晰
1637228461567078	参与瑶池数据库SQL挑战赛的测评	134ms	代码很规整，格式很规范，性能优越，也用了高阶的SQL写法来做实现，值得大家学习
游客h42bnxufjiuea	参与瑶池数据库SQL挑战赛的测评	129ms	有高阶SQL使用方法，性能也很好，由于题目描述的原因对于第二题“首周”的理解有偏差，但是不影响对于题目的解答

2.人气SQL奖

评奖规则：挑战任意一个赛题并给出正确结果截图+SQL 源码+解题思路的参与者可参与评选，按作品点赞数排序，排名前三位的参与者。

恭喜以下用户获得小熊煮蛋器，我们将在三个工作日内通过1积分发送兑换资格，请大家关注社区积分待领取提醒

获奖人	赛题答案（点击超链接查看详细内容）	点赞数量
CHENGANG	参与瑶池数据库SQL挑战赛的测评	21
辣辣滴小罗号	玩法平台-阿里云数据库SQL挑战赛-任务组的测评	16
nb@plus	参与瑶池数据库SQL挑战赛的测评	14
owen_wang2023	参与瑶池数据库SQL挑战赛的测评	14

3.优质SQL奖

评奖规则：挑战任意一个赛题并给出正确结果截图+SQL 源码+解题思路的参与者

恭喜以下70位用户获得20元猫超卡，我们将在三个工作日内通过短信进行发放，请大家注意查收！

4.阳光普照奖

评奖规则：挑战任意一个赛题并提交结果（不要求结果正确）+SQL 源码的参与者

所有提交作品通过了审核的参与者均可获得100积分，积分已在上周发放完毕，请大家到社区积分商城领取。

阿里云瑶池数据库SQL大赛参考答案

注：大家可以根据给出的SQL以及解题思路跑出答案哦~

第一题

SELECT
  t.name AS Test,
  s.name AS Student,
  ta.score AS Score
FROM
  (
    SELECT
      testId,
      MAX(score) AS max_score
    FROM
      TestAttempt
    GROUP BY
      testId
  ) AS temp
  JOIN Test AS t ON temp.testId = t.id
  JOIN TestAttempt AS ta ON temp.testId = ta.testId
  AND temp.max_score = ta.score
  JOIN Student AS s ON ta.studentId = s.id
WHERE
  (
    SELECT
      COUNT(DISTINCT score)
    FROM
      TestAttempt
    WHERE
      testId = temp.testId
      AND score >= temp.max_score
  ) <= 3
ORDER BY
  t.name,
  ta.score DESC

解题思路：首先，筛选出每个学生在每门考试中的最高分，再将每个学生的最高分和其他学生的最高分进行对比，如果有更高分或平分的人的总数不超过三个，那么这个考生的最高分会被列入。

第二题

SELECT
  ROUND(CAST(repeat_players AS FLOAT) / total_players, 2) AS fraction
FROM
  (
    SELECT DISTINCT
      COUNT(DISTINCT a1.player_id) AS repeat_players,
      (
        SELECT
          COUNT(DISTINCT player_id)
        FROM
          Activity
      ) AS total_players
    FROM
      Activity a1
      JOIN (
        SELECT
          player_id,
          MIN(event_date) AS first_play_date
        FROM
          Activity
        GROUP BY
          player_id
      ) a2 ON a1.player_id = a2.player_id
    WHERE
      a1.event_date BETWEEN a2.first_play_date AND DATE_ADD(a2.first_play_date, INTERVAL 6 DAY)
      AND a1.games_played > 0
      AND a1.event_date <> a2.first_play_date
  ) t

解题思路：这题的重点是把所有首周内有至少两次登录的用户，因此，将所有玩家的游玩日期和他们首次游玩的日期进行比较，有在七天以内的进行记录。这边使用了MySQL的date interval，配合BETWEEN，7天之内的话应该是试用INTERVAL 6 DAY，但是使用INTERVAL 7 DAY的也算正确。

第三题

SELECT
    t.id,
    ROUND(SQRT(t.p*(t.p-t.a)*(t.p-t.b)*(t.p-t.c)), 2) AS area
FROM (
    SELECT 
        triangle.id,
        ROUND(SQRT(POWER(p1.x-p2.x,2)+POWER(p1.y-p2.y,2)+POWER(p1.z-p2.z,2)), 2) AS a,
        ROUND(SQRT(POWER(p1.x-p3.x,2)+POWER(p1.y-p3.y,2)+POWER(p1.z-p3.z,2)), 2) AS b,
        ROUND(SQRT(POWER(p2.x-p3.x,2)+POWER(p2.y-p3.y,2)+POWER(p2.z-p3.z,2)), 2) AS c,
        ROUND((ROUND(SQRT(POWER(p1.x-p2.x,2)+POWER(p1.y-p2.y,2)+POWER(p1.z-p2.z,2)),2)
             +ROUND(SQRT(POWER(p1.x-p3.x,2)+POWER(p1.y-p3.y,2)+POWER(p1.z-p3.z,2)),2)
             +ROUND(SQRT(POWER(p2.x-p3.x,2)+POWER(p2.y-p3.y,2)+POWER(p2.z-p3.z,2)),2))/2, 2) AS p
    FROM 
        triangle
        JOIN point p1 ON pointid1 = p1.id 
        JOIN point p2 ON pointid2 = p2.id
        JOIN point p3 ON pointid3 = p3.id
) t 
ORDER BY id;