[Data Correction]: When querying database to check for number of championships won by Hamilton, it returns 8 records with 2014 replaced by 2007 and 2010 added.

[samin-batra]

Endpoint

circuits, driverStandings

URL

n/a

What is the current Value?

It currently returns 8 rows as such: year, driver_id, pts, rank
This is a result of 2 queries that were prepared to rank drivers in each season by points scored and then getting the top ranked driver where driver_id is Hamilton's driver id. Also, the points scored metric is returning significantly larger values than what was originally scored.

What should the value be?

7 rows should be returned, for the following years: 2008,2014,2015,2017,2018,2019,2020

Description

Run this Postgres query:

1. We first group by season id, driver id to get points scored
2. We then rank for each season, the drivers according to points scored in descending order. Finally, we get the rows for Lewis Hamilton for seasons where he scored number 1 rank.

create temp table temp1 as
select year,driver_id,sum(points) as pts from formula_one_driverchampionship where
session_id in
(select id from formula_one_session where type='R')
group by year,driver_id


select * from (
select year,driver_id,pts,rank() over (partition by year order by pts desc) as rnk from temp1
) where driver_id=795 and rnk=1

[jolpica]

Hey,

I'm not able to help debug your SQL query but I think you have not limited the driverstandigs table to just the final race of each season. You can do this by checking the season field is not null.

To help pin down this issue, could you please provide the years that are given that are incorrect and the points values?
Ideally with an API endpoint returning these values.

Thanks

[jolpica]

Here is a corrected query, the points have already been aggregated.

jolpica=# select year,driver_id, points from formula_one_driverchampionship where season_id is not null and driver_id=795 and position=1;
 year | driver_id | points 
------+-----------+--------
 2008 |       795 |     98
 2014 |       795 |    384
 2015 |       795 |    381
 2017 |       795 |    363
 2018 |       795 |    408
 2019 |       795 |    413
 2020 |       795 |    347
(7 rows)

In future I will likely not have the time to help with SQL like this, so please only report bugs in data that you can find on our exposed endpoints on api.jolpi.ca. Thank you.

[samin-batra]

Hi, thank you for pointing out the issue. Appreciate it (Y)