Warehouse User Guide > Appendixes > Run Sample Queries
Was this helpful?
Run Sample Queries
The following table contains queries you can run against the “sample” data set, which is included with every warehouse you provision. It contains airline data from the U.S. Department of Transportation Bureau of Transportation Statistics (the most current data available from https://transtats.bts.gov).
Copy the commands from the “SQL” column and paste them into your query document. To use the Query Editor, see Run Your Own Queries.
Note:  If you are accessing the warehouse using the SQL Command Line Interface (CLI, installed with the Avalanche Client Runtime Package) through a terminal monitor, be sure to end each command with \g. If you are using a JDBC connection and a third-party tool such as DBeaver, the \g line terminator is not needed.
Description
SQL
Which day has the most flights from 2014–2019 ordered by number of flights descending
SELECT description AS day_of_week,
       Count(*)    AS num_flights
FROM   sample.ontime a,
       sample.l_weekdays b
WHERE  a.year BETWEEN 2014 AND 2019
       AND a.dayofweek = b.code
GROUP  BY 1
ORDER  BY 2 DESC
Which day of the week has the most delayed flights from 2014–2019, ordered by number of flights descending
SELECT description AS day_of_week,
       Count(*)    AS no_flights
FROM   sample.ontime a,
       sample.l_weekdays b
WHERE  depdelay > 10
       AND "year" BETWEEN 2014 AND 2019
       AND a.dayofweek = b.code
GROUP  BY 1
ORDER  BY no_flights DESC
Total number of flights where year > 2014
SELECT year,
       Count(*) AS numberofflights
FROM   sample.ontime
WHERE  year > 2014
GROUP  BY year
Percentage of delays for each carrier for 2018.
SELECT t3.description                   AS airline, 
       total, 
       delayed, 
       Varchar(delayed*100/total) + '%' AS percentage 
FROM   (SELECT reporting_airline, 
               Count(*) AS delayed 
        FROM   sample.ontime 
        WHERE  depdelay > 10 
               AND year = 2018 
        GROUP  BY reporting_airline) t 
        JOIN (SELECT reporting_airline, 
                    Count(*) AS total 
             FROM   sample.ontime 
             WHERE  year = 2018 
             GROUP  BY reporting_airline) t2 
         ON ( t.reporting_airline = t2.reporting_airline ) 
        JOIN sample.l_unique_carriers t3 
         ON Char(t3.code) = t.reporting_airline 
ORDER  BY percentage DESC
Count all the rows in the ontime table
SELECT Count(*) AS count 
FROM   sample.ontime 
Which cities are served the most by other cities
SELECT TOP 10 destcityname                   AS Destination, 
              Count(DISTINCT origincityname) AS OriginCityCount 
FROM   sample.ontime 
WHERE  year = 2018 
GROUP  BY 1 
ORDER  BY 2 DESC
Top 10 Markets with most destinations
SELECT m.description AS Market,
Listagg(DISTINCT c.origincityname, '; ') within GROUP (ORDER BY c.origincityname) AS originarea,
count(*) AS num_of_destinations
FROM (
SELECT origincitymarketid,
destcitymarketid
FROM sample.ontime
WHERE year = 2018
GROUP BY 1,
2) ct
JOIN
(
SELECT origincitymarketid,
origincityname
FROM sample.ontime
GROUP BY 1,
2) c
ON c.origincitymarketid = ct.origincitymarketid
JOIN sample.l_city_market_id m on m.code= ct.origincitymarketid
GROUP BY 1
ORDER BY 3 DESC
FETCH first 10 rows only
Reasons for aircraft delays for 2018
SELECT 'Carrier Delay' AS Type,
       Count(*)        AS Total
FROM   sample.ontime
WHERE  ( carrierdelay != 0
         AND carrierdelay IS NOT NULL )
       AND year = 2018
UNION
SELECT 'Weather Delay' AS Type,
       Count(*)        AS Total
FROM   sample.ontime
WHERE  ( weatherdelay != 0
         AND weatherdelay IS NOT NULL )
       AND year = 2018
UNION
SELECT 'Nas Delay' AS Type,
       Count(*)    AS Total
FROM   sample.ontime
WHERE  ( nasdelay != 0
         AND nasdelay IS NOT NULL )
       AND year = 2018
UNION
SELECT 'Security Delay' AS Type,
       Count(*)         AS Total
FROM   sample.ontime 
WHERE  ( securitydelay != 0
         AND securitydelay IS NOT NULL )
       AND year = 2018
UNION 
SELECT 'Late Aircraft' AS Type,
       Count(*)        AS Total
FROM   sample.ontime 
WHERE  ( lateaircraftdelay != 0
         AND lateaircraftdelay IS NOT NULL )
       AND year = 2018 
ORDER  BY 2 DESC
To load your own data into a warehouse, go to the Data Loading Guide. See Data Loading Overview.
 
Last modified date: 10/30/2024