SQL Advanced Cheatsheet — Subqueries

Introduction

Aggregate Multiple Stages

SELECT sub.month, sub.day_of_week, AVG(sub.incident_count) AS avg_incidents
FROM
(SELECT day_of_week, date, COUNT(incident_ID) AS incident_count
FROM
GROUP BY 1, 2
) sub
GROUP BY 1, 2
ORDER BY 1, 2
SELECT COUNT(CASE WHEN is_shippable = 'YES' THEN 1 ELSE NULL END) *
1.00 / COUNT(*) * 100 AS percent_shippable
FROM
(SELECT o.ID, CASE WHEN c.address IS NULL THEN 'NO' ELSE 'YES'
END AS is_shippable
FROM orders o
LEFT JOIN customers c
ON o.customer_ID = c.ID) sub

Subqueries in Conditional Logic

SELECT *
FROM crime_incidents_table
WHERE date IN (SELECT date
FROM crime_incidents_table
ORDER BY date
LIMIT 5)

Joins Using Subqueries

SELECT *
FROM crime_incidents_table incidents
JOIN (SELECT date
FROM crime_incidents_table
ORDER BY date
LIMIT 5) sub
ON incidents.date = sub.date
SELECT main.*, sub.count_by_category AS total_incidents_by_category
FROM crime_incidents_table main
JOIN (SELECT category, COUNT(*) AS count_by_category
FROM crime_incidents_table
GROUP BY 1
ORDER BY 2
LIMIT 3) sub
ON sub.category = main.category

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store