LC 586 & LC 1076 & LC 1082 & LC 1204

Solution to LeetCode about ‘window function’ integrated with summary.

LeetCode 586

Customer Placing the Largest Number of Orders (Easy) [link]

Remember the usage of GROUP BY and ORDER BY COUNT(customer_number) DESC.

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(customer_number) DESC
LIMIT 1;

The limitation here is that no matter how many customers have the largest number of orders, we only report one of them. The follow-up questions would be ‘If there is a tie, report them all’.

SELECT customer_number
FROM Orders
GROUP BY customer_number
HAVING COUNT(customer_number) = (
    SELECT COUNT(customer_number) as cnt
    FROM Orders
    GROUP BY customer_number
    ORDER BY cnt DESC
    LIMIT 1
);

Use window function integrated with GROUP BY and COUNT

SELECT customer_number
FROM(
    SELECT *,
        dense_rank() OVER(ORDER BY COUNT(order_number) DESC) as rnk
    FROM Orders
    GROUP BY customer_number
) t
WHERE rnk = 1;

LeetCode 1076

Project Employees II (Easy) [link]

Use window function integrated with GROUP BY and COUNT.

SELECT project_id
FROM(
    SELECT *,
        dense_rank() OVER(ORDER BY COUNT(employee_id) DESC) AS rnk
    FROM Project
    GROUP BY project_id
) t
WHERE rnk = 1;

LeetCode 1082

Sales Analysis I (Easy) [link]

Initial thought:

SELECT DISTINCT seller_id
FROM(
    SELECT seller_id,
        SUM(price) OVER(PARTITION BY seller_id) AS total_price
    FROM Sales   
) tt
WHERE total_price = (
    SELECT MAX(total_price)
    FROM(
        SELECT seller_id,
            SUM(price) OVER(PARTITION BY seller_id) AS total_price
        FROM Sales
    ) t
);

Simplified versions: use window function integrated with GROUP BY and SUM .

SELECT seller_id
FROM (
    SELECT seller_id , dense_rank() OVER(ORDER BY SUM(price) DESC) AS rnk
    FROM Sales 
    GROUP BY seller_id
) t
WHERE t.rnk=1;

LeetCode 1204

Last Person to Fit in the Elevator (Medium) [link]

Need to specify ORDER BY turn for cumulating sum.

SELECT person_name
FROM(
    SELECT *,
        SUM(weight) OVER (ORDER BY turn) AS acc_weight
    FROM Queue    
) t
WHERE acc_weight <= 1000
ORDER BY acc_weight DESC
LIMIT 1;