21.10 Subqueries

Sometimes it’s not possible to translate a dplyr pipeline into a single SELECT statement and you need to use a subquery. A subquery is just a query used as a data source in the FROM clause, instead of the usual table.

flights |> 
  mutate(
    year1 = year + 1,
    year2 = year1 + 1
  ) |> 
  show_query()
## <SQL>
## SELECT q01.*, year1 + 1.0 AS year2
## FROM (
##   SELECT flights.*, "year" + 1.0 AS year1
##   FROM flights
## ) q01