LC 1251 & LC 1445 & LC 1571 & LC 1699

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

The following basic-level SQL problems are about ‘Function’. e.g. SUM() with IF conditions.

LeetCode 1251

Average Selling Price (Easy) [link]

Using JOIN

SELECT P.product_id, ROUND(SUM(P.price * U.units) / SUM(U.units), 2) AS average_price
FROM Prices P
LEFT JOIN UnitsSold U
ON P.product_id = U.product_id AND U.purchase_date BETWEEN P.start_date AND P.end_date
GROUP BY P.product_id;

Using WHERE

SELECT P.product_id, ROUND(SUM(P.price * U.units) / SUM(U.units), 2) AS average_price
FROM Prices P,UnitsSold U
WHERE P.product_id = U.product_id AND U.purchase_date BETWEEN P.start_date AND P.end_date
GROUP BY P.product_id;

LeetCode 1445

Apples & Oranges (Medium) [link]

Using IF

SELECT t.sale_date, SUM(t.sold_num) AS diff
FROM (
    SELECT sale_date, fruit, (
        IF(fruit = 'apples', sold_num, -sold_num)
    ) AS sold_num
    FROM Sales
) AS t
GROUP BY sale_date
ORDER BY sale_date;

A simplified version

SELECT sale_date, SUM(IF(fruit = 'apples', sold_num, -sold_num)) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date;

Using CASE WHEN

SELECT sale_date, 
    SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE -sold_num END) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date;

LeetCode 1571

Warehouse Manager (Easy) [link]

SELECT W.name AS warehouse_name, SUM(W.units * P.Width * P.Length * P.Height) AS volume
FROM Warehouse W
LEFT JOIN Products P
ON W.product_id = P.product_id
GROUP BY W.name;

LeetCode 1699

Number of Calls Between Two Persons (Medium) [link]

SELECT person1, person2, COUNT(*) AS call_count, SUM(duration) AS total_duration
FROM (
    SELECT 
        IF(from_id <= to_id, from_id, to_id) AS person1,
        IF(from_id > to_id, from_id, to_id) AS person2,
        duration
    FROM Calls
) AS t
GROUP BY person1, person2;
SQL-Basic
  TOC