Below is my code for average total distance each day of week and each month.

I realized something is very wrong though. The dataset does not include data on when days have no flghts. the AVG function will on average days on which there is a flight not including a 0 value for days with no flights.

–Selects average distance by day

select dep_day_of_week, avg(total_distance)

from

–Subquery to sum distance from every flight grouped by day. The CASE is to order by day correctly.

(select case

when dep_day_of_week = ‘Monday’ then 1

when dep_day_of_week = ‘Tuesday’ then 2

when dep_day_of_week = ‘Wednesday’ then 3

when dep_day_of_week = ‘Thursday’ then 4

when dep_day_of_week = ‘Friday’ then 5

when dep_day_of_week = ‘Saturday’ then 6

when dep_day_of_week = ‘Sunday’ then 7

else null

end as daysnumbered, dep_day_of_week, dep_month,dep_date, sum(distance) as total_distance

from flights

Group by dep_date)

–grouped by days to get average of miles for every day

group by daysnumbered

order by daysnumbered

;

select month, avg(total_distance)

from

## –SUBSTR converts dep_month into month only

(select dep_month, substr(dep_month,6,2) as month, sum(distance) as total_distance

from flights

group by 1

order by 1)

group by 1

order by 1;