阿里云瑶池数据库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

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;