Neo4j OnTime Implementation

Schema Diagram and Graph Statistics

Node Statistics

Node Label	Count
Airport	379

Edge Statistics

Relationship	Count
FLIGHT	17,412,876

Disk space: 22.07 GiB

Total node import time: 150.82 seconds

Total edge import time: 3230.86 seconds

Data Loading

Overview

Data import is handled in two stages: node creation followed by sequential edge ingestion. We put all the csv files under the data import directory of Neo4j. This directory can be set in the neo4j.conf file using the parameter 'dbms.directories.import'.

Node Import (150.82s)

Nodes are imported by iterating over all CSV files and extracting unique airport information to create Airport nodes. We call a periodic apoc.periodic.iterate procedure to handle this efficiently. This script handles all 36 months' worth of airport data from 2020-2022 together, no per month loading time is available.

cypher

CALL apoc.periodic.iterate(
"
  UNWIND [
    'file:///ontime20-22/2020_1.csv',
    'file:///ontime20-22/2020_2.csv',
    'file:///ontime20-22/2020_3.csv',
    'file:///ontime20-22/2020_4.csv',
    'file:///ontime20-22/2020_5.csv',
    'file:///ontime20-22/2020_6.csv',
    'file:///ontime20-22/2020_7.csv',
    'file:///ontime20-22/2020_8.csv',
    'file:///ontime20-22/2020_9.csv',
    'file:///ontime20-22/2020_10.csv',
    'file:///ontime20-22/2020_11.csv',
    'file:///ontime20-22/2020_12.csv',
    'file:///ontime20-22/2021_1.csv',
    'file:///ontime20-22/2021_2.csv',
    'file:///ontime20-22/2021_3.csv',
    'file:///ontime20-22/2021_4.csv',
    'file:///ontime20-22/2021_5.csv',
    'file:///ontime20-22/2021_6.csv',
    'file:///ontime20-22/2021_7.csv',
    'file:///ontime20-22/2021_8.csv',
    'file:///ontime20-22/2021_9.csv',
    'file:///ontime20-22/2021_10.csv',
    'file:///ontime20-22/2021_11.csv',
    'file:///ontime20-22/2021_12.csv',
    'file:///ontime20-22/2022_1.csv',
    'file:///ontime20-22/2022_2.csv',
    'file:///ontime20-22/2022_3.csv',
    'file:///ontime20-22/2022_4.csv',
    'file:///ontime20-22/2022_5.csv',
    'file:///ontime20-22/2022_6.csv',
    'file:///ontime20-22/2022_7.csv',
    'file:///ontime20-22/2022_8.csv',
    'file:///ontime20-22/2022_9.csv',
    'file:///ontime20-22/2022_10.csv',
    'file:///ontime20-22/2022_11.csv',
    'file:///ontime20-22/2022_12.csv'
  ] AS file
  LOAD CSV WITH HEADERS FROM file AS row
  WITH [
    {
      id: toInteger(row.OriginAirportID),
      code: row.Origin,
      city: row.OriginCityName,
      state: row.OriginState,
      stateName: row.OriginStateName
    },
    {
      id: toInteger(row.DestAirportID),
      code: row.Dest,
      city: row.DestCityName,
      state: row.DestState,
      stateName: row.DestStateName
    }
  ] AS airports
  UNWIND airports AS a
  WITH DISTINCT a.id AS airport_id, a.code AS code, a.city AS city, a.state AS state, a.stateName AS stateName
  RETURN airport_id, code, city, state, stateName
  ",
  "
  MERGE (ap:Airport {airport_id: airport_id})
  SET ap.code = code,
      ap.city = city,
      ap.state = state,
      ap.stateName = stateName
  ",
  {batchSize:1000, parallel:false}
);

Edge Import

For edge imports, since processing all files can take a lot of time, we use a sequential bash script to execute periodic iterates for each monthly CSV file.

bash

#!/bin/bash
YEARS=(2020 2021 2022)
MONTHS=(1 2 3 4 5 6 7 8 9 10 11 12)
for year in "${YEARS[@]}"; do
  for month in "${MONTHS[@]}"; do
    FILE="${year}_${month}.csv"    
    cypher-shell -u neo4j << CYPHER
