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.