LC 1158 & LC 1393 & LC 1407

SQL-Intro
SQL
Publish Date:   2022-03-18

The following intro-level SQL problems are about ‘Control Flow’.

LeetCode 1158

Market Analysis I (Medium) [link]

SELECT U.user_id AS buyer_id, U.join_date, IFNULL(t.orders_in_2019, 0) AS orders_in_2019
FROM Users U
LEFT JOIN (
    SELECT buyer_id, order_date, COUNT(order_id) AS orders_in_2019
    FROM Orders
    WHERE YEAR(order_date) = '2019'
    GROUP BY buyer_id
) AS t
ON U.user_id = t.buyer_id;

LeetCode 1393

Capital Gain/Loss (Medium) [link]

SELECT stock_name, 
    SUM(
        CASE WHEN operation = 'Buy' THEN -price ELSE price END
    ) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;

SELECT stock_name, SUM(IF(operation = 'Buy', -price, price)) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name

LeetCode 1407

Top Travelers (Easy) [link]

Note here we need to group by user id because it is possible that two people’s names are identical.

SELECT U.name, IFNULL(SUM(R.distance), 0) AS travelled_distance
FROM Users U
LEFT JOIN Rides R
ON U.id = R.user_id
GROUP BY U.id
ORDER BY travelled_distance DESC, name ASC;
SQL-Intro
  TOC