阿里云瑶池数据库SQL挑战赛获奖名单及参考答案公布！

本次「阿里云瑶池数据库SQL挑战赛」历时将近一个月，现已圆满落幕，感谢阿里云瑶池数据库的支持以及各位社区朋友的参与。经过大赛初评评审团、大赛专家评审团的评选，最终决出最佳SQL奖1人、优质SQL奖10人、阳光普照奖3人。在此恭喜各位获奖选手，现将获奖名单以及参考答案进行公布！

1.最佳SQL奖

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

恭喜以下用户获得Beats Studio Buds 耳机：

2.优质SQL奖

评奖规则：挑战任意一个赛题并给出正确结果截图+SQL 源码+解题思路的参与者有机会获得 20 元猫超卡，根据提交时间、 SQL 写法、SQL 性能、解题思路综合评选 恭喜以下用户获得20 元猫超卡:

3.阳光普照奖

评奖规则：挑战任意一个赛题并提交结果（不要求结果正确）+SQL 源码的参与者。 恭喜以下用户获得墨天轮100墨值奖励：

参考答案

注：大家可以根据给出的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;

使用海伦公式，要点在于数学公式的SQL实现，不作过多解释。

奖励领取

请获得最佳SQL奖与优质SQL奖的用户尽快联络墨天轮小助手-小墨（微信号：modb666）领取您的奖品；获得阳光普照奖用户的墨值奖励将于3个工作日内发于您的墨天轮账户中，若未收到可联络小墨咨询。再次恭喜各位获奖的朋友，咱们下期活动再见！