赛题 1：找出各项考试中的佼佼者

DMS截图

SQL源码

WITH
  RankResult AS (
    SELECT
      RANK() OVER (
        PARTITION BY
          testId
        ORDER BY
          score DESC
      ) as rank_num,
      testId,
      studentId,
      score
    FROM
      (
        SELECT
          testId,
          studentId,
          MAX(score) as score
        FROM
          TestAttempt ta
        GROUP BY
          testId,
          studentId
      ) t1
  )
SELECT
  t.name as testname,
  s.name as studentname,
  r.score
FROM
  RankResult r
  LEFT JOIN Test t ON r.testId = t.id
  LEFT JOIN Student s ON r.studentId = s.id
WHERE
  rank_num <= 3

解题思路

1、同一考生在一门考试中可能有多个成绩，且结果仅需要最高分，所以通过GROUP BY和MAX()函数得到每个考生在每一门考试的最高分；
2、对于并列的情况，需要全部列举出来，并且当列举的考生达到或超过了三名，不再列举后面排名的考生，所以使用rank()函数对步骤1中的结果进行不连续排序，得到每门考试的考生的排名；
3、筛选出排序前三的结果，即得到每门考试得分排名前三的考生。

赛题 2：游戏游玩情况

DMS截图

SQL源码

WITH
  T1 AS (
    SELECT
      player_id,
      MIN(event_date) as min_date
    FROM
      Activity a
    GROUP BY
      player_id
  ),
  T2 AS (
    SELECT
      a.player_id,
      DATEDIFF(a.event_date, b.min_date) as diff_date
    FROM
      Activity a
      LEFT JOIN T1 b ON a.player_id = b.player_id
  )
SELECT
  ROUND(
    COUNT(DISTINCT player_id) / (
      SELECT
        COUNT(DISTINCT player_id)
      FROM
        Activity
    ),
    2
  )
FROM
  T2
WHERE
  diff_date > 0
  AND diff_date <= 7

解题思路

1、通过GROUP BY和MIN()函数，得到玩家首次游玩的时间；
2、使用DATEDIFF()函数将玩家游玩时间与首次游玩时间做差，得到玩家每次游玩时间与首次游玩时间差了多少天；
3、筛选出相差天数大于0但小于等于7的结果，并去重统计玩家数，即为首次游玩后的一周内至少再有一次游玩的玩家数量，最后除以总玩家数得到比例。

赛题 3：计算三角形面积

DMS截图

SQL源码

WITH
  T1 AS (
    SELECT
      t.id,
      SQRT(
        POWER((p1.x - p2.x), 2) + POWER((p1.y - p2.y), 2) + POWER((p1.z - p2.z), 2)
      ) as a,
      SQRT(
        POWER((p1.x - p3.x), 2) + POWER((p1.y - p3.y), 2) + POWER((p1.z - p3.z), 2)
      ) as b,
      SQRT(
        POWER((p2.x - p3.x), 2) + POWER((p2.y - p3.y), 2) + POWER((p2.z - p3.z), 2)
      ) as c
    FROM
      Triangle t
      LEFT JOIN Point p1 ON t.pointId1 = p1.id
      LEFT JOIN Point p2 ON t.pointId2 = p2.id
      LEFT JOIN Point p3 ON t.pointId3 = p3.id
  ),
  T2 as (
    SELECT
      id,
      a,
      b,
      c,
      (a + b + c) / 2 as p
    FROM
      T1
  )
SELECT
  id,
  ROUND(SQRT(p * (p - a) * (p - b) * (p - c)), 2) as S
FROM
  T2

解题思路

1、将三角形表与顶点表相关联，得到三角形每个顶点的坐标信息，再根据三维坐标中计算线段长度的公式，计算出三角形三条边的长度；
2、根据三角形三条边的长度，使用海伦公式计算三角形的面积。