I really hope that if B-tree feels that it has not performed well in LeetCode's SQL practice so far, it is necessary to take a look at the handbook of Advanced SQL statements. At present, I have stopped to watch it twice. Because I was blinded by the SQL statement logic show taught by the data warehouse last week, I think it is possible to be proficient and make progress only by knowing these things from the bottom. I haven't compared which is easy to use at the moment, but I posted a fairly complete one first:
🔗SQL Quick Guide
534. Game Play Analysis III <OVER.>
Solution:
The main core is the use of OVER, which supports aggregate values based on GROUP.
SELECT player_id, event_date, sum(games_played) OVER (partition by player_id order by event_date) AS games_played_so_far FROM Activity ORDER BY player_id, event_date ASC
550. Game Play Analysis IV <JOIN.>
I can't help but sigh that the most important thing to brush questions is the idea. There are just a few options - JOIN table, dependency partition...
When you look at this question, you should think that start date can be associated with event_ Dates are placed side by side. After the rule is set, the LEFT JOIN that does not meet the requirements is directly filtered out.
SOLUTION:
SELECT round(count(a2.player_id) / count(a1.player_id), 2) As fraction FROM( SELECT player_id, min(event_date) AS start_date FROM Activity GROUP BY player_id) AS a1 LEFT JOIN( SELECT player_id, event_date FROM Activity ) AS a2 ON a1.player_id = a2.player_id AND event_date = start_date + 1
570. Managers with at Least 5 Direct Reports <JOIN.>
SOLUTION:
My logic first thought of the writing of JOIN. At the beginning, I used LEFT JOIN. The result of a test sample should be [[]], and my result is [[null]]. Later, I suddenly thought that the property of LEFT JOIN is to forcibly connect and fill in null even if it is a null match. You can just use the JOIN. Change it to the following. Pass.
SELECT name FROM( SELECT managerId FROM Employee GROUP BY managerId HAVING count(*) >= 5 ) AS e1 JOIN Employee e ON e1.managerId = e.id
Considering that it is absolutely feasible to limit the ID in the same table, it is slightly modified (but the runtime is increased), and the non JOIN is written as follows:
SELECT name FROM Employee WHERE id IN( SELECT managerId FROM Employee GROUP BY managerId HAVING count(*) >= 5 )
580. Count Student Number in Departments <JOIN.>
SOLUTION:
It's not complicated at all. It's the problem of JOIN, because two tables are linked to get all the information. However, I encountered a very brief error (DISH) at the beginning, that is, based on the Architecture display of 0, the Art of 0 does not appear in the table. Because I used to write s.dept in GROUP BY_ ID. if you GROUP BY Student, it's not surprising that there will be a lack of departments.
SELECT d.dept_name, count(s.dept_id) AS student_number FROM Student s RIGHT JOIN Department d ON s.dept_id = d.dept_id GROUP BY d.dept_name # It can also be written as GROUP BY 1 ORDER BY student_number DESC # It can also be written as ORDER BY 2 DESC
608. Tree Node <CASE, IS NULL>
I like this question! On the one hand, CASE is officially used, which makes me feel a little in coding. Second, I have never noticed that the difference between IS NULLh and = 'null' is directly demonstrated here.
When I write IS NULL as' null 'in the second line, the error is as follows:
It can be seen that this = null can't match things here. reference resources: Database query - the difference between is null and = null
He talked a lot about the key points of the above author's sentence:
Null means nothing. It cannot be =, >, <... All judgments are false. All judgments can only be made with is null.
SOLUTION:
See notes for ideas
SELECT id, CASE WHEN p_id IS NULL THEN 'Root' WHEN id IN (SELECT p_id FROM Tree) THEN 'Inner' ELSE 'Leaf' END AS type FROM Tree ORDER BY id ASC
1045. Customers Who Bought All Products <DISTINCT.>
SOLUTION:
For me, the last line of this question depends on nonsense, especially if the foundation is not solid enough. But as long as you get the meaning right, you will be deeply impressed when you can get the correct answer (I didn't know that you could add a bracket to reselect in HAVING).
However, the focus of this problem is obviously not here, mainly in the right ideas and entry points. The entry point of this question is that the quantity in the Product table is equal to the non duplicate quantity of the corresponding goods in the Customer table after GROUP BY.
SELECT customer_id FROM Customer GROUP BY customer_id HAVING count(DISTINCT product_key) = (SELECT count(product_key) FROM Product)
1077. Project Employees III
SOLUTION:
Idea: you don't want to JOIN first. Because considering the sorting problem, we naturally think of several sorting functions. Consider row_number() and rank(). Remember, the former is not repeated, and the latter is repeated. If you can't remember, look again: 🔗 Any article on the differences and examples of several functions
SELECT project_id, wholeTable.employee_id FROM( SELECT project_id, p.employee_id, rank() OVER (partition by project_id ORDER BY experience_years DESC) AS rn FROM Project p JOIN Employee e ON p.employee_id = e.employee_id ) AS wholeTable WHERE wholeTable.rn = 1
1098. Unpopular Books
SOLUTION:
I'm very sorry. It's a very garbage algorithm. At least the time complexity is amazing. But I think the idea is clear.
# Find out those who have sold more than 10 copies - > eliminate them # Then find the products that are on sale within one month # Subtract these two screening results from Books SELECT c.book_id, c.name FROM( # complete works SELECT * FROM Books ) c LEFT JOIN( # More than 10 copies have been sold within the specified time SELECT b.book_id, b.name FROM Books b JOIN Orders o ON b.book_id = o.book_id WHERE dispatch_date > '2018-06-23' GROUP BY 1 HAVING sum(quantity)>=10 ) ten ON c.book_id = ten.book_id LEFT JOIN( # Selling late doesn't count SELECT book_id, name FROM Books WHERE available_from >= '2019-05-23' ) late ON late.book_id = c.book_id WHERE late.book_id IS null and ten.book_id IS null
In fact, there is no need to JOIN the last night, because the source and basic structure of table c are the same. Can be rewritten as:
SELECT c.book_id, c.name FROM( # complete works SELECT * FROM Books ) c LEFT JOIN( # More than 10 copies have been sold within the specified time SELECT b.book_id, b.name FROM Books b JOIN Orders o ON b.book_id = o.book_id WHERE dispatch_date > '2018-06-23' GROUP BY 1 HAVING sum(quantity)>=10 ) ten ON c.book_id = ten.book_id WHERE ten.book_id IS null and c.available_from < '2019-05-23'
Well, increased by 4% (dog head).
1112. Highest Grade For Each Student
SOLUTION:
SELECT student_id, course_id, grade FROM ( SELECT *, row_number() OVER(partition by student_id ORDER BY grade DESC, course_id) AS rn FROM Enrollments) t WHERE t.rn = 1 ORDER BY student_id
1126. Active businesses < you can see the logic >
SOLUTION:
Two main requirements:
1 - event_ The occurrence corresponding to type is higher than the average of this business
2 - there must be more than one business that meets condition 1
WITH eventView AS( SELECT *, avg(occurences) AS average FROM Events GROUP BY event_type ) SELECT Events.business_id FROM Events JOIN eventView ON Events.event_type = eventView.event_type WHERE eventView.average < Events.occurences # 1 GROUP BY Events.business_id # Restriction 1 HAVING count(*) > 1 # 2
1164. Product Price at a Given Date
SOLUTION:
Happy, happy! Learned something new: 🔗 COALESCE() function
WITH ranked AS( SELECT product_id, new_price AS price, change_date, row_number() OVER (partition by product_id ORDER BY change_date DESC) AS rn FROM Products WHERE change_date <= '2019-08-16' ) , template AS( SELECT DISTINCT product_id FROM Products ) SELECT template.product_id,COALESCE(e1.price, 10) AS price FROM (SELECT product_id, price FROM ranked WHERE rn = 1) AS e1 RIGHT JOIN template ON template.product_id = e1.product_id
The idea is that one table adjusts the updates before 8-16 and adds a sorted column, and the other table is all products_ ID is used for the following JOIN to supplement the default value of 10. The merging of two tables is the problem of JOIN. The role of COALESCE is a simplified version similar to CASE WHEN.