Question

I have a table of bike riders in the following format:

Bike_number, Start_date(date and time), End_date(date, time)

I also have a table of temperatures per hour

Using sql: how do I calculate Count/min/max/average of riders as compared to the temperature at the hour

Answer 1

Assuming that for temperatures table, the hour is denoted by its start_time such that the table looks something like this -

The SQL query is -

select TO_CHAR(temperatures.Start_date, 'YYYY-MM-DD HH24:MI:SS') as start_time, temp, count(Bike_number)
from bike_riders, temperatures
where bike_riders.Start_date < (temperatures.Start_date + 1/24)
AND bike_riders.End_date > temperatures.Start_date
group by temperatures.Start_date, temp
order by temperatures.Start_date;

Here, all the bikers who started before the end of the hour and ended after the start of the hour are counted.

TO_CHAR() is used to format date.

Sample output -