LeetCode 1098: Unpopular Books
A clear explanation of finding books with fewer than 10 sales in the last year that were not sold in the last year using LEFT JOIN and GROUP BY.
Tag
Database
48 notes
LeetCode 1097: Game Play Analysis V
A clear explanation of finding the fraction of players retained the day after their first login using self-join and window functions.
LeetCode 1084: Sales Analysis III
A clear explanation of finding products sold only in the first quarter of 2019 using GROUP BY with date range conditions.
LeetCode 1083: Sales Analysis II
A clear explanation of finding buyers who bought an iPhone but not an iPad using JOIN and NOT IN filtering.
LeetCode 1082: Sales Analysis I
A clear explanation of finding the best seller(s) by total price using GROUP BY, SUM, and a subquery for the maximum.
LeetCode 1077: Project Employees III
A clear explanation of finding the most experienced employee(s) for each project using a window function or correlated subquery.
LeetCode 1076: Project Employees II
A clear explanation of finding the project with the most employees using GROUP BY, COUNT, and a subquery for the maximum.
LeetCode 1075: Project Employees I
A clear explanation of computing the average years of experience per project using JOIN and AVG aggregation.
LeetCode 1070: Product Sales Analysis III
A clear explanation of finding the first year each product was sold using a self-join or window function.
LeetCode 1069: Product Sales Analysis II
A clear explanation of computing total quantity sold per product using GROUP BY and SUM aggregation.
LeetCode 1068: Product Sales Analysis I
A clear explanation of retrieving product names and their sale years using a JOIN between Sales and Product tables.
LeetCode 1050: Actors and Directors Who Cooperated At Least Three Times
A clear explanation of finding actor-director pairs with at least three collaborations using GROUP BY and HAVING.
LeetCode 1045: Customers Who Bought All Products
A clear explanation of finding customers who purchased every product in the catalog using GROUP BY and HAVING with COUNT DISTINCT.
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 608: Tree Node
A SQL guide for classifying binary tree nodes as Root, Inner, or Leaf based on parent-child relationships.
LeetCode 197: Rising Temperature
A clear explanation of the Rising Temperature SQL problem using a self join and date comparison.
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 610: Triangle Judgement
A SQL guide for checking whether three side lengths can form a valid triangle using the triangle inequality.
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.