The Art of PostgreSQL/

...

/

Common Table Expressions

Common Table Expressions

Learn about the common table expression in SQL and practice daisy chaining.

We'll cover the following...

Many drivers did not finish due to accidents, which was the second reason listed after did not qualify. If we want to check how often accidents happen in Formula 1, we can look at the seasons in which most accidents occurred.

Press + to interact
  select extract(year from races.date) as season,
         count(*)
           filter(where status = 'Accident') as accidents
    
    from results
         join status using(statusid)
         join races using(raceid)
group by season
order by accidents desc
   limit 5;

So the five seasons with the most accidents in the history of Formula 1 are as follows :

 season │ accidents 
════════╪═══════════
   1977 │        60
   1975 │        54
   1978 │        48
   1976 │        48
   1985 │        36
(5 rows)

It seems the most dangerous seasons of all time are clustered at the end of the 1970s and the beginning of the 1980s.

Common table expression: with

Let’s zoom in on this period with the following console-friendly histogram query.

Common table expression is the full name of the with clause that we see in effect in the query. It allows us to run a subquery as a prologue and then refer to its result set like any other relation in the from clause of the main query. In our case, we can see that the main query is from accidents, and the CTE has been given that name.

In the accidents CTE, we compute basic ...