Transforming Techniques

📚 Schema reference · PNW flights 5 tables

airlines

carrier
name

airports

faa
name
lat
lon
alt
tz
dst
tzone

flights

year
month
day
dep_time
sched_dep_time
dep_delay
arr_time
sched_arr_time
arr_delay
carrier
flight
tailnum
origin
dest
air_time
distance
hour
minute
time_hour

planes

tailnum
year
type
manufacturer
model
engines
seats
speed
engine

weather

origin
year
month
day
hour
temp
dewp
humid
wind_dir
wind_speed
wind_gust
precip
pressure
visib
time_hour

Transforming

Categorize flights by distance as Short, Medium, or Long. Use ‘Short’ for distances less than 500 miles, ‘Medium’ for 500 to 2000 miles, and ‘Long’ for more than 2000 miles. Show the distance column alongside the category as flight_distance_category.

Hint 1

-- HINT: Use CASE WHEN statements to categorize data based on conditions. The syntax is CASE WHEN condition THEN result END. For example:
SELECT column1,
CASE 
WHEN condition1 THEN 'Result1'
WHEN condition2 THEN 'Result2'
ELSE 'OtherResult'
END AS new_column
FROM table_name;

Hint 2

-- HINT: Fill in the blanks to categorize flights by distance. Use 'Short' for distances less than 500 miles, 'Medium' for 500 to 2000 miles, and 'Long' for more than 2000 miles.
SELECT distance, CASE 
WHEN distance < ___ THEN 'Short'
WHEN distance BETWEEN ___ AND ___ THEN 'Medium'
ELSE 'Long'
END AS flight_distance_category
FROM flights;

Calculate the average speed of flights grouped by origin and destination and order by the fastest average speed. Show origin, dest, and the average speed as avg_speed.

To calculate the average speed, use the distance and air_time columns. Remember to convert air_time from minutes to hours for speed in miles per hour.

Hint 1

-- HINT: Speed in mph is distance divided by hours, and air_time is in minutes,
-- so divide air_time by 60 inside the AVG. Group by both origin and dest, and
-- order the results by the average speed in descending order to get the
-- fastest speeds at the top.

Hint 2

-- HINT: Complete the query by calculating the average speed and ordering the results. Fill in the blanks:
SELECT origin, dest,
AVG(___ / (___ / 60)) AS avg_speed
FROM flights
GROUP BY ___, ___
ORDER BY ___ DESC;