Algorithm

LeetCode - Study Play - SQL 50

Crack SQL Interview in 50 Qs

本周(20260824-20260828)完成LeetCode的SQL 50系列题目。 主要目标是快速回顾数据库查询相关基础知识。

系列共计50道题目，难度包含简易、中级和困难，考察面覆盖查询、关联、聚合、排序、分组、子查询。现将题目情况做简要记录。

整体上讲，系列题目还是很简单的，考察的都是基本的知识，不存在超出能力范围的内容，主要时间用于阅读题目和查阅文档。 但以PostgreSQL解答问题时，也暴露部分短板，例如对于某些语句的陌生(SELECT DISTINCT ON (a) a FROM x ORDER BY a)，窗口查询的覆盖尚不够全面，部分数据库语法、函数仍需查阅文档等。 此外，尝试Pandas解答问题也许是有必要的。

基本查询(5E)

  1. [简单] 1757. Recyclable and Low Fat Products
  2. [简单] 584. Find Customer Referee
  3. [简单] 595. Big Countries
  4. [简单] 1148. Article Views I
  5. [简单] 1683. Invalid Tweets

基本关联(7E+2M)

  1. [简单] 1378. Replace Employee ID With The Unique Identifier
  2. [简单] 1068. Product Sales Analysis I
  3. [简单] 1581. Customer Who Visited but Did Not Make Any Transactions
  4. [简单] 197. Rising Temperature
  5. [简单] 1661. Average Time of Process per Machine
  6. [简单] 577. Employee Bonus
  7. [简单] 1280. Students and Examinations
  8. [中等] 570. Managers with at Least 5 Direct Reports
  9. [中等] 1934. Confirmation Rate

基本聚合函数(5E+3M)

  1. [简单] 620. Not Boring Movies
  2. [简单] 1251. Average Selling Price
  3. [简单] 1075. Project Employees I
  4. [简单] 1633. Percentage of Users Attended a Contest
  5. [简单] 1211. Queries Quality and Percentage
  6. [中等] 1193. Monthly Transactions I
  7. [中等] 1174. Immediate Food Delivery II
  8. [中等] 550. Game Play Analysis IV

排序和分组(5E+2M)

  1. [简单] 2356. Number of Unique Subjects Taught by Each Teacher
  2. [简单] 1141. User Activity for the Past 30 Days I
  3. [中等] 1070. Product Sales Analysis III
  4. [简单] 596. Classes With at Least 5 Students
  5. [简单] 1729. Find Followers Count
  6. [简单] 619. Biggest Single Number
  7. [中等] 1045. Customers Who Bought All Products

高级查询和关联(3E+4M)

  1. [简单] 1731. The Number of Employees Which Report to Each Employee
  2. [简单] 1789. Primary Department for Each Employee
  3. [简单] 610. Triangle Judgement
  4. [中等] 180. Consecutive Numbers
  5. [中等] 1164. Product Price at a Given Date
  6. [中等] 1204. Last Person to Fit in the Bus
  7. [中等] 1907. Count Salary Categories

子查询(1E+5M+1H)

  1. [简单] 1978. Employees Whose Manager Left the Company
  2. [中等] 626. Exchange Seats
  3. [中等] 1341. Movie Rating
  4. [中等] 1321. Restaurant Growth
  5. [中等] 602. Friend Requests II: Who Has the Most Friends
  6. [中等] 585. Investments in 2016
  7. [困难] 185. Department Top Three Salaries

高级字符串函数/正则表达式/语句(6E+1M)

  1. [简单] 1667. Fix Names in a Table
  2. [简单] 1527. Patients With a Condition
  3. [简单] 196. Delete Duplicate Emails
  4. [中等] 176. Second Highest Salary
  5. [简单] 1484. Group Sold Products By The Date
  6. [简单] 1327. List the Products Ordered in a Period
  7. [简单] 1517. Find Users With Valid E-Mails