How to calculate total travel hours in SQL?
Question
You are working for an airline company and you have received a dataset of Pilot’s travel time between cities. You need to determine the reciprocal and find the total travel hours between cities. That means, the pilot’s travel from city ‘1’ to ‘2’ and city ‘2’ to ‘1’ should be considered as one and then the total travel hours has to be calculated.
Write an SQL query to achieve the expected output.
Answer
Let us create the test data first.
To achieve the desired result, you should swap the city names within the row based on some order. That means, you can sort the from_city and to_city names in a row in ascending or descending order and then keep the first city as city_1
and the other as city_2. To do this, you can use less than < or greater than > operator on from_city
and to_city
.
This way you will be able to aggregate the travel_time_hours
by grouping city_1
and city_2
.
This is the solution I have come up with. If you have achieved the output in other way, let me know in comments.
If you like this interview question and answer, you may also like the below scenario based questions.