I'm new to SQL, so after trying a lot, I couldn't find appropriate solution on Stackoverflow. That's why I posted my question.[May be it's a repeating question] I have 2 tables Driver and Ride. Structure given below:
Person: id Name Email reg_number 37 test1 email@example.com 111111 38 test2 firstname.lastname@example.org 222222 39 test3 email@example.com 333333 40 test4 firstname.lastname@example.org 444444 41 test5 email@example.com 555555 42 test6 firstname.lastname@example.org 666666 Rides: id Person_id start_time end_time distance 23 38 2018-08-08T12:12:12 2018-08-08T13:12:12 1000 24 39 2018-08-08T12:12:12 2018-08-08T14:12:12 1100 25 40 2018-08-08T12:12:12 2018-08-08T13:12:12 1200 26 41 2018-08-08T12:12:12 2018-08-08T15:12:12 1300 27 42 2018-08-08T12:12:12 2018-08-08T15:12:12 600 28 42 2018-08-08T12:12:12 2018-08-08T13:12:12 700 29 41 2018-08-08T12:12:12 2018-08-08T16:12:12 800
<strong>My Query is</strong> : Person is a driver who rides cab.
start_time is the start time of his ride. end_time is the end time of his ride. Distance is distance in KM.
I want to pass start and end time to the query.
<strong>Result should include</strong> TOP 5 Persons with their email,name, total minutes of Ride, maximum ride duration in minutes.
Only rides that starts and ends within the mentioned durations should be counted.
<strong>Note:</strong> Total minutes of all rides by a person is the criteria for TOP.Answer1:
Date/time functions are notoriously dependent on the database. You need to tag the database you are using.
I am going to assume that you are using MySQL.
You can answer the "top 5" by using just the
select r.person_id, sum(to_seconds(r.end_time) - to_seconds(r.start_time)) as duration_seconds from rides r where r.start_time >= ? and r.end_time <= ? group by r.person_id order by duration_seconds desc limit 5;
This is basically what you want. Now you just need to add in the
person information and convert the seconds to minutes:
select p.*, top5.duration_seconds / 60 as duration_minutes from (select r.person_id, sum(to_seconds(r.end_time) - to_seconds(r.start_time)) as duration_seconds from rides r where r.start_time >= ? and r.end_time <= ? group by r.person_id order by duration_seconds desc limit 5 ) top5 join person p on top5.person_id = p.id;
Other databases have other ways of extracting the duration between two
? is the placeholder for the values defining the time range.