Some SQL tips I've learned over the last few years

…even though I spent part of my career writing SQL queries in corporations.

Temporary tables with with

with can be used to create temporary tables that can be used in the main query.

with cities as (
    select name, state, population
    from city
)

select *
from cities
where population > 1000000;

filter in aggregated columns

having is not the only way to filter aggregated columns. filter is much simpler and doesn’t require creating subqueries for multiple aggregated columns.

select state
      ,count(*) as qty_cities
      ,count(*) filter (where population > 1000000) as qty_big_cities
from cities