Advanced JOIN Techniques

select r.year,
       r.name         as race,
       d.surname      as driver,
       c.name         as constructor,
       res.positionorder as pos,
       res.points
  from f1db.results  res
  join f1db.races    r
    on r.raceid       = res.raceid
  join f1db.drivers  d
    on d.driverid     = res.driverid
  join f1db.constructors c
    on c.constructorid = res.constructorid
 where r.year = 2017
   and res.positionorder <= 3
 order by r.date, res.positionorder
 limit 12;

Top-three finishers per race in the 2017 season with driver and constructor names.

select r.year,
       prev.round  as prev_round,
       regexp_replace(prev.name,
         ' Grand Prix$', '') as prev_gp,
       r.round     as curr_round,
       regexp_replace(r.name,
         ' Grand Prix$', '') as curr_gp,
       r.date - prev.date   as days_gap
  from f1db.races r
  join f1db.races prev
    on prev.year  = r.year
   and prev.round = r.round - 1
 where r.year = 2017
 order by r.round;

Days between consecutive race weekends in the 2017 Formula 1 calendar.

-- Amplified (wrong): counts result rows,
-- not distinct constructors, because the
-- 1:N join multiplies each constructor
-- by all its result rows.
select c.name          as constructor,
       count(*)        as result_rows
  from f1db.constructors c
  join f1db.results res
    on res.constructorid = c.constructorid
 group by c.constructorid, c.name
 order by result_rows desc
 limit 5;

Race result rows per constructor ranked by total entries across all seasons.

select d.surname       as driver,
       top3.race,
       top3.pos,
       top3.points
  from f1db.drivers d
  join lateral (
    select r.name  as race,
           res.positionorder as pos,
           res.points
      from f1db.results res
      join f1db.races   r
        on r.raceid = res.raceid
     where res.driverid = d.driverid
       and r.year       = 2017
       and res.points   > 0
     order by res.points desc
     limit 3
  ) top3 on true
 where d.surname in (
   'Hamilton','Vettel','Bottas'
 )
 order by d.surname, top3.points desc;

Top three highest-scoring race results in 2017 for Hamilton, Vettel, and Bottas.

select d.surname              as driver,
       r.round,
       left(r.name, 14)       as race,
       res.points,
       sum(res.points) over (
         partition by d.driverid
         order by r.date
         rows between unbounded preceding
                  and current row
       )                      as cum_pts
  from f1db.results res
  join f1db.drivers d
    on d.driverid = res.driverid
  join f1db.races   r
    on r.raceid   = res.raceid
 where r.year = 2017
   and d.surname in ('Hamilton', 'Vettel')
 order by d.surname, r.date;

Cumulative championship points per race for Hamilton and Vettel across the 2017 season.

select forename, surname,
       constructors.name    as constructor,
       count(*)             as races,
       count(distinct status) as reasons
  from f1db.drivers
       join f1db.results      using(driverid)
       join f1db.races        using(raceid)
       join f1db.status       using(statusid)
       join f1db.constructors using(constructorid)
 where date >= date '1978-01-01'
   and date <  date '1978-01-01'
                + interval '1 year'
   and not exists (
     select 1
       from f1db.results r
      where position is not null
        and r.driverid = drivers.driverid
   )
 group by constructors.name, driverid
 order by count(*) desc limit 10;

Drivers who entered at least one 1978 race without ever finishing.