LC 1341 & LC 1867

SQL-Basic
SQL
Publish Date:   2022-04-03

The following basic-level SQL problems are about ‘subquery’.

LeetCode 1341

Movie Rating (Medium) [link]

Note: UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records.

(SELECT name AS results
FROM(
    SELECT u.name, COUNT(m1.movie_id) AS cnt
    FROM MovieRating m1
    JOIN Users u
    ON m1.user_id = u.user_id
    GROUP BY m1.user_id
) t1
ORDER BY cnt DESC, name
LIMIT 1)
UNION
(SELECT title AS results
FROM(
    SELECT m2.title, AVG(m1.rating) AS avg_rating
    FROM MovieRating AS m1
    JOIN Movies AS m2
    ON m1.movie_id = m2.movie_id
    WHERE DATE_FORMAT(m1.created_at, '%Y-%m') = '2020-02'
    GROUP BY m1.movie_id
) t2
ORDER BY avg_rating DESC, title
LIMIT 1);

LeetCode 1867

Orders With Maximum Quantity Above Average (Medium) [link]

Using window function.

SELECT order_id
FROM(
    SELECT DISTINCT order_id,
        MAX(quantity) OVER(PARTITION BY order_id) AS max_quantity,
        AVG(quantity) OVER(PARTITION BY order_id) AS avg_quantity
    FROM OrdersDetails
) t1
WHERE max_quantity > (
    SELECT MAX(avg_quantity) as max_quantity
    FROM (
        SELECT DISTINCT order_id,
            MAX(quantity) OVER(PARTITION BY order_id) AS max_quantity,
            AVG(quantity) OVER(PARTITION BY order_id) AS avg_quantity
        FROM OrdersDetails
    ) t2
);

A simplified version of using window function.

SELECT order_id
FROM(
    SELECT DISTINCT order_id,
        MAX(quantity) > MAX(AVG(quantity)) OVER() AS flag
    FROM OrdersDetails
    GROUP BY order_id
) t
WHERE flag = 1;
SQL-Basic
  TOC