Question:

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:

Tables:

Person: id Name Email reg_number 37 test1 test1@gmail.com 111111 38 test2 test2@gmail.com 222222 39 test3 test3@gmail.com 333333 40 test4 test4@gmail.com 444444 41 test5 test5@gmail.com 555555 42 test6 test6@gmail.com 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.

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 rides table:

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 datetime values.

The ? is the placeholder for the values defining the time range.