LeetCode SQL - GROUP BY

Table of contents

Duplicates

182. Duplicate Emails

SELECT email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1

1050. Actors and Directors Who Cooperated At Least Three Times

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

GROUP BY

619. Biggest Single Number

WITH cte AS (
	SELECT num
	FROM MyNumbers
	GROUP BY num
	HAVING COUNT(num) = 1
    )
SELECT MAX(num) AS num
FROM cte

1075. Project Employees I

SELECT
    p.project_id,
    COALESCE(ROUND(AVG(e.experience_years), 2), 0) AS average_years
FROM
    Project p
    LEFT JOIN Employee e USING (employee_id)
GROUP BY p.project_id

1045. Customers Who Bought All Products

COUNT (NO FULL OUTER JOIN or CROSS JOIN need!)

See LeetCode SQL Missing Values - 1965. Employees With Missing Information

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

When you WOULD use CROSS JOIN:

• If you needed to show which products each customer is missing
• If you needed a report showing all customer-product combinations with purchase status
• If the problem asked for something like “products that no customer bought”

570. Managers with at Least 5 Direct Reports

SELECT
    m.name
FROM
    employee m
    JOIN employee e ON m.id = e.managerId
GROUP BY
    m.id,
    m.name
HAVING
    COUNT(*) >= 5;

1211. Queries Quality and Percentage

SELECT
    query_name,
    ROUND(AVG(rating::NUMERIC / POSITION), 2) AS quality,
    ROUND(AVG((rating < 3)::INT) * 100, 2) AS poor_query_percentage
FROM
    queries
GROUP BY
    query_name

262. Trips and Users

JOIN

SELECT
    request_at AS "Day",
    ROUND(
        COUNT(*) FILTER (WHERE t.status ILIKE 'cancelled%')::NUMERIC / COUNT(*),
        2
    ) AS "Cancellation Rate"
FROM
    Trips t
    JOIN Users c ON t.client_id = c.users_id 
        AND c.role = 'client' 
        AND c.banned = 'No'
    JOIN Users d ON t.driver_id = d.users_id 
        AND d.role = 'driver' 
        AND d.banned = 'No'
WHERE t.request_at::DATE BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at
ORDER BY t.request_at

EXISTS

SELECT
    request_at AS "Day",
    ROUND(
        COUNT(*) FILTER (WHERE status ILIKE 'cancelled%')::DECIMAL / COUNT(*),
        2
    ) AS "Cancellation Rate"
FROM
    trips t
WHERE request_at::DATE BETWEEN '2013-10-01' AND '2013-10-03'
    AND EXISTS (SELECT 1 FROM users WHERE users_id = t.client_id AND role = 'client' AND banned = 'No')
    AND EXISTS (SELECT 1 FROM users WHERE users_id = t.driver_id AND role = 'driver' AND banned = 'No')
GROUP BY request_at
ORDER BY request_at;

STRING_AGG

1484. Group Sold Products By The Date

SELECT
    sell_date,
    COUNT(DISTINCT product) AS num_sold,
    STRING_AGG(
        DISTINCT product, ',' 
        ORDER BY product
    ) AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date