GraphFrames OnTime Implementation

Schema Diagram and Graph Statistics

Since GraphFrames does not support printing a graph schema diagram, we reuse the Neo4j schema diagram for reference.

Node Statistics

Node Label	Count
Airport	379

Edge Statistics

Relationship	Count
FLIGHT	17,412,876

Data Loading

Overview

Data import is handled in four parts. First we upload all the raw data files into DataFrames. Then we define nodes and edges. Finally, we create graph schema.

DataFrames

The first thing we do would be to load all CSV files into a big DataFrame. We do it using a for-loop.

python

import time
dfs = {} 
for year in range(2020, 2023):
    timeacc = 0
    for month in range(1, 13):
        path = f"hdfs:///ontime20-22/{year}_{month}.csv"
        start_time = time.time()
        df = (
            spark.read
                 .option("header", "true")
                 .option("inferSchema", "true")
                 .csv(path)
        )
        time_cost = time.time() - start_time
        print(f"Loaded file for {year}_{month} in {time_cost:.2f} seconds")
        dfs[(year, month)] = df
        timeacc += time_cost
    print(f"Average loading time for year {year} is {timeacc / 12:.2f} seconds")

Build Vertices

Then we define the airport node.

python

from pyspark.sql.functions import expr
from functools import reduce
from pyspark.sql import DataFrame

start_time = time.time()
all_monthly_dfs = list(dfs.values())
full_flight_history = reduce(DataFrame.unionByName, all_monthly_dfs)

airport_vertices = (
    full_flight_history.selectExpr(
        """
        stack(2, 
            OriginAirportID, OriginCityName, Origin, OriginStateName,
            DestAirportID, DestCityName, Dest, DestStateName
        ) as (id, city, code, state)
        """
    )
    .dropna(subset=["id"])
    .dropDuplicates(["id"]) 
)

airport_vertices.printSchema()
airport_vertices.show(10, truncate=False)
print(f"Total Unique Airports: {airport_vertices.count()}")
time_cost = time.time() - start_time
print(f"Building all airport vertices require {time_cost:.2f} seconds")

Define Edge

Next step is to define edges.

python

from pyspark.sql.functions import col

columns_to_select = [
    col("OriginAirportID").alias("src"),
    col("DestAirportID").alias("dst"),
    "FlightDate", 
    "Reporting_Airline", 
    "Flight_Number_Reporting_Airline",
    "Year", "Quarter", "Month", "DayofMonth",
    "IATA_CODE_Reporting_Airline", "DOT_ID_Reporting_Airline", 
    "Tail_Number", 
    "OriginAirportSeqID", "DestAirportSeqID",
    "CRSDepTime", "DepTime", "DepDelay", "DepDelayMinutes", "DepDel15", 
    "DepartureDelayGroups", "DepTimeBlk", "TaxiOut", "WheelsOff",
    "CRSArrTime", "ArrTime", "ArrDelay", "ArrDelayMinutes", "ArrDel15", 
    "ArrivalDelayGroups", "ArrTimeBlk", "TaxiIn", "WheelsOn",
    "Cancelled", "CancellationCode", "Diverted",
    "CRSElapsedTime", "ActualElapsedTime", "AirTime", "Flights", 
    "Distance", "DistanceGroup",
    "CarrierDelay", "WeatherDelay", "NASDelay", "SecurityDelay", "LateAircraftDelay"
]
flight_edges = full_flight_history.select(columns_to_select)
flight_edges.printSchema()

Define Graph

Now that we have defined our vertex and edge, we can create graph schema with those.

python

from graphframes import GraphFrames
g = GraphFrames(airport_vertices, flight_edges)
print(f"Total Flights (Edges): {g.edges.count()}")

We can print the edge count to verify.

Queries

Select a query below to view its logic and description.

1. Neighborhood

2. Path

3. Pattern

4. Aggregation

5. Analytics

6. Hybrid

7. Others

No queries in this category.

Q1. Direct Destinations from an Airport

Subcategory: T1.1 One-Hop Neighbors

This query lists airports directly reachable from a selected origin airport by one outgoing FLIGHT relationship during a selected year.

python

# Q1. Direct Destinations from an Airport
q1 = (
    g.find("(a)-[e1]->(b)")
    .where((F.col("a.code") == SOURCE_CODE) & (F.col("e1.Year") == 2020))
    .select(
        F.col("b.id").alias("airport_id"),
        F.col("b.code").alias("code"),
        F.col("b.city").alias("city"),
        F.col("b.state").alias("state"),
    )
    .distinct()
    .orderBy("code")
)

Equivalent query in GQL standard:

gql

MATCH (origin:Airport {airport_id: $originAirportId})-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
RETURN DISTINCT dest.airport_id AS airport_id, dest.code AS code, dest.city AS city, dest.state AS state
ORDER BY code
LIMIT $limit;

Q2. Airports Reachable within K Hops

Subcategory: T1.2 k-Hop Neighborhood

This query returns airports reachable from a selected airport within three directed flight legs.

python

