The first concept that helps when thinking about the format of a subquery is the placement of it: with, nested, inline, or scalar.
The second concept to consider is an indentation, which helps heighten readability for your future self or other users that want to leverage your code.
Badly Formatted Queries
Though these poorly formatted examples will execute the same way as the well-formatted examples, they just aren’t very friendly for understanding what is happening!
Here is the first, where it is impossible to decipher what is going on:
SELECT * FROM (SELECT DATE_TRUNC('day',occurred_at) AS day, channel, COUNT(*) as events FROM web_events GROUP BY 1,2 ORDER BY 3 DESC) sub;
This second version, which includes some helpful line breaks, is easier to read than the previous version, but it is still not as easy to read as the queries in the Well Formatted Query section.
SELECT *
FROM (
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC) sub;
Well Formatted Query
Now for a well-formatted example, you can see the table we are pulling from much easier than in the previous queries.
SELECT *
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC) sub;
Additionally, if we have a GROUP BY, ORDER BY, WHERE, HAVING, or any other statement following our subquery, we would then indent it at the same level as our outer query.
The query below is similar to the above, but it is applying additional statements to the outer query, so you can see there are GROUP BY and ORDER BY statements used on the output and are not tabbed. The inner query GROUP BY and ORDER BY statements are indented to match the inner table.
SELECT *
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC) sub
GROUP BY day, channel, events
ORDER BY 2 DESC;
These final two queries are so much easier to read!
