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.
48 notes
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.
A clear explanation of finding the fraction of players retained the day after their first login using self-join and window functions.
A clear explanation of finding products sold only in the first quarter of 2019 using GROUP BY with date range conditions.
A clear explanation of finding buyers who bought an iPhone but not an iPad using JOIN and NOT IN filtering.
A clear explanation of finding the best seller(s) by total price using GROUP BY, SUM, and a subquery for the maximum.
A clear explanation of finding the most experienced employee(s) for each project using a window function or correlated subquery.
A clear explanation of finding the project with the most employees using GROUP BY, COUNT, and a subquery for the maximum.
A clear explanation of computing the average years of experience per project using JOIN and AVG aggregation.
A clear explanation of finding the first year each product was sold using a self-join or window function.
A clear explanation of computing total quantity sold per product using GROUP BY and SUM aggregation.
A clear explanation of retrieving product names and their sale years using a JOIN between Sales and Product tables.
A clear explanation of finding actor-director pairs with at least three collaborations using GROUP BY and HAVING.
A clear explanation of finding customers who purchased every product in the catalog using GROUP BY and HAVING with COUNT DISTINCT.
A SQL guide for finding the minimum Euclidean distance between any two points in a 2D plane.
A SQL guide for classifying binary tree nodes as Root, Inner, or Leaf based on parent-child relationships.
A clear explanation of the Rising Temperature SQL problem using a self join and date comparison.
A clear SQL guide for solving Combine Two Tables using LEFT JOIN.
A SQL guide for comparing each department's monthly average salary against the company's monthly average salary.
A SQL guide for finding users who both follow someone and have followers, then counting how many followers they have.
A SQL guide for finding the minimum distance between any two unique points on the X-axis.
A SQL guide for checking whether three side lengths can form a valid triangle using the triangle inequality.
A SQL guide for finding salespeople who never had an order related to the company named RED.
A SQL guide for finding all cinema seats that are free and adjacent to at least one other free seat.
A SQL guide for counting friendships from both requester and accepter sides, then returning the user with the most friends.
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.
A clear explanation of finding the first device used by each player using SQL aggregation and a join.
A clear explanation of finding each player's first login date using SQL aggregation.
A clear explanation of Winning Candidate using SQL aggregation to count votes and return the candidate with the most votes.
A clear explanation of Find Median Given Frequency of Numbers using cumulative frequency and SQL window functions.
A clear explanation of Managers with at Least 5 Direct Reports using grouping and a self join.
A clear explanation of Median Employee Salary using SQL window functions to rank employees inside each company.
A clear explanation of calculating the fraction of players who logged in again the day after their first login.
A clear explanation of computing cumulative games played per player and date using SQL window functions.
A clear explanation of the Trips and Users SQL problem using joins, filtering, grouping, and conditional aggregation.
A clear SQL solution for finding employees whose salaries are in the top three unique salary levels within their department.
A clear SQL solution for finding every employee who earns the highest salary in their department.
A clear SQL solution for finding customers who have no matching rows in the Orders table.
A clear SQL solution for reporting email values that appear more than once in the Person table.
A clear SQL solution for finding employees whose salary is greater than their manager's salary using a self join.
A clear SQL solution for finding numbers that appear at least three times consecutively in the Logs table.
A clear SQL solution for ranking scores with dense ranking, where ties share the same rank and no rank numbers are skipped.
A clear SQL solution for finding the nth highest distinct salary from the Employee table.
A clear SQL solution for finding the second highest distinct salary from the Employee table.
B-tree indexes, LSM trees, SSTables, write-ahead logs, compaction strategies, cache-oblivious structures, inverted indexes, and external sort/merge.
Sorting method that keeps data in memory until a memory limit is reached, then spills sorted runs to external storage and merges them.
The sorting stage used before a sort-merge join when one or both inputs are not already ordered by the join key.
External sorting procedure used by database systems for ORDER BY, GROUP BY, DISTINCT, and sort-merge joins.
External sorting algorithm that performs run generation and a single multiway merge using limited memory.