SQL - How do I return the most recent occurence in a column along with other aggregated columns?-CodePudding

(BigQuery SQL) I have three tables of Formula 1 data - results, races, drivers. I am trying to compile a summary that includes the driver, a count of their top 5 finishes, a count of their podium finishes, their total wins, most recent race, and most recent win. I am struggling with the most recent win.

The three tables have the following columns:

results:

races:

drivers:

I can get every piece of information I need except for the most recent win with the following query:

SELECT
  drivers.driverId,
  driverRef,
  SUM(IF(results.position <=5,1,0)) AS top_five_finishes,
  SUM(IF(results.position <=3,1,0)) AS podium_finishes,
  SUM(IF(results.position = 1,1,0)) AS number_of_wins,
  MAX(races.date) AS most_recent_race,
FROM `formula1.all_results` AS results
  FULL JOIN `formula1.all_drivers` AS drivers ON results.driverId = drivers.driverId
  FULL JOIN `formula1.all_races` AS races ON results.raceId = races.raceId
GROUP BY driverRef, driverId
ORDER BY number_of_wins DESC

Which returns:

I am struggling to include the most recent win. I have tried adding the following statement to the above query:

(select max(races.date)
  from
    `formula1-356612.formula1_project.all_results` AS results
    full join `formula1-356612.formula1_project.all_drivers` AS drivers ON results.driverId = drivers.driverId
    full join `formula1-356612.formula1_project.all_races` AS races ON results.raceId = races.raceId
  where results.position = 1
  ) as most_recent_win

But that simply returns the most recent race in the entire table. I have tried creating a subquery to find the most recent win but it returns every win, not the most recent:

select
  results.driverId as driver_id,
  races.date as date_of_win,
  races.name as track_name,
  results.position as place
from `formula1.all_results` as results
  join `formula1.all_races` as races on results.raceId = races.raceId
where results.position = 1
group by driver_id, date_of_win, track_name, place
order by date_of_win desc

And additionally I am unsure how I would join that information to the existing query.

CodePudding user response：

In order to get one most recent win:

SELECT 
.....
</ Your query with necessary fields and conditions>
....
ORDER BY date_of_win DESC  
LIMIT 1

CodePudding user response：

I've discovered the answer. It's a simple statement:

max(if(results.position = 1, races.date, null)) as most_recent_win