Query for average trip time for trips - BigQuery [closed]

Question

Flowlogistic moves equipment and parts for the mining company by air. In 2019 Flowlogistic managed more than 8,000 flights for this customer. The log file contains a record of each of those flights, including the date, the vendor airline, distance traveled, and duration of each flight.

The Sample data is here. Sample Data

I used the following schema:

date: integer
origin: string
destination: string
airline: string
miles: float
minutes: integer
duration: string

• minutes contains the travel time for each flight from take-off to landing measured in minutes.
• origin and destination use the IATA three-character airport codes.
• LHR = London Heathrow Airport, United Kingdom

• FRA = Frankfurt Airport, Germany

• KUL = Kuala Lumpur International Airport, Malaysia

airline contains the name of the airline vendor contracted to Flowlogistic

I would like to create a query that produces the average trip time for trips originating from the airport in Frankfurt, Germany (FRA), and destined for the airport in Kuala Lumpur, Malaysia (KUL), and group the results by the airline. The resulting average times should be similar.

I would like to create a query that produces the average trip time for trips originating from London Heathrow Airport, United Kingdom (LHR) and destined for the airport in Kuala Lumpur, Malaysia (KUL), and group the results by airline, and order them from lowest to highest. The resulting average times should reveal whether the airline, PlanePeople Air, kept its promise to use faster airplanes from Heathrow Airport.

I am tired of errors, Can anyone help. I really appreciate your guidance. Thanks

Answer 1

Just an FYI, in your sample data you don't have any KUL destination airports. Here is the query that will give you the results you are looking for. Please substitute the WHERE predicate clause with the origin and destination airport of your choice:

(I have created a dataset name flowlogistic and a table named flights)

select  origin, 
        destination,
        airline,
        avg(minutes) as average_trip_time
from    `flowlogistic.flights`
where   origin = 'FRA' and destination = 'CDG'
group by 
        origin,
        destination,
        airline
order by 
    average_trip_time;

Answer 2

SELECT airline , avg(minutes) as avg_time
FROM 'sample_data.flights'
WHERE origin = 'FRA' and destination = 'KUL'
GROUP BY airline