CALL apoc.periodic.iterate(
  "
  LOAD CSV WITH HEADERS FROM 'file:///ontime20-22/${FILE}' AS row
  RETURN row
  ",
  "
  MATCH (origin:Airport {airport_id: toInteger(row.OriginAirportID)})
  MATCH (dest:Airport {airport_id: toInteger(row.DestAirportID)})
  MERGE (origin)-[f:FLIGHT {
      flightDate: date(row.FlightDate),
      airline: row.IATA_CODE_Reporting_Airline,
      flightNumber: row.Flight_Number_Reporting_Airline
  }]->(dest)
  SET f.Year = toInteger(row.Year),
      f.Quarter = toInteger(row.Quarter),
      f.Month = toInteger(row.Month),
      f.DayofMonth = toInteger(row.DayofMonth),
      f.DayOfWeek = toInteger(row.DayOfWeek),
      f.FlightDate = date(row.FlightDate),
      f.Reporting_Airline = row.Reporting_Airline,
      f.DOT_ID_Reporting_Airline = toInteger(row.DOT_ID_Reporting_Airline),
      f.IATA_CODE_Reporting_Airline = row.IATA_CODE_Reporting_Airline,
      f.Tail_Number = row.Tail_Number,
      f.Flight_Number_Reporting_Airline = row.Flight_Number_Reporting_Airline,
      f.OriginAirportID = toInteger(row.OriginAirportID),
      f.OriginAirportSeqID = toInteger(row.OriginAirportSeqID),
      f.OriginCityMarketID = toInteger(row.OriginCityMarketID),
      f.Origin = row.Origin,
      f.OriginCityName = row.OriginCityName,
      f.OriginState = row.OriginState,
      f.OriginStateFips = row.OriginStateFips,
      f.OriginStateName = row.OriginStateName,
      f.OriginWac = toInteger(row.OriginWac),
      f.DestAirportID = toInteger(row.DestAirportID),
      f.DestAirportSeqID = toInteger(row.DestAirportSeqID),
      f.DestCityMarketID = toInteger(row.DestCityMarketID),
      f.Dest = row.Dest,
      f.DestCityName = row.DestCityName,
      f.DestState = row.DestState,
      f.DestStateFips = row.DestStateFips,
      f.DestStateName = row.DestStateName,
      f.DestWac = toInteger(row.DestWac),
      f.CRSDepTime = toInteger(row.CRSDepTime),
      f.DepTime = toInteger(row.DepTime),
      f.DepDelay = toInteger(row.DepDelay),
      f.DepDelayMinutes = toInteger(row.DepDelayMinutes),
      f.DepDel15 = toInteger(row.DepDel15),
      f.DepartureDelayGroups = row.DepartureDelayGroups,
      f.DepTimeBlk = row.DepTimeBlk,
      f.TaxiOut = toInteger(row.TaxiOut),
      f.WheelsOff = row.WheelsOff,
      f.WheelsOn = row.WheelsOn,
      f.TaxiIn = toInteger(row.TaxiIn),
      f.CRSArrTime = toInteger(row.CRSArrTime),
      f.ArrTime = toInteger(row.ArrTime),
      f.ArrDelay = toInteger(row.ArrDelay),
      f.ArrDelayMinutes = toInteger(row.ArrDelayMinutes),
      f.ArrDel15 = toInteger(row.ArrDel15),
      f.ArrivalDelayGroups = row.ArrivalDelayGroups,
      f.ArrTimeBlk = row.ArrTimeBlk,
      f.Cancelled = toInteger(row.Cancelled),
      f.CancellationCode = row.CancellationCode,
      f.Diverted = toInteger(row.Diverted),
      f.CRSElapsedTime = toInteger(row.CRSElapsedTime),
      f.ActualElapsedTime = toInteger(row.ActualElapsedTime),
      f.AirTime = toInteger(row.AirTime),
      f.Flights = toInteger(row.Flights),
      f.Distance = toInteger(row.Distance),
      f.DistanceGroup = toInteger(row.DistanceGroup),
      f.CarrierDelay = toInteger(row.CarrierDelay),
      f.WeatherDelay = toInteger(row.WeatherDelay),
      f.NASDelay = toInteger(row.NASDelay),
      f.SecurityDelay = toInteger(row.SecurityDelay),
      f.LateAircraftDelay = toInteger(row.LateAircraftDelay),
      f.FirstDepTime = toInteger(row.FirstDepTime),
      f.TotalAddGTime = toInteger(row.TotalAddGTime),
      f.LongestAddGTime = toInteger(row.LongestAddGTime)
  ",
  {batchSize:1000, parallel:false}
) YIELD batches, total, timeTaken, committedOperations, failedOperations
RETURN batches, total, timeTaken/1000.0 AS seconds, committedOperations, failedOperations;
CYPHER
    echo "Completed: ${FILE}"
  done
done

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.

cypher

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;

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.

cypher

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;

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.

cypher

MATCH p = (:Airport {airport_id: $originAirportId})-[f:FLIGHT*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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT*1..3]->(:Airport {airport_id: $targetAirportId})
RETURN count(p) > 0 AS reachable
LIMIT 1;

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.

cypher

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

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.

cypher

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT*1..3]->(:Airport {airport_id: $targetAirportId})
WHERE all(n IN nodes(p) WHERE single(m IN nodes(p) WHERE m = n))
RETURN p, length(p) AS hop_count
ORDER BY hop_count
LIMIT $limit;

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.

cypher

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT*1..3]->(:Airport {airport_id: $targetAirportId})
WHERE all(n IN nodes(p) WHERE single(m IN nodes(p) WHERE m = n))
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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

MATCH (airport:Airport)-[:FLIGHT]-(neighbor:Airport)
RETURN airport.airport_id AS airport_id, airport.code AS code, airport.city AS city,
       count(DISTINCT neighbor) AS degree
ORDER BY degree DESC
LIMIT $limit;

Q25. Airport PageRank

Subcategory: T5.2 PageRank

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

cypher

CALL gds.graph.project(
  'OnTimeAirportGraph',
  'Airport',
  {FLIGHT: {type: 'FLIGHT', orientation: 'NATURAL'}}
);

CALL gds.pageRank.stream('OnTimeAirportGraph')
YIELD nodeId, score
RETURN gds.util.asNode(nodeId).airport_id AS airport_id,
       gds.util.asNode(nodeId).code AS code,
       score
ORDER BY score DESC
LIMIT $limit;

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.

cypher

CALL gds.graph.project(
  'OnTimeAirportUndirectedGraph',
  'Airport',
  {FLIGHT: {type: 'FLIGHT', orientation: 'UNDIRECTED'}}
);

CALL gds.wcc.stream('OnTimeAirportUndirectedGraph')
YIELD nodeId, componentId
RETURN componentId, count(*) AS component_size
ORDER BY component_size DESC
LIMIT $limit;

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.

cypher

MATCH p = (a)-[r1]->(b)-[r2]->(c)
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

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.

cypher

MATCH p = (a)-[r1]->(b)-[r2]->(c)
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

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.

cypher

MATCH ()-[r1]->(n)-[r2]->()
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

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.

cypher

MATCH ()-[r1]->()-[r2]->()-[r3]->()
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

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;