CODEWITHSUNDEEP
sqlpostgresql
undecided000
undecided000

Reputation: 105

SQL - Get average time from 2 different dates by only using common values

I have a database with runners, time and date. They run different tracks but the same distance usually. I want to find out how much longer a certain track takes on average by only comparing it with runners who have ran both, rather than just getting the average. So for example compare the average time on 02/05/2020 vs average time on 09/05/2020 by only using people who ran on both dates.

Is this possible?

Sample table

RUNNER TIME  DISTANCE  DATE 
A      23.40 7         02/05/2020
B      24.28 7         02/05/2020
C      28.90 7         02/05/2020
A      23.60 7         09/05/2020
D      22.80 7         09/05/2020
B      14.30 4         09/05/2020

The statement will need a "distance" field as some runners can run different distances. So as A is the only runner on both dates and same distance, the answer would be 0.2 or -0.2, depending which way you do it, it doesn't matter.

Upvotes: 1

Views: 41

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

If I understand this correctly, given is one distance and two dates. You want to look at the times of runners who ran the distance on both dates and get the average running time difference for the two dates.

You can achieve this by joining day 1 and day 2 for the distance by runner.

with day1 as (select runner, running_time from runs
              where distance = 1 and running_date = date '2020-02-05')
   , day2 as (select runner, running_time from runs
              where distance = 1 and running_date = date '2020-02-09')
select avg(day1.running_time - day2.running_time) average_diff
from day1 join day2 using (runner);

Demo: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1154af67b35ade42bea8b1e1cd241321

table runs

+----------+--------+--------------+--------------+
| distance | runner | running_date | running_time |
+----------+--------+--------------+--------------+
|        7 |      1 | 2020-02-05   | 00:20:00     |
|        7 |      1 | 2020-02-09   | 00:18:00     | <- runner 1 was 2 mins faster on day 2
|        7 |      2 | 2020-02-05   | 00:25:00     |
|        7 |      2 | 2020-02-09   | 00:19:00     | <- runner 2 was 6 mins faster on day 2 
|        7 |      3 | 2020-02-05   | 00:50:00     | <- runner 3 only ran on day 1
|        7 |      4 | 2020-02-09   | 00:10:00     | 
|        9 |      4 | 2020-02-09   | 00:15:00     | <- runner 4 ran another distance on day 2
+----------+--------+--------------+--------------+

result

+--------------+
| average_diff |
+--------------+
| 00:04:00     | <- runner 1 = 2 mins, runner 2 = 6 mins => average 4 mins
+--------------+

Upvotes: 2

Related Questions