# Q2. Airports Reachable within K Hops
def q2_reachable_airports(pattern, hop_count):
    return (
        route_graph_2020.find(pattern)
        .where(F.col("origin.code") == SOURCE_CODE)
        .select(
            F.col("dest.id").alias("airport_id"),
            F.col("dest.code").alias("code"),
            F.col("dest.city").alias("city"),
            F.lit(hop_count).alias("hop_count"),
        )
    )


q2_hop1 = q2_reachable_airports("(origin)-[flight*1]->(dest)", 1)
q2_hop2 = q2_reachable_airports("(origin)-[flight*2]->(dest)", 2)
q2_hop3 = q2_reachable_airports("(origin)-[flight*3]->(dest)", 3)
q2 = (
    q2_hop1.unionByName(q2_hop2).unionByName(q2_hop3)
    .distinct()
    .orderBy("hop_count", "code")
)

Equivalent query in GQL standard:

gql

MATCH p = (:Airport {airport_id: $originAirportId})-[f:FLIGHT]->{1,3}(dest:Airport)
WHERE all(flight IN f WHERE flight.year = $Year)
RETURN DISTINCT
    dest.airport_id AS airport_id,
    dest.code AS code,
    dest.city AS city,
    length(p) AS hop_count
ORDER BY hop_count, code
LIMIT $limit;

Q3. Airports Exactly Three Flights Away

Subcategory: T1.3 Exact kth-Hop Neighbors

This query finds airports exactly three outgoing flight legs away from a selected origin airport.

python

# Q3. Airports Exactly Three Flights Away
q3 = (
    route_graph_2020.find("(origin)-[flight*3]->(dest)")
    .where(F.col("origin.code") == SOURCE_CODE)
    .select(
        F.col("dest.id").alias("airport_id"),
        F.col("dest.code").alias("code"),
        F.col("dest.city").alias("city"),
        F.lit(3).alias("hop_count"),
    )
    .distinct()
    .orderBy("code")
)

Equivalent query in GQL standard:

gql

MATCH p = (:Airport {airport_id: $originAirportId})-[f:FLIGHT]->{3,3}(dest:Airport)
WHERE all(flight IN f WHERE flight.year = $Year)
RETURN DISTINCT dest.airport_id AS airport_id, dest.code AS code, dest.city AS city, length(p) AS hop_count
ORDER BY code
LIMIT $limit;

Q4. Delayed One-Hop Destinations

Subcategory: T1.4 Neighborhood with Property Filter

This query filters one-hop destinations by departure delay on the FLIGHT relationship.

python

# Q4. Delayed One-Hop Destinations
q4 = (
    g.find("(a)-[e1]->(b)")
    .where(
        (F.col("a.code") == SOURCE_CODE)
        & (F.col("e1.Year") == 2020)
        & (F.col("e1.DepDelayMinutes") >= 120)
    )
    .select(
        F.col("b.id").alias("airport_id"),
        F.col("b.code").alias("code"),
        F.col("e1.FlightDate").alias("flight_date"),
        F.col("e1.IATA_CODE_Reporting_Airline").alias("airline"),
        F.col("e1.DepDelayMinutes").alias("departure_delay"),
    )
    .orderBy(F.desc("departure_delay"))
)

Equivalent query in GQL standard:

gql

MATCH (origin:Airport {airport_id: $originAirportId})-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
  AND f.DepDelayMinutes >= $minDelayMinutes
RETURN dest.airport_id AS airport_id, dest.code AS code, f.FlightDate AS flight_date,
       f.IATA_CODE_Reporting_Airline AS airline, f.DepDelayMinutes AS departure_delay
ORDER BY departure_delay DESC
LIMIT $limit;

Q5. Airports by State and Code Prefix

Subcategory: T2.1 Node Filtering (Single Node Paths)

This query filters Airport nodes by imported node properties without traversing relationships.

python

# Q5. Airports by State and Code Prefix
q5 = (
    route_graph.find("(airport)-[]-(neighbor)")
    .where(
        (F.col("airport.state") == "CA")
        & F.col("airport.code").startswith("S")
    )
    .select(
        F.col("airport.id").alias("airport_id"),
        F.col("airport.code").alias("code"),
        F.col("airport.city").alias("city"),
        F.col("airport.state_name").alias("state_name"),
    )
    .distinct()
    .orderBy("code")
)

Equivalent query in GQL standard:

gql

MATCH (airport:Airport)
WHERE airport.state = $state
  AND airport.code STARTS WITH $codePrefix
RETURN airport.airport_id AS airport_id, airport.code AS code, airport.city AS city, airport.stateName AS state_name
ORDER BY code
LIMIT $limit;

Q6. Long Non-Cancelled Flights

Subcategory: T2.2 Edge Filtering

This query filters FLIGHT relationships by relationship properties loaded from the OnTime CSV files.

python

# Q6. Long Non-Cancelled Flights
q6 = (
    g.find("(origin)-[flight]->(dest)")
    .where(
        (F.col("flight.Year") == 2020)
        & (F.col("flight.Cancelled") == 0)
        & (F.col("flight.Distance") >= 2000)
    )
    .select(
        F.col("origin.code").alias("origin"),
        F.col("dest.code").alias("dest"),
        F.col("flight.FlightDate").alias("flight_date"),
        F.col("flight.IATA_CODE_Reporting_Airline").alias("airline"),
        F.col("flight.Distance").alias("distance"),
    )
    .orderBy(F.desc("distance"))
)

Equivalent query in GQL standard:

gql

MATCH (origin:Airport)-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
  AND f.Cancelled = 0
  AND f.Distance >= $minDistance
RETURN origin.code AS origin, dest.code AS dest, f.FlightDate AS flight_date,
       f.IATA_CODE_Reporting_Airline AS airline, f.Distance AS distance
ORDER BY distance DESC
LIMIT $limit;

Q7. Reachability between Two Airports

Subcategory: T2.3 Reachability between Two Nodes

This query checks whether a destination airport is reachable from a source airport within three directed flight legs.

python

# Q7. Reachability between Two Airports
q7_paths = route_graph.all_paths(
    from_expr=f"code = '{SOURCE_CODE}'",
    to_expr=f"code = '{TARGET_CODE}'",
    max_path_length=3,
    is_directed=True,
    use_local_checkpoints=True,
)
q7 = q7_paths.agg((F.count(F.lit(1)) > 0).alias("reachable"))

Equivalent query in GQL standard:

gql

MATCH p = ANY (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(:Airport {airport_id: $targetAirportId})
RETURN true AS reachable;

Q8. Shortest Airport Route

Subcategory: T2.4 Shortest Path

This query finds the shortest directed airport-to-airport route up to four flight legs.

python

# Q8. Shortest Airport Route
q8_raw = route_graph.bfs(
    fromExpr=f"code = '{SOURCE_CODE}'",
    toExpr=f"code = '{TARGET_CODE}'",
    maxPathLength=4,
)
q8 = format_bfs_result(q8_raw)

Equivalent query in GQL standard:

gql

MATCH p = SHORTEST (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,4}(:Airport {airport_id: $targetAirportId})
RETURN p, length(p) AS hop_count;

Q9. Simple Routes up to Three Legs

Subcategory: T2.5 All Simple Paths up to Length k

This query enumerates simple directed routes between two airports up to three flight legs.

python

# Q9. Simple Routes up to Three Legs
q9_hop1 = (
    route_graph.find("(a)-[]->(b)")
    .where((F.col("a.code") == SOURCE_CODE) & (F.col("b.code") == TARGET_CODE))
    .select(
        F.array(F.col("a.id"), F.col("b.id")).alias("path"),
        F.lit(1).alias("hop_count"),
    )
)
q9_hop2 = (
    route_graph.find("(a)-[]->(b); (b)-[]->(c)")
    .where(
        (F.col("a.code") == SOURCE_CODE)
        & (F.col("c.code") == TARGET_CODE)
        & distinct_vertices("a", "b", "c")
    )
    .select(
        F.array(F.col("a.id"), F.col("b.id"), F.col("c.id")).alias("path"),
        F.lit(2).alias("hop_count"),
    )
)
q9_hop3 = (
    route_graph.find("(a)-[]->(b); (b)-[]->(c); (c)-[]->(d)")
    .where(
        (F.col("a.code") == SOURCE_CODE)
        & (F.col("d.code") == TARGET_CODE)
        & distinct_vertices("a", "b", "c", "d")
    )
    .select(
        F.array(
            F.col("a.id"), F.col("b.id"), F.col("c.id"), F.col("d.id")
        ).alias("path"),
        F.lit(3).alias("hop_count"),
    )
)
q9 = (
    q9_hop1.unionByName(q9_hop2).unionByName(q9_hop3)
    .orderBy("hop_count")
)

Equivalent query in GQL standard:

gql

MATCH p = ACYCLIC (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(:Airport {airport_id: $targetAirportId})
RETURN p, length(p) AS hop_count
ORDER BY hop_count
LIMIT $limit;

Q10. Top Weighted Routes by Distance

Subcategory: T2.6 top-K Weighted Paths

This query ranks bounded airport routes by cumulative flight distance.

python

# Q10. Top Weighted Routes by Distance
q10_hop1 = (
    g.find("(a)-[e1]->(b)")
    .where((F.col("a.code") == SOURCE_CODE) & (F.col("b.code") == TARGET_CODE))
    .select(
        path_value(["a", "b"], ["e1"]),
        F.lit(1).alias("hop_count"),
        F.coalesce(F.col("e1.Distance"), F.lit(0.0)).alias("total_distance"),
    )
)
q10_hop2 = (
    g.find("(a)-[e1]->(b); (b)-[e2]->(c)")
    .where(
        (F.col("a.code") == SOURCE_CODE)
        & (F.col("c.code") == TARGET_CODE)
        & distinct_vertices("a", "b", "c")
    )
    .select(
        path_value(["a", "b", "c"], ["e1", "e2"]),
        F.lit(2).alias("hop_count"),
        (
            F.coalesce(F.col("e1.Distance"), F.lit(0.0))
            + F.coalesce(F.col("e2.Distance"), F.lit(0.0))
        ).alias("total_distance"),
    )
)
q10_hop3 = (
    g.find("(a)-[e1]->(b); (b)-[e2]->(c); (c)-[e3]->(d)")
    .where(
        (F.col("a.code") == SOURCE_CODE)
        & (F.col("d.code") == TARGET_CODE)
        & distinct_vertices("a", "b", "c", "d")
    )
    .select(
        path_value(["a", "b", "c", "d"], ["e1", "e2", "e3"]),
        F.lit(3).alias("hop_count"),
        (
            F.coalesce(F.col("e1.Distance"), F.lit(0.0))
            + F.coalesce(F.col("e2.Distance"), F.lit(0.0))
            + F.coalesce(F.col("e3.Distance"), F.lit(0.0))
        ).alias("total_distance"),
    )
)
q10 = (
    q10_hop1.unionByName(q10_hop2).unionByName(q10_hop3)
    .orderBy("total_distance")
)

Equivalent query in GQL standard:

gql

MATCH p = ACYCLIC (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(:Airport {airport_id: $targetAirportId})
WITH p, reduce(total = 0, f IN relationships(p) | total + coalesce(f.Distance, 0)) AS total_distance
RETURN p, length(p) AS hop_count, total_distance
ORDER BY total_distance ASC
LIMIT $limit;

Q11. Directed Triangle of Airports

Subcategory: T3.1 Triangle Detection

This query detects directed three-airport cycles in the flight graph.

python

# Q11. Directed Triangle of Airports
q11 = (
    g.find("(a)-[ab]->(b); (b)-[bc]->(c); (c)-[ca]->(a)")
    .where((F.col("a.id") < F.col("b.id")) & (F.col("b.id") < F.col("c.id")))
    .select(
        F.col("a.code").alias("airport_a"),
        F.col("b.code").alias("airport_b"),
        F.col("c.code").alias("airport_c"),
    )
)

Equivalent query in GQL standard:

gql

MATCH (a:Airport)-[:FLIGHT]->(b:Airport)-[:FLIGHT]->(c:Airport)-[:FLIGHT]->(a)
WHERE a.airport_id < b.airport_id AND b.airport_id < c.airport_id
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c
LIMIT $limit;

Q12. Four-Airport Cycle

Subcategory: T3.2 Square Detection

This query detects directed four-airport cycles in the flight graph.

python

# Q12. Four-Airport Cycle
q12 = (
    g.find(
        "(a)-[ab]->(b); (b)-[bc]->(c); "
        "(c)-[cd]->(d); (d)-[da]->(a)"
    )
    .where(distinct_vertices("a", "b", "c", "d"))
    .select(
        F.col("a.code").alias("airport_a"),
        F.col("b.code").alias("airport_b"),
        F.col("c.code").alias("airport_c"),
        F.col("d.code").alias("airport_d"),
    )
)

Equivalent query in GQL standard:

gql

MATCH (a:Airport)-[:FLIGHT]->(b:Airport)-[:FLIGHT]->(c:Airport)-[:FLIGHT]->(d:Airport)-[:FLIGHT]->(a)
WHERE a <> b AND a <> c AND a <> d AND b <> c AND b <> d AND c <> d
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c, d.code AS airport_d
LIMIT $limit;

Q13. Hub Airports by Distinct Destinations

Subcategory: T3.3 Star Pattern Query

This query identifies hub airports with many distinct outgoing destination neighbors.

python

# Q13. Hub Airports by Distinct Destinations
q13 = (
    route_graph_2020.find("(center)-[]->(dest)")
    .groupBy(
        F.col("center.id").alias("airport_id"),
        F.col("center.code").alias("code"),
        F.col("center.city").alias("city"),
    )
    .agg(F.countDistinct(F.col("dest.id")).alias("destination_count"))
    .where(F.col("destination_count") >= 190)
    .orderBy(F.desc("destination_count"))
)

Equivalent query in GQL standard:

gql

MATCH (center:Airport)-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
WITH center, count(DISTINCT dest) AS destination_count
WHERE destination_count >= $minDegree
RETURN center.airport_id AS airport_id, center.code AS code, center.city AS city, destination_count
ORDER BY destination_count DESC
LIMIT $limit;

Q14. Four-Airport Clique

Subcategory: T3.4 4-Clique

This query finds sets of four airports where every pair has at least one flight relationship in either direction.

python

# Q14. Four-Airport Clique
q14 = (
    g.find(
        "(a)-[]-(b); (a)-[]-(c); (a)-[]-(d); "
        "(b)-[]-(c); (b)-[]-(d); (c)-[]-(d)"
    )
    .where(
        (F.col("a.id") < F.col("b.id"))
        & (F.col("b.id") < F.col("c.id"))
        & (F.col("c.id") < F.col("d.id"))
    )
    .select(
        F.col("a.code").alias("airport_a"),
        F.col("b.code").alias("airport_b"),
        F.col("c.code").alias("airport_c"),
        F.col("d.code").alias("airport_d"),
    )
)

Equivalent query in GQL standard:

gql

MATCH (a:Airport)-[:FLIGHT]-(b:Airport),
      (a)-[:FLIGHT]-(c:Airport),
      (a)-[:FLIGHT]-(d:Airport),
      (b)-[:FLIGHT]-(c),
      (b)-[:FLIGHT]-(d),
      (c)-[:FLIGHT]-(d)
WHERE a.airport_id < b.airport_id AND b.airport_id < c.airport_id AND c.airport_id < d.airport_id
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c, d.code AS airport_d
LIMIT $limit;

Q15. Two-Hop Airport Connections

Subcategory: T3.5 Two-hop Join Pattern

This query finds two-leg connections through an intermediate airport for a selected source airport.

python

# Q15. Two-Hop Airport Connections
q15 = (
    g.find("(a)-[e1]->(b); (b)-[e2]->(c)")
    .where(
        (F.col("a.code") == SOURCE_CODE)
        & (F.col("e1.Year") == 2020)
        & (F.col("e2.Year") == 2020)
        & (F.col("a.id") != F.col("c.id"))
    )
    .groupBy(
        F.col("b.code").alias("connection_airport"),
        F.col("c.code").alias("destination"),
    )
    .agg(F.count(F.lit(1)).alias("connection_count"))
    .orderBy(F.desc("connection_count"))
)

Equivalent query in GQL standard:

gql

MATCH (source:Airport {airport_id: $sourceAirportId})-[first:FLIGHT]->(middle:Airport)-[second:FLIGHT]->(dest:Airport)
WHERE first.Year = $year
  AND second.Year = $year
  AND source <> dest
RETURN middle.code AS connection_airport, dest.code AS destination, count(*) AS connection_count
ORDER BY connection_count DESC
LIMIT $limit;

Q16. Three-Hop Line Pattern

Subcategory: T3.6 Line Pattern

This query matches a four-airport line connected by three consecutive FLIGHT relationships.

python

# Q16. Three-Hop Line Pattern
q16 = (
    g.find("(a)-[e1]->(b); (b)-[e2]->(c); (c)-[e3]->(d)")
    .where(distinct_vertices("a", "b", "c", "d"))
    .select(
        F.col("a.code").alias("airport_a"),
        F.col("b.code").alias("airport_b"),
        F.col("c.code").alias("airport_c"),
        F.col("d.code").alias("airport_d"),
        path_value(["a", "b", "c", "d"], ["e1", "e2", "e3"]),
    )
)

Equivalent query in GQL standard:

gql

MATCH p = (a:Airport)-[:FLIGHT]->(b:Airport)-[:FLIGHT]->(c:Airport)-[:FLIGHT]->(d:Airport)
WHERE a <> b AND a <> c AND a <> d AND b <> c AND b <> d AND c <> d
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c, d.code AS airport_d, p
LIMIT $limit;

Q17. Airport Degree Summary

Subcategory: T4.1 Ungrouped Node Degree Statistics by Label

This query computes global statistics over each airport's distinct outgoing destination count.

python

# Q17. Airport Degree Summary
q17_airport_degrees = (
    route_graph.find("(airport)-[]->(dest)")
    .groupBy(
        F.col("airport.id").alias("airport_id"),
        F.col("airport.state").alias("state"),
    )
    .agg(F.countDistinct(F.col("dest.id")).alias("out_degree"))
)
q17 = q17_airport_degrees.agg(
    F.count(F.lit(1)).alias("airport_count"),
    F.avg("out_degree").alias("avg_out_degree"),
    F.min("out_degree").alias("min_out_degree"),
    F.max("out_degree").alias("max_out_degree"),
)

Equivalent query in GQL standard:

gql

MATCH (airport:Airport)
OPTIONAL MATCH (airport)-[:FLIGHT]->(dest:Airport)
WITH airport, count(DISTINCT dest) AS out_degree
RETURN count(airport) AS airport_count,
       avg(out_degree) AS avg_out_degree,
       min(out_degree) AS min_out_degree,
       max(out_degree) AS max_out_degree;

Q18. State-Level Degree Summary

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query groups airport degree statistics by the airport state loaded on Airport nodes.

python

# Q18. State-Level Degree Summary
q18_airport_degrees = (
    route_graph.find("(airport)-[]->(dest)")
    .groupBy(
        F.col("airport.id").alias("airport_id"),
        F.col("airport.state").alias("state"),
    )
    .agg(F.countDistinct(F.col("dest.id")).alias("out_degree"))
)
q18 = (
    q18_airport_degrees
    .groupBy("state")
    .agg(
        F.count(F.lit(1)).alias("airport_count"),
        F.avg("out_degree").alias("avg_out_degree"),
        F.min("out_degree").alias("min_out_degree"),
        F.max("out_degree").alias("max_out_degree"),
    )
    .orderBy(F.desc("avg_out_degree"))
)

Equivalent query in GQL standard:

gql

MATCH (airport:Airport)
OPTIONAL MATCH (airport)-[:FLIGHT]->(dest:Airport)
WITH airport, count(DISTINCT dest) AS out_degree
RETURN airport.state AS state,
       count(airport) AS airport_count,
       avg(out_degree) AS avg_out_degree,
       min(out_degree) AS min_out_degree,
       max(out_degree) AS max_out_degree
ORDER BY avg_out_degree DESC
LIMIT $limit;

Q19. Flight Edge Distance Summary

Subcategory: T4.3 Ungrouped Edge Count and Property Statistics

This query summarizes numeric properties over all matching FLIGHT relationships.

python

# Q19. Flight Edge Distance Summary
q19 = (
    g.find("(origin)-[flight]->(dest)")
    .where(F.col("flight.Year") == 2020)
    .agg(
        F.count(F.col("flight")).alias("flight_count"),
        F.avg("flight.Distance").alias("avg_distance"),
        F.min("flight.Distance").alias("min_distance"),
        F.max("flight.Distance").alias("max_distance"),
        F.avg("flight.ArrDelayMinutes").alias("avg_arrival_delay"),
    )
)

Equivalent query in GQL standard:

gql

MATCH (:Airport)-[f:FLIGHT]->(:Airport)
WHERE f.Year = $year
RETURN count(f) AS flight_count,
       avg(f.Distance) AS avg_distance,
       min(f.Distance) AS min_distance,
       max(f.Distance) AS max_distance,
       avg(f.ArrDelayMinutes) AS avg_arrival_delay;

Q20. State Pair Flight Statistics

Subcategory: T4.4 Grouped Edge Count and Property Statistics

This query groups FLIGHT relationship statistics by origin and destination state.

python

# Q20. State Pair Flight Statistics
q20 = (
    g.find("(origin)-[flight]->(dest)")
    .where(F.col("flight.Year") == 2020)
    .groupBy(
        F.col("origin.state").alias("origin_state"),
        F.col("dest.state").alias("dest_state"),
    )
    .agg(
        F.count(F.col("flight")).alias("flight_count"),
        F.avg("flight.Distance").alias("avg_distance"),
        F.avg("flight.ArrDelayMinutes").alias("avg_arrival_delay"),
    )
    .orderBy(F.desc("flight_count"))
)

Equivalent query in GQL standard:

gql

MATCH (origin:Airport)-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
RETURN origin.state AS origin_state,
       dest.state AS dest_state,
       count(f) AS flight_count,
       avg(f.Distance) AS avg_distance,
       avg(f.ArrDelayMinutes) AS avg_arrival_delay
ORDER BY flight_count DESC
LIMIT $limit;

Q21. Route Counts by Path Length

Subcategory: T4.5 Path Counts by Length

This query counts bounded paths from a selected airport grouped by path length.

python

# Q21. Route Counts by Path Length
def q21_route_count(pattern, length):
    return (
        g.find(pattern)
        .where(F.col("source.code") == SOURCE_CODE)
        .agg(
            F.count(F.lit(1)).alias("path_count"),
            F.countDistinct(F.col("target.id")).alias("distinct_target_count"),
        )
        .withColumn("path_length", F.lit(length))
        .select("path_length", "path_count", "distinct_target_count")
    )


q21 = (
    q21_route_count("(source)-[flight*1]->(target)", 1)
    .unionByName(q21_route_count("(source)-[flight*2]->(target)", 2))
    .unionByName(q21_route_count("(source)-[flight*3]->(target)", 3))
    .orderBy("path_length")
)

Equivalent query in GQL standard:

gql

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(target:Airport)
RETURN length(p) AS path_length,
       count(p) AS path_count,
       count(DISTINCT target) AS distinct_target_count
ORDER BY path_length;

Q22. Route Counts by Length and Target State

Subcategory: T4.6 Grouped Path Counts by Length and Endpoint Type

This query extends path-length counts by also grouping endpoints by destination airport state.

python

# Q22. Route Counts by Length and Target State
def q22_route_count_by_state(pattern, length):
    return (
        g.find(pattern)
        .where(F.col("source.code") == SOURCE_CODE)
        .groupBy(F.col("target.state").alias("target_state"))
        .agg(
            F.count(F.lit(1)).alias("path_count"),
            F.countDistinct(F.col("target.id")).alias("distinct_target_count"),
        )
        .withColumn("path_length", F.lit(length))
        .select("path_length", "target_state", "path_count", "distinct_target_count")
    )


q22 = (
    q22_route_count_by_state("(source)-[flight*1]->(target)", 1)
    .unionByName(
        q22_route_count_by_state("(source)-[flight*2]->(target)", 2)
    )
    .unionByName(
        q22_route_count_by_state("(source)-[flight*3]->(target)", 3)
    )
    .orderBy("path_length", F.desc("path_count"))
)

Equivalent query in GQL standard:

gql

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(target:Airport)
RETURN length(p) AS path_length,
       target.state AS target_state,
       count(p) AS path_count,
       count(DISTINCT target) AS distinct_target_count
ORDER BY path_length, path_count DESC
LIMIT $limit;

Q23. Airports Serving Two Destination States

Subcategory: T4.7 Union and Intersection

This query finds origin airports that serve both of two selected destination states.

python

# Q23. Airports Serving Two Destination States
q23 = (
    route_graph.find(
        "(origin)-[]->(dest_a); (origin)-[]->(dest_b)"
    )
    .where(
        (F.col("dest_a.state") == "IN")
        & (F.col("dest_b.state") == "IL")
    )
    .select(
        F.col("origin.id").alias("airport_id"),
        F.col("origin.code").alias("code"),
        F.col("origin.city").alias("city"),
        F.col("origin.state").alias("state"),
    )
    .distinct()
    .orderBy("code")
)

Equivalent query in GQL standard:

gql

MATCH (origin:Airport)-[:FLIGHT]->(destA:Airport {state: $stateA})
MATCH (origin)-[:FLIGHT]->(destB:Airport {state: $stateB})
RETURN DISTINCT origin.airport_id AS airport_id, origin.code AS code, origin.city AS city, origin.state AS state
ORDER BY code
LIMIT $limit;

Q24. Top Airports by Degree Centrality

Subcategory: T5.1 Degree Centrality

This query ranks airports by the number of distinct airports they connect to in either direction.

python

# Q24. Top Airports by Degree Centrality
q24 = (
    route_graph.find("(airport)-[]-(neighbor)")
    .groupBy(
        F.col("airport.id").alias("airport_id"),
        F.col("airport.code").alias("code"),
        F.col("airport.city").alias("city"),
    )
    .agg(F.countDistinct(F.col("neighbor.id")).alias("degree"))
    .orderBy(F.desc("degree"))
)

Q25. Airport PageRank

Subcategory: T5.2 PageRank

This query runs PageRank on the airport flight graph using Neo4j Graph Data Science.

python

# Q25. Airport PageRank
q25_pagerank_graph = g.pageRank(resetProbability=0.15, tol=0.001)
q25 = (
    q25_pagerank_graph.vertices
    .select(
        F.col("id").alias("airport_id"),
        "code",
        F.col("pagerank").alias("score"),
    )
    .orderBy(F.desc("score"))
)

Note: Cannot be expressed in standard GQL because Graph Data Science procedures are not part of standard GQL.

Q26. Weakly Connected Airport Components

Subcategory: T5.3 Connected Components / Weakly Connected Components

This query finds weakly connected components after treating flight direction as undirected.

python

# Q26. Weakly Connected Airport Components
q26_membership = g.connectedComponents(
    algorithm="two_phase",
    use_local_checkpoints=True,
    broadcastThreshold=-1,
)
q26 = (
    q26_membership
    .groupBy(F.col("component").alias("component_id"))
    .agg(F.count(F.lit(1)).alias("component_size"))
    .orderBy(F.desc("component_size"))
)

Note: Cannot be expressed in standard GQL because Graph Data Science procedures are not part of standard GQL.

Q27. Same-day Connections

Subcategory: T2.2 Edge Filtering; T4.3 Ungrouped Edge Count and Property Statistics

This query finds same-day two-leg flight connections from LAX to LGA through an intermediate airport, keeps only connections with at least a scheduled one-hour layover, and returns the monthly count for the selected year.

python

# Q27. Same-day Connections
q27 = (
    g.find("(a)-[r1]->(b); (b)-[r2]->(c)")
    .where(
        (F.col("r1.FlightDate") == F.col("r2.FlightDate"))
        & (F.col("r1.CRSArrTime") + 100 <= F.col("r2.CRSDepTime"))
        & (F.col("r1.src") == 12892)
        & (F.col("r2.dst") == 12953)
        & (F.col("r1.Year") == 2020)
    )
    .groupBy(F.col("r1.Month").alias("month"))
    .agg(F.count(F.lit(1)).alias("path_count"))
    .orderBy("month")
)

Equivalent query in GQL standard:

gql

MATCH p = (a:Airport)-[r1:FLIGHT]->(b:Airport)-[r2:FLIGHT]->(c:Airport)
WHERE r1.FlightDate = r2.FlightDate
  AND r1.CRSArrTime + 100 <= r2.CRSDepTime
  AND a.airport_id = 12892
  AND c.airport_id = 12953
  AND r1.Year = $year
WITH r1.Month AS month, count(p) AS path_count
RETURN month, path_count
ORDER BY month;

Q28. Missed Connecting Flights

Subcategory: T2.2 Edge Filtering; T4.3 Ungrouped Edge Count and Property Statistics

This query finds scheduled two-leg LAX-to-LGA connections through an intermediate airport, filters to cases where the first actual arrival is at or after the second actual departure, and returns the monthly missed-connection count for the selected year.

python

# Q28. Missed Connecting Flights
q28 = (
    g.find("(a)-[r1]->(b); (b)-[r2]->(c)")
    .where(
        (F.col("r1.FlightDate") == F.col("r2.FlightDate"))
        & (F.col("r1.CRSArrTime") + 100 <= F.col("r2.CRSDepTime"))
        & (F.col("r1.ArrTime") >= F.col("r2.DepTime"))
        & (F.col("r1.src") == 12892)
        & (F.col("r2.dst") == 12953)
        & (F.col("r1.Year") == 2020)
    )
    .groupBy(F.col("r1.Month").alias("month"))
    .agg(F.count(F.lit(1)).alias("path_count"))
    .orderBy("month")
)

Equivalent query in GQL standard:

gql

MATCH p = (a:Airport)-[r1:FLIGHT]->(b:Airport)-[r2:FLIGHT]->(c:Airport)
WHERE r1.FlightDate = r2.FlightDate
  AND r1.CRSArrTime + 100 <= r2.CRSDepTime
  AND r1.ArrTime >= r2.DepTime
  AND a.airport_id = 12892
  AND c.airport_id = 12953
  AND r1.Year = $year
WITH r1.Month AS month, count(p) AS path_count
RETURN month, path_count
ORDER BY month;

Q29. Top 10 Aircraft Turnarounds

Subcategory: T3.5 Two-hop Join Pattern; T4.3 Ungrouped Edge Count and Property Statistics

This query finds airports where the same aircraft arrives and then departs on the same date, counts distinct tail numbers that form valid scheduled turnarounds, and returns the top 10 airports for the selected date.

python

# Q29. Top 10 Aircraft Turnarounds
q29_target_date = F.lit("2020-12-25").cast("date")
q29_counts = (
    g.find("(a)-[r1]->(n); (n)-[r2]->(c)")
    .where(
        (F.col("r1.FlightDate") == q29_target_date)
        & (F.col("r1.FlightDate") == F.col("r2.FlightDate"))
        & (F.col("r1.Tail_Number") == F.col("r2.Tail_Number"))
        & F.col("r1.Tail_Number").isNotNull()
        & (F.col("r1.Tail_Number") != "")
        & (F.col("r1.CRSArrTime") < F.col("r2.CRSDepTime"))
    )
    .groupBy(
        F.col("n.id").alias("airport_id"),
        F.col("n.city").alias("city"),
        F.col("n.code").alias("code"),
    )
    .agg(F.countDistinct(F.col("r1.Tail_Number")).alias("cnt"))
)
q29 = (
    q29_counts
    .orderBy(F.desc("cnt"))
    .limit(10)
)

Equivalent query in GQL standard:

gql

MATCH (:Airport)-[r1:FLIGHT]->(n:Airport)-[r2:FLIGHT]->(:Airport)
WHERE r1.FlightDate = date($date)
  AND r1.FlightDate = r2.FlightDate
  AND r1.Tail_Number = r2.Tail_Number
  AND r1.Tail_Number IS NOT NULL
  AND r1.Tail_Number <> ""
  AND r1.CRSArrTime < r2.CRSDepTime
WITH n, count(DISTINCT r1.Tail_Number) AS cnt
RETURN n, cnt
ORDER BY cnt DESC
LIMIT 10;

Q30. Three-Leg Flight Sequences

Subcategory: T2.2 Edge Filtering; T4.3 Ungrouped Edge Count and Property Statistics

This query follows three consecutive flight legs on the same date for the same aircraft, enforces scheduled arrival-before-departure ordering between legs, and counts distinct tail numbers that complete the sequence.

python

# Q30. Three-Leg Flight Sequences
q30_target_date = F.lit("2020-12-25").cast("date")
q30 = (
    g.find("(a)-[r1]->(b); (b)-[r2]->(c); (c)-[r3]->(d)")
    .where(
        (F.col("r1.FlightDate") == q30_target_date)
        & (F.col("r1.FlightDate") == F.col("r2.FlightDate"))
        & (F.col("r1.FlightDate") == F.col("r3.FlightDate"))
        & (F.col("r1.Tail_Number") == F.col("r2.Tail_Number"))
        & (F.col("r1.Tail_Number") == F.col("r3.Tail_Number"))
        & F.col("r1.Tail_Number").isNotNull()
        & (F.col("r1.Tail_Number") != "")
        & (F.col("r1.CRSArrTime") < F.col("r2.CRSDepTime"))
        & (F.col("r2.CRSArrTime") < F.col("r3.CRSDepTime"))
    )
    .agg(F.countDistinct(F.col("r1.Tail_Number")).alias("cnt"))
)

Equivalent query in GQL standard:

gql

MATCH (:Airport)-[r1:FLIGHT]->(:Airport)-[r2:FLIGHT]->(:Airport)-[r3:FLIGHT]->(:Airport)
WHERE r1.FlightDate = date($date)
  AND r1.FlightDate = r2.FlightDate
  AND r1.FlightDate = r3.FlightDate
  AND r1.Tail_Number = r2.Tail_Number
  AND r1.Tail_Number = r3.Tail_Number
  AND r1.Tail_Number IS NOT NULL
  AND r1.Tail_Number <> ""
  AND r1.CRSArrTime < r2.CRSDepTime
  AND r2.CRSArrTime < r3.CRSDepTime
RETURN count(DISTINCT r1.Tail_Number) AS cnt;