mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-12-15 23:22:36 +08:00
99 lines
4.9 KiB
HTML
99 lines
4.9 KiB
HTML
<p>表：<code>course_completions</code></p>
|
||
|
||
<pre>
|
||
+-------------------+---------+
|
||
| Column Name | Type |
|
||
+-------------------+---------+
|
||
| user_id | int |
|
||
| course_id | int |
|
||
| course_name | varchar |
|
||
| completion_date | date |
|
||
| course_rating | int |
|
||
+-------------------+---------+
|
||
(user_id, course_id) 是此表中具有不同值的列的组合。
|
||
每一行代表一个用户完成的课程及其评分（1-5 分）。
|
||
</pre>
|
||
|
||
<p>编写一个解决方案，通过分析顶尖学生完成课程的序列来识别 <strong>课程路径</strong>：</p>
|
||
|
||
<ul>
|
||
<li>只考虑 <strong>顶尖学生</strong>（完成 <strong>至少</strong> <code>5</code> <strong>门课程且平均评分</strong> <code>4</code> <strong>分或以上 </strong>的人）。</li>
|
||
<li>对每个顶尖学生，确定他们按时间顺序完成的 <strong>课程序列</strong>。</li>
|
||
<li>找出这些学生所学的所有 <strong>连续课程对 </strong>（<code>课程 A → 课程 B</code>）。</li>
|
||
<li>返回课程对的频率，确定顶尖学生中最常见的课程路径。</li>
|
||
</ul>
|
||
|
||
<p>返回结果表，按课程对频率 <strong>降序</strong> 排列，若频率相同则按第一课程名称和第二课程名称 <strong>升序</strong> 排列。</p>
|
||
|
||
<p>结果格式如下所示。</p>
|
||
|
||
<p> </p>
|
||
|
||
<p><strong class="example">示例：</strong></p>
|
||
|
||
<div class="example-block">
|
||
<p><strong>输入：</strong></p>
|
||
|
||
<p>course_completions 表：</p>
|
||
|
||
<pre class="example-io">
|
||
+---------+-----------+------------------+-----------------+---------------+
|
||
| user_id | course_id | course_name | completion_date | course_rating |
|
||
+---------+-----------+------------------+-----------------+---------------+
|
||
| 1 | 101 | Python Basics | 2024-01-05 | 5 |
|
||
| 1 | 102 | SQL Fundamentals | 2024-02-10 | 4 |
|
||
| 1 | 103 | JavaScript | 2024-03-15 | 5 |
|
||
| 1 | 104 | React Basics | 2024-04-20 | 4 |
|
||
| 1 | 105 | Node.js | 2024-05-25 | 5 |
|
||
| 1 | 106 | Docker | 2024-06-30 | 4 |
|
||
| 2 | 101 | Python Basics | 2024-01-08 | 4 |
|
||
| 2 | 104 | React Basics | 2024-02-14 | 5 |
|
||
| 2 | 105 | Node.js | 2024-03-20 | 4 |
|
||
| 2 | 106 | Docker | 2024-04-25 | 5 |
|
||
| 2 | 107 | AWS Fundamentals | 2024-05-30 | 4 |
|
||
| 3 | 101 | Python Basics | 2024-01-10 | 3 |
|
||
| 3 | 102 | SQL Fundamentals | 2024-02-12 | 3 |
|
||
| 3 | 103 | JavaScript | 2024-03-18 | 3 |
|
||
| 3 | 104 | React Basics | 2024-04-22 | 2 |
|
||
| 3 | 105 | Node.js | 2024-05-28 | 3 |
|
||
| 4 | 101 | Python Basics | 2024-01-12 | 5 |
|
||
| 4 | 108 | Data Science | 2024-02-16 | 5 |
|
||
| 4 | 109 | Machine Learning | 2024-03-22 | 5 |
|
||
+---------+-----------+------------------+-----------------+---------------+
|
||
</pre>
|
||
|
||
<p><strong>输出：</strong></p>
|
||
|
||
<pre class="example-io">
|
||
+------------------+------------------+------------------+
|
||
| first_course | second_course | transition_count |
|
||
+------------------+------------------+------------------+
|
||
| Node.js | Docker | 2 |
|
||
| React Basics | Node.js | 2 |
|
||
| Docker | AWS Fundamentals | 1 |
|
||
| JavaScript | React Basics | 1 |
|
||
| Python Basics | React Basics | 1 |
|
||
| Python Basics | SQL Fundamentals | 1 |
|
||
| SQL Fundamentals | JavaScript | 1 |
|
||
+------------------+------------------+------------------+
|
||
</pre>
|
||
|
||
<p><strong>解释：</strong></p>
|
||
|
||
<ul>
|
||
<li><strong>用户 1：</strong>完成了 6 门课程，平均分为 4.5（满足顶尖学生）</li>
|
||
<li><strong>用户 2：</strong>完成了 5 门课程，平均分为 4.4（满足顶尖学生）</li>
|
||
<li><strong>用户 3：</strong>完成了 5 门课程但平均得分为 2.8（不满足资格）</li>
|
||
<li><strong>用户 4：</strong>只完成了 3 门课程（不满足资格）</li>
|
||
<li><strong>顶尖学生的课程对：</strong>
|
||
<ul>
|
||
<li>用户 1：Python Basics → SQL Fundamentals → JavaScript → React Basics → Node.js → Docker</li>
|
||
<li>用户 2：Python Basics → React Basics → Node.js → Docker → AWS Fundamentals</li>
|
||
<li>最常见的路径：Node.js → Docker (2 次)，React Basics → Node.js (2 次)</li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
|
||
<p>结果按 transition_count 降序排列，然后按 first_course 升序排列，再按 second_course 升序排列。</p>
|
||
</div>
|