LC 182 & LC 1050 & LC 1084 & LC 1587

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

The following intro-level SQL problems are about ‘Filtering (HAVING)’.

LeetCode 182

Duplicate Emails (Easy) [link]

Using GROUP BY and HAVING (note that we have to use HAVING rather than WHERE after GROUP BY)

SELECT email AS Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

Using GROUP BY, temporary table, and WHERE

SELECT t.email AS Email
FROM (
    SELECT email, COUNT(email) AS cnt
    FROM Person
    GROUP BY email
) AS t
WHERE t.cnt > 1;

LeetCode 1050

Actors and Directors Who Cooperated At Least Three Times (Easy) [link]

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(timestamp) >= 3;

LeetCode 1084

Sales Analysis III (Easy) [link]

SELECT P.product_id, P.product_name
FROM Sales S
LEFT JOIN Product P
ON S.product_id = P.product_id
GROUP BY product_id
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31';

LeetCode 1587

Bank Account Summary (Easy) [link]

SELECT U.name, SUM(T.amount) AS balance
FROM Transactions T
LEFT JOIN Users U
ON T.account = U.account
GROUP BY T.account
HAVING SUM(amount) > 10000;
SQL-Intro
  TOC