LeetCode 197: Rising Temperature
A clear explanation of the Rising Temperature SQL problem using a self join and date comparison.
Tag
Database
35 notes
LeetCode 175: Combine Two Tables
A clear SQL guide for solving Combine Two Tables using LEFT JOIN.
LeetCode 615: Average Salary: Departments VS Company
A SQL guide for comparing each department's monthly average salary against the company's monthly average salary.
LeetCode 614: Second Degree Follower
A SQL guide for finding users who both follow someone and have followers, then counting how many followers they have.
LeetCode 613: Shortest Distance in a Line
A SQL guide for finding the minimum distance between any two unique points on the X-axis.
LeetCode 612: Shortest Distance in a Plane
A SQL guide for finding the minimum Euclidean distance between any two points in a 2D plane.
LeetCode 610: Triangle Judgement
A SQL guide for checking whether three side lengths can form a valid triangle using the triangle inequality.
LeetCode 608: Tree Node
A SQL guide for classifying binary tree nodes as Root, Inner, or Leaf based on parent-child relationships.
LeetCode 607: Sales Person
A SQL guide for finding salespeople who never had an order related to the company named RED.
LeetCode 603: Consecutive Available Seats
A SQL guide for finding all cinema seats that are free and adjacent to at least one other free seat.
LeetCode 602: Friend Requests II: Who Has the Most Friends
A SQL guide for counting friendships from both requester and accepter sides, then returning the user with the most friends.
LeetCode 601: Human Traffic of Stadium
A SQL guide for finding stadium records that belong to runs of at least three consecutive ids where each row has at least 100 people.
LeetCode 512: Game Play Analysis II
A clear explanation of finding the first device used by each player using SQL aggregation and a join.
LeetCode 511: Game Play Analysis I
A clear explanation of finding each player's first login date using SQL aggregation.
LeetCode 574: Winning Candidate
A clear explanation of Winning Candidate using SQL aggregation to count votes and return the candidate with the most votes.
LeetCode 571: Find Median Given Frequency of Numbers
A clear explanation of Find Median Given Frequency of Numbers using cumulative frequency and SQL window functions.
LeetCode 570: Managers with at Least 5 Direct Reports
A clear explanation of Managers with at Least 5 Direct Reports using grouping and a self join.
LeetCode 569: Median Employee Salary
A clear explanation of Median Employee Salary using SQL window functions to rank employees inside each company.
LeetCode 550: Game Play Analysis IV
A clear explanation of calculating the fraction of players who logged in again the day after their first login.
LeetCode 534: Game Play Analysis III
A clear explanation of computing cumulative games played per player and date using SQL window functions.
LeetCode 262: Trips and Users
A clear explanation of the Trips and Users SQL problem using joins, filtering, grouping, and conditional aggregation.
LeetCode 185: Department Top Three Salaries
A clear SQL solution for finding employees whose salaries are in the top three unique salary levels within their department.
LeetCode 184: Department Highest Salary
A clear SQL solution for finding every employee who earns the highest salary in their department.
LeetCode 183: Customers Who Never Order
A clear SQL solution for finding customers who have no matching rows in the Orders table.
LeetCode 182: Duplicate Emails
A clear SQL solution for reporting email values that appear more than once in the Person table.
LeetCode 181: Employees Earning More Than Their Managers
A clear SQL solution for finding employees whose salary is greater than their manager's salary using a self join.
LeetCode 180: Consecutive Numbers
A clear SQL solution for finding numbers that appear at least three times consecutively in the Logs table.
LeetCode 178: Rank Scores
A clear SQL solution for ranking scores with dense ranking, where ties share the same rank and no rank numbers are skipped.
LeetCode 177: Nth Highest Salary
A clear SQL solution for finding the nth highest distinct salary from the Employee table.
LeetCode 176: Second Highest Salary
A clear SQL solution for finding the second highest distinct salary from the Employee table.
3.6 External-Memory and Database Structures
B-tree indexes, LSM trees, SSTables, write-ahead logs, compaction strategies, cache-oblivious structures, inverted indexes, and external sort/merge.
Spill Sort
Sorting method that keeps data in memory until a memory limit is reached, then spills sorted runs to external storage and merges them.
Sort Merge Join Sort Phase
The sorting stage used before a sort-merge join when one or both inputs are not already ordered by the join key.
Database External Sort
External sorting procedure used by database systems for ORDER BY, GROUP BY, DISTINCT, and sort-merge joins.
Two Phase Multiway Merge Sort
External sorting algorithm that performs run generation and a single multiway merge using limited memory.