LC 1045 & LC 1321 & LC 1549

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

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

LeetCode 1045

Customers Who Bought All Products (Medium) [link]

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (
    SELECT COUNT(DISTINCT product_key) cnt
    FROM Product
);

LeetCode 1321

Restaurant Growth (Medium) [link]

SELECT visited_on, amount, average_amount
FROM (
    SELECT visited_on, 
        SUM(amo) OVER(ORDER BY visited_on ROWS 6 PRECEDING) AS amount,
        ROUND(AVG(amo) OVER(ORDER BY visited_on ROWS 6 PRECEDING),2) AS average_amount
    FROM (
        SELECT *, SUM(amount) AS amo
        FROM Customer
        GROUP BY visited_on
    ) t
) t2
WHERE DATEDIFF(
    visited_on, (
    SELECT MIN(visited_on) FROM Customer
) ) >= 6;

LeetCode 1549

The most recent orders for each product (Medium) [link]

SELECT P.product_name, P.product_id, O.order_id, O.order_date
FROM Orders O
LEFT JOIN Products P
ON O.product_id = P.product_id
WHERE (P.product_id, O.order_date) IN ( # use product_id rather than order_id
    SELECT product_id, MAX(order_date) AS recent
    FROM Orders
    GROUP BY product_id
)
ORDER BY product_name, product_id, order_id;
SQL-Basic
  TOC