Table transformation using subqueries

Date: April 17th 2016
Last updated: April 17th 2016

This example comes from codeacademy:

• https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-iii?utm_source=customerio&utm_campaign=sql_table_transformation_announcement&utm_medium=email_newsletter&utm_content=cta

Useful references

• http://www.w3schools.com/sql/sql_orderby.asp

/* select three columns using reference to 'a' */
/* note the extra field in the subquery - dep_date */
SELECT a.dep_month,
       a.dep_day_of_week,
       SUM(a.flight_distance) AS total_distance
  /* start subquery inside 'FROM' */
  FROM (
        /* sum the flight distance for each day */
        /* this is done using GROUP BY 1,2,3 */
        /* ensure the subquery is referenced by 'a' */
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               SUM(distance) AS flight_distance
          FROM flights
         GROUP BY 1,2,3
       ) a
 /* group and order the final table */      
 GROUP BY 2,1
 /* extra stuff - order top 10 by furthest to least distance */
 ORDER BY 3 DESC LIMIT 10;