第一题
编写一个SQL查询，找出每个考试中 得分最高的的考生 。
DMS截图
SQL源码
SELECT
Test.name AS TestName,
Student.name AS StudentName,
BestScore.score AS Score
FROM
(
SELECT
studentId,
testId,
MAX(score) AS score
FROM
TestAttempt
GROUP BY
studentId, testId
) AS BestScore
JOIN TestAttempt ON BestScore.studentId = TestAttempt.studentId AND BestScore.testId = TestAttempt.testId AND BestScore.score = TestAttempt.score
JOIN Test ON Test.id = TestAttempt.testId
JOIN Student ON Student.id = TestAttempt.studentId
WHERE
(TestAttempt.testId, BestScore.score) IN (
SELECT
testId,
score
FROM
(
SELECT
testId,
studentId,
score,
DENSE_RANK() OVER (PARTITION BY testId ORDER BY score DESC) AS ExamRank
FROM
(
SELECT
studentId,
testId,
MAX(score) AS score
FROM
TestAttempt
GROUP BY
studentId, testId
) AS BestScores
) AS T
WHERE
ExamRank <= 3
)
ORDER BY
Test.name, BestScore.score DESC;
解题思路
可以使用窗口函数 DENSE_RANK() 对每门考试中的学生成绩进行排名，同时使用 PARTITION BY 定义每门考试作为一个分区；
然后从 Test 表和上一步得到的分数排名结果中 JOIN 出每个考试对应的前三名考生的信息；
最后在最外层查询中，根据排名确定每门考试的得分最高的学生，并按照要求返回结果表。
第二题
编写一个 SQL 查询，报告在首次游玩后的一周内至少再有一次游玩的玩家的比例，也就是注册首周内至少有两次登录的玩家占总玩家的比例，四舍五入到小数点后两位。
DMS截图
SQL源码
SELECT
ROUND(
(SELECT COUNT(DISTINCT player_id)
FROM
(SELECT player_id
FROM Activity
WHERE DATEDIFF(event_date,
(SELECT MIN(event_date)
FROM Activity AS a
WHERE a.player_id = Activity.player_id)) BETWEEN 1 AND 7
) AS WithinWeekPlayers
)
/
(SELECT COUNT(DISTINCT player_id) FROM Activity), 2
) AS PlayerRatio
解题思路
1. SELECT MIN(event_date) FROM Activity AS a WHERE a.player_id = Activity.player_id 查找每个玩家的首次游玩日期。
2. DATEDIFF(event_date, <首次游玩日期>) BETWEEN 1 AND 7 确定首次游玩后的一周内的活动。
3. SELECT COUNT(DISTINCT player_id) FROM <步骤2的结果> 计算在首次游玩后的一周内至少再有一次游玩的玩家数量。
4. SELECT COUNT(DISTINCT player_id) FROM Activity 计算总玩家数量。
5. 步骤3的结果 / 步骤4的结果 得出在首次游玩后的一周内至少再有一次游玩的玩家的比例。
6. ROUND(<步骤5的结果>, 2) 将结果四舍五入到小数点后两位。
第三题
写一个SQL查询，计算每个三角形的面积，保留两位小数。
DMS截图
SQL源码
SELECT
d.id, ROUND(0.5 * SQRT(pow(a, 2) + pow(b, 2) + pow(c, 2)), 2) AS area
FROM (
SELECT triangle.id
, (p2.y - p1.y) * (p3.z - p1.z) - (p3.y - p1.y) * (p2.z - p1.z) AS a
, (p2.z - p1.z) * (p3.x - p1.x) - (p2.x - p1.x) * (p3.z - p1.z) AS b
, (p2.x - p1.x) * (p3.y - p1.y) - (p3.x - p1.x) * (p2.y - p1.y) AS c
FROM triangle
JOIN point p1 ON pointid1 = p1.id
JOIN point p2 ON pointid2 = p2.id
JOIN point p3 ON pointid3 = p3.id
) d
ORDER BY d.id;
解题思路
计算三角形面积的一种常见方法是使用海伦公式，但在三维空间中，我们不能直接使用它。
首先计算出每个三角形每条边的长度，然后利用向量积的长度公式计算三角形的面积.
首先通过JOIN操作获取每个三角形三个顶点的坐标，然后利用向量积的长度公式计算面积，最后使用ROUND函数将结果保留到小数点后两位。
