analytics_benchmark/queries/sql/2.sql at main · toddstoffel/analytics_benchmark · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
-- Query 2: Airline delay analysis by type and year
-- Analyzes different types of delays (Airline Delay, Late Aircraft Delay,
-- Air System Delay, and Weather Delay) for each airline by year, demonstrating fact/dimension joins

WITH airline_delays AS (
    SELECT
        a.airline,
        f.year,
        'Airline Delay' AS delay_type,
        COUNT(*) AS delay
    FROM
        flights f
    JOIN
        airlines a ON f.carrier = a.iata_code
    WHERE
        f.carrier_delay > 0
    GROUP BY
        a.airline, f.year

    UNION ALL

    SELECT
        a.airline,
        f.year,
        'Late Aircraft Delay' AS delay_type,
        COUNT(*) AS delay
    FROM
        flights f
    JOIN
        airlines a ON f.carrier = a.iata_code
    WHERE
        f.late_aircraft_delay > 0
    GROUP BY
        a.airline, f.year

    UNION ALL

    SELECT
        a.airline,
        f.year,
        'Air System Delay' AS delay_type,
        COUNT(*) AS delay
    FROM
        flights f
    JOIN
        airlines a ON f.carrier = a.iata_code
    WHERE
        f.nas_delay > 0
    GROUP BY
        a.airline, f.year

    UNION ALL

    SELECT
        a.airline,
        f.year,
        'Weather Delay' AS delay_type,
        COUNT(*) AS delay
    FROM
        flights f
    JOIN
        airlines a ON f.carrier = a.iata_code
    WHERE
        f.weather_delay > 0
    GROUP BY
        a.airline, f.year
)
SELECT
    ad.airline,
    ad.year,
    ad.delay_type,
    ad.delay
FROM
    airline_delays ad
ORDER BY
    ad.airline, ad.year, ad.delay_type;