The readability of the code is better with CTE that with nested queries. So I try to avoid them — I used a nested query in the example above because It is more concise.

For readers not familiar with CTE:

A CTE (Common Table Expression) is a temporary result set that you can reference within another query

I write queries using temporary tables:

CREATE TEMPORARY TABLE table_name AS (SELECT foo FROM bar);

The main benefits of temporary tables are:

  • you can investigate the data in the temp table with additional queries to make sure that the result is correct.

Written by

Senior Data Scientist, tweeting twitter.com/romanorac.

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