You have been given a table called travel_detail and you need to calculate the total travel hours between the cities.
For example, the total travel hours between Delhi and Goa is 235 (i.e 125+110)
Write an SQL query to calculate the total travel hours between cities
Sample Input:-
Sample Output:-
My Solution:-
We have to consider the total travel hours between City1 to City2 and City2 to City1 as one single pair. So we will compare the City names with greater than and less than operator to swap the same side then we just have to calculate the total travel hours.
Final Query:-
WITH travel(city_l, city_2, travel_time_hours) AS
(
SELECT city_1 = CASE
WHEN from_city < to_city THEN from_city ELSE to_city END,
city_2 = CASE
WHEN from_city > to_city THEN from_city ELSE to_city END ,
travel_time_hours FROM travel_detail
)
SELECT city_1, city_2,
SUM(travel_time_hours) AS total_travel_hours FROM travel GROUP BY city_1, city_2 ORDER BY city_1, city_2;
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.
