TigerGraph OnTime Implementation

Schema Diagram and Graph Statistics

Node Statistics

Node Label	Count
Airport	379

Edge Statistics

Relationship	Count
FLIGHT	17,412,876

Data Loading

Overview

Data import is handled in two stages. First we need to define our vertex and edge schemas, then we create a graph schema using the Airport vertex and Flight edge we created. The second step is to define a loading job to import the CSV files into the graph database.

Define Node

We use the CREATE VERTEX statement to define the Airport vertex type.

gsql

CREATE VERTEX Airport (
    PRIMARY_ID id INT,
    city STRING,
    code STRING,
    state STRING
) WITH primary_id_as_attribute="true"

Define Edge

We use the CREATE DIRECTED EDGE statement to define the Flight edge type. The DISCRIMINATOR here is important. Without a discriminator, TigerGraph typically treats multiple attempts to create an edge between the same pair of nodes (like Airport A and Airport B) as an update to the existing edge rather than a new record. Here since we might have more than one flight records between two airports, this DISCRIMINATOR is necessary.

gsql

CREATE DIRECTED EDGE Flight (
	FROM Airport,
	TO Airport,

	DISCRIMINATOR(
		FlightDate DATETIME,
		Reporting_Airline STRING,
		Flight_Number_Reporting_Airline STRING
	),

	Year INT,
	Quarter INT,
	Month INT,
	DayofMonth INT,

	IATA_CODE_Reporting_Airline STRING,
	DOT_ID_Reporting_Airline INT,
	Tail_Number STRING,

	OriginAirportSeqID INT,
	DestAirportSeqID INT,

	CRSDepTime STRING,
	DepTime STRING,
	DepDelay DOUBLE,
	DepDelayMinutes DOUBLE,
	DepDel15 DOUBLE,
	DepartureDelayGroups INT,
	DepTimeBlk STRING,
	TaxiOut DOUBLE,
	WheelsOff STRING,

	CRSArrTime STRING,
	ArrTime STRING,
	ArrDelay DOUBLE,
	ArrDelayMinutes DOUBLE,
	ArrDel15 DOUBLE,
	ArrivalDelayGroups INT,
	ArrTimeBlk STRING,
	TaxiIn DOUBLE,
	WheelsOn STRING,

	Cancelled DOUBLE,
	CancellationCode STRING,
	Diverted DOUBLE,

	CRSElapsedTime DOUBLE,
	ActualElapsedTime DOUBLE,
	AirTime DOUBLE,
	Flights DOUBLE,
	Distance DOUBLE,
	DistanceGroup INT,

	CarrierDelay DOUBLE,
	WeatherDelay DOUBLE,
	NASDelay DOUBLE,
	SecurityDelay DOUBLE,
	LateAircraftDelay DOUBLE
);

Define Graph

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

gsql

CREATE GRAPH ontime (Airport, Flight)

In practice, we put all three CREATE clauses together, and create the schema all together.

Import Job

In this step, we define a import job query to upload data from raw CSV files to TigerGraph. We use a bash script file to upload the data file by file, by setting the f1 parameter. Below is the import job definition for one of the CSV files (2020_1.csv). The same logic is applied to all other files. We also provide the script file.

gsql

CREATE LOADING JOB load_ontime FOR GRAPH ontime {
    DEFINE FILENAME flights = "/datasets/prop_datasets/ontime20-22/2020_1.csv";

    /* Origin airport */
    LOAD flights TO VERTEX Airport VALUES (
        $"OriginAirportID",
        $"OriginCityName",
        $"Origin",
        $"OriginState"
    ) USING
        HEADER="true",
        SEPARATOR=",",
        QUOTE="double";

    /* Destination airport */
    LOAD flights TO VERTEX Airport VALUES (
        $"DestAirportID",
        $"DestCityName",
        $"Dest",
        $"DestState"
    ) USING
        HEADER="true",
        SEPARATOR=",",
        QUOTE="double";

    /* Flight edge */
    LOAD flights TO EDGE Flight VALUES (
        /* Endpoints */
        $"OriginAirportID",
        $"DestAirportID",

        /* Discriminator attributes */
        $"FlightDate",
        $"Reporting_Airline",
        $"Flight_Number_Reporting_Airline",

        /* Ordinary attributes */
        $"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"
    ) USING
        HEADER="true",
        SEPARATOR=",",
        QUOTE="double";
}

bash

#!/usr/bin/env bash
set -euo pipefail

DATA_DIR="/datasets/prop_datasets/ontime20-22"

for year in 2020 2021 2022; do
    for month in {1..12}; do
        csv_file="${DATA_DIR}/${year}_${month}.csv"

        if [[ ! -f "${csv_file}" ]]; then
            echo "ERROR: Missing ${csv_file}" >&2
            exit 1
        fi

        echo "Loading ${csv_file}"

        gsql -g ontime \
          "RUN LOADING JOB load_ontime USING flights=\"${csv_file}\""
    done
done

echo "All 36 CSV files loaded."

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.

gsql

/* Q1. Direct Destinations from an Airport */
CREATE OR REPLACE DISTRIBUTED QUERY Q1() FOR GRAPH ontime SYNTAX v2 {
  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.code == "IND";

  Result = SELECT t
           FROM Source:s -(Flight>:e)- Airport:t
           WHERE e.Year == 2020
           ORDER BY t.code;

  PRINT Result[Result.id AS airport_id,
               Result.code AS code,
               Result.city AS city,
               Result.state AS state];
}

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.

gsql

/* Q2. Airports Reachable within K Hops */
CREATE OR REPLACE DISTRIBUTED QUERY Q2() FOR GRAPH ontime SYNTAX v2 {
  MinAccum<INT> @hop_count;

  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.code == "IND";

  Hop1 = SELECT t
         FROM Source:s -(Flight>:e1)- Airport:t
         WHERE e1.Year == 2020
         ACCUM t.@hop_count += 1;

  Hop2 = SELECT t
         FROM Hop1:s -(Flight>:e2)- Airport:t
         WHERE e2.Year == 2020
         ACCUM t.@hop_count += 2;

  Hop3 = SELECT t
         FROM Hop2:s -(Flight>:e3)- Airport:t
         WHERE e3.Year == 2020
         ACCUM t.@hop_count += 3;

  Reachable = Hop1 UNION Hop2 UNION Hop3;
  Result = SELECT t FROM Reachable:t
           ORDER BY t.@hop_count, t.code;

  PRINT Result[Result.id AS airport_id,
               Result.code AS code,
               Result.city AS city,
               Result.@hop_count AS hop_count];
}

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.

gsql

/* Q3. Airports Exactly Three Flights Away */
CREATE OR REPLACE DISTRIBUTED QUERY Q3() FOR GRAPH ontime SYNTAX v2 {
  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.code == "IND";

  Hop1 = SELECT t
         FROM Source:s -(Flight>:e)- Airport:t
         WHERE e.Year == 2020;

  Hop2 = SELECT t
         FROM Hop1:s -(Flight>:e)- Airport:t
         WHERE e.Year == 2020;

  Hop3 = SELECT t
         FROM Hop2:s -(Flight>:e)- Airport:t
         WHERE e.Year == 2020
         ORDER BY t.code;

  PRINT Hop3[Hop3.id AS airport_id,
             Hop3.code AS code,
             Hop3.city AS city,
             3 AS hop_count];
}

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.

gsql

/* Q4. Delayed One-Hop Destinations */
CREATE OR REPLACE DISTRIBUTED QUERY Q4() FOR GRAPH ontime SYNTAX v2 {
  SELECT t.id AS airport_id,
         t.code AS code,
         e.FlightDate AS flight_date,
         e.IATA_CODE_Reporting_Airline AS airline,
         e.DepDelayMinutes AS departure_delay
  INTO Result
  FROM Airport:s -(Flight>:e)- Airport:t
  WHERE s.code == "IND"
    AND e.Year == 2020
    AND e.DepDelayMinutes >= 120
  ORDER BY departure_delay DESC;

  PRINT Result;
}

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.

gsql

/* Q5. Airports by State and Code Prefix */
CREATE OR REPLACE DISTRIBUTED QUERY Q5() FOR GRAPH ontime SYNTAX v2 {
  SELECT DISTINCT a.id AS airport_id,
                  a.code AS code,
                  a.city AS city,
                  a.state AS state_name
  INTO Result
  FROM Airport:a
  WHERE a.state == "CA"
    AND a.code LIKE "S%"
  ORDER BY a.code;

  PRINT Result;
}

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.

gsql

/* Q6. Long Non-Cancelled Flights */
CREATE OR REPLACE DISTRIBUTED QUERY Q6() FOR GRAPH ontime SYNTAX v2 {
  SELECT s.code AS origin,
         t.code AS dest,
         e.FlightDate AS flight_date,
         e.IATA_CODE_Reporting_Airline AS airline,
         e.Distance AS distance
  INTO Result
  FROM Airport:s -(Flight>:e)- Airport:t
  WHERE e.Year == 2020
    AND e.Cancelled == 0
    AND e.Distance >= 2000
  ORDER BY distance DESC;

  PRINT Result;
}

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.

gsql

/* Q7. Reachability between Two Airports */
CREATE OR REPLACE DISTRIBUTED QUERY Q7() FOR GRAPH ontime SYNTAX v2 {
  OrAccum @@reachable;

  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.code == "IND";

  Hop1 = SELECT t
         FROM Source:s -(Flight>:e)- Airport:t
         ACCUM IF t.code == "JFK" THEN @@reachable += TRUE END;

  IF NOT @@reachable THEN
    Hop2 = SELECT t
           FROM Hop1:s -(Flight>:e)- Airport:t
           ACCUM IF t.code == "JFK" THEN @@reachable += TRUE END;
  END;

  IF NOT @@reachable THEN
    Hop3 = SELECT t
           FROM Hop2:s -(Flight>:e)- Airport:t
           ACCUM IF t.code == "JFK" THEN @@reachable += TRUE END;
  END;

  PRINT @@reachable AS 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.

gsql

/* Q8. Shortest Airport Route, bounded to four hops */
CREATE OR REPLACE DISTRIBUTED QUERY Q8() FOR GRAPH ontime SYNTAX v2 {
  MinAccum<INT> @distance;
  SetAccum<STRING> @paths;
  INT hop = 0;

  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.code == "IND"
           POST-ACCUM s.@distance += 0,
                      s.@paths += s.code;

  Frontier = Source;
  WHILE Frontier.size() > 0 AND hop < 4 DO
    hop = hop + 1;

    NextHop = SELECT t
              FROM Frontier:s -(Flight>:e)- Airport:t
              WHERE t.@distance > hop
              ACCUM t.@distance += hop,
                    FOREACH path IN s.@paths DO
                      t.@paths += path + "->" + t.code
                    END;

    Frontier = SELECT t FROM NextHop:t
               WHERE t.@distance == hop;
  END;

  TargetResult = SELECT t FROM Start:t
                 WHERE t.code == "JFK" AND t.@distance <= 4;

  PRINT TargetResult[TargetResult.code AS destination,
                     TargetResult.@distance AS hop_count,
                     TargetResult.@paths AS paths];
}

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.

gsql

/* Q9. Simple Routes up to Three Legs */
CREATE OR REPLACE DISTRIBUTED QUERY Q9() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<INT a_id, INT b_id, STRING path> Path1;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id, STRING path> Path2;
  TYPEDEF TUPLE<STRING path, INT hop_count> Route;

  SetAccum<Path1> @path1;
  SetAccum<Path2> @path2;
  SetAccum<Route> @@routes;

  All = {Airport.*};
  Source = SELECT s FROM All:s
           WHERE s.code == "IND";

  Hop1 = SELECT b
         FROM Source:a -(Flight>:e)- Airport:b
         WHERE a.id != b.id
         ACCUM b.@path1 += Path1(a.id, b.id, a.code + "->" + b.code),
               IF b.code == "JFK" THEN
                 @@routes += Route(a.code + "->" + b.code, 1)
               END;

  Hop2 = SELECT c
         FROM Hop1:b -(Flight>:e)- Airport:c
         ACCUM
           FOREACH p IN b.@path1 DO
             IF p.a_id != c.id AND p.b_id != c.id THEN
               c.@path2 += Path2(p.a_id, p.b_id, c.id,
                                  p.path + "->" + c.code),
               IF c.code == "JFK" THEN
                 @@routes += Route(p.path + "->" + c.code, 2)
               END
             END
           END;

  Hop3 = SELECT d
         FROM Hop2:c -(Flight>:e)- Airport:d
         ACCUM
           FOREACH p IN c.@path2 DO
             IF p.a_id != d.id AND p.b_id != d.id AND p.c_id != d.id
                AND d.code == "JFK" THEN
               @@routes += Route(p.path + "->" + d.code, 3)
             END
           END;

  PRINT @@routes AS routes;
}

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.

gsql

/* Q10. Top Weighted Routes by Distance */
CREATE OR REPLACE DISTRIBUTED QUERY Q10() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<INT a_id, INT b_id, STRING path,
                DOUBLE total_distance> WeightedPath1;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id, STRING path,
                DOUBLE total_distance> WeightedPath2;
  TYPEDEF TUPLE<STRING path, INT hop_count,
                DOUBLE total_distance> WeightedRoute;

  SetAccum<WeightedPath1> @path1;
  SetAccum<WeightedPath2> @path2;
  SetAccum<WeightedRoute> @@routes;

  All = {Airport.*};
  Source = SELECT s FROM All:s
           WHERE s.code == "IND";

  Hop1 = SELECT b
         FROM Source:a -(Flight>:e)- Airport:b
         WHERE a.id != b.id
         ACCUM b.@path1 += WeightedPath1(a.id, b.id,
                                          a.code + "->" + b.code,
                                          e.Distance),
               IF b.code == "JFK" THEN
                 @@routes += WeightedRoute(a.code + "->" + b.code,
                                            1, e.Distance)
               END;

  Hop2 = SELECT c
         FROM Hop1:b -(Flight>:e)- Airport:c
         ACCUM
           FOREACH p IN b.@path1 DO
             IF p.a_id != c.id AND p.b_id != c.id THEN
               c.@path2 += WeightedPath2(p.a_id, p.b_id, c.id,
                                          p.path + "->" + c.code,
                                          p.total_distance + e.Distance),
               IF c.code == "JFK" THEN
                 @@routes += WeightedRoute(p.path + "->" + c.code,
                                            2,
                                            p.total_distance + e.Distance)
               END
             END
           END;

  Hop3 = SELECT d
         FROM Hop2:c -(Flight>:e)- Airport:d
         ACCUM
           FOREACH p IN c.@path2 DO
             IF p.a_id != d.id AND p.b_id != d.id AND p.c_id != d.id
                AND d.code == "JFK" THEN
               @@routes += WeightedRoute(p.path + "->" + d.code,
                                          3,
                                          p.total_distance + e.Distance)
             END
           END;

  PRINT @@routes AS weighted_routes;
}

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.

gsql

/* Q11. Directed Triangle of Airports */
CREATE OR REPLACE DISTRIBUTED QUERY Q11() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<INT a_id, STRING a_code,
                INT b_id, STRING b_code> DirectedPair;
  TYPEDEF TUPLE<STRING airport_a,
                STRING airport_b,
                STRING airport_c> TriangleRow;

  SetAccum<INT> @out_neighbor_ids;
  SetAccum<DirectedPair> @prefixes;
  SetAccum<TriangleRow> @@triangles;

  All = {Airport.*};
  BuildTopology = SELECT a
                  FROM All:a -(Flight>:e)- Airport:b
                  ACCUM a.@out_neighbor_ids += b.id;

  Middles = SELECT b
            FROM All:a -(Flight>:e)- Airport:b
            WHERE a.id < b.id
            ACCUM b.@prefixes += DirectedPair(a.id, a.code,
                                               b.id, b.code);

  CloseTriangles = SELECT c
                   FROM Middles:b -(Flight>:e)- Airport:c
                   ACCUM
                     FOREACH prefix IN b.@prefixes DO
                       IF prefix.b_id < c.id
                          AND c.@out_neighbor_ids.contains(prefix.a_id) THEN
                         @@triangles += TriangleRow(prefix.a_code,
                                                     prefix.b_code,
                                                     c.code)
                       END
                     END;

  PRINT @@triangles AS triangles;
}

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.

gsql

/* Q12. Four-Airport Cycle */
CREATE OR REPLACE DISTRIBUTED QUERY Q12() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<INT a_id, INT b_id> Path2;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id> Path3;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id, INT d_id> Cycle4;

  SetAccum<INT> @out_neighbor_ids;
  SetAccum<Path2> @path2;
  SetAccum<Path3> @path3;
  SetAccum<Cycle4> @cycles;
  SumAccum<UINT> @@cycle_count;

  All = {Airport.*};
  BuildTopology = SELECT a
                  FROM All:a -(Flight>:e)- Airport:b
                  ACCUM a.@out_neighbor_ids += b.id;

  Hop1 = SELECT b
         FROM All:a -(Flight>:e)- Airport:b
         WHERE a.id != b.id
         ACCUM b.@path2 += Path2(a.id, b.id);

  Hop2 = SELECT c
         FROM Hop1:b -(Flight>:e)- Airport:c
         ACCUM
           FOREACH p IN b.@path2 DO
             IF p.a_id != c.id AND p.b_id != c.id THEN
               c.@path3 += Path3(p.a_id, p.b_id, c.id)
             END
           END;

  Closed = SELECT d
           FROM Hop2:c -(Flight>:e)- Airport:d
           ACCUM
             FOREACH p IN c.@path3 DO
               IF p.a_id != d.id AND p.b_id != d.id
                  AND p.c_id != d.id
                  AND d.@out_neighbor_ids.contains(p.a_id) THEN
                 d.@cycles += Cycle4(p.a_id, p.b_id, p.c_id, d.id)
               END
             END
           POST-ACCUM @@cycle_count += d.@cycles.size();

  PRINT @@cycle_count AS four_airport_cycle_count;
}

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.

gsql

/* Q13. Hub Airports by Distinct Destinations */
CREATE OR REPLACE DISTRIBUTED QUERY Q13() FOR GRAPH ontime SYNTAX v2 {
  SetAccum<INT> @destinations;

  All = {Airport.*};
  CountDestinations = SELECT s
                      FROM All:s -(Flight>:e)- Airport:t
                      WHERE e.Year == 2020
                      ACCUM s.@destinations += t.id;

  Hubs = SELECT s FROM All:s
         WHERE s.@destinations.size() >= 190
         ORDER BY s.@destinations.size() DESC;

  PRINT Hubs[Hubs.id AS airport_id,
             Hubs.code AS code,
             Hubs.city AS city,
             Hubs.@destinations.size() AS 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.

gsql

/* Q14. Four-Airport Clique */
CREATE OR REPLACE DISTRIBUTED QUERY Q14() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<INT left_id, INT right_id> UndirectedLink;
  TYPEDEF TUPLE<INT a_id, INT b_id> CliquePair;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id> CliqueTriple;

  SetAccum<VERTEX<Airport>> @neighbors;
  SetAccum<CliquePair> @pairs;
  SetAccum<CliqueTriple> @triples;
  SetAccum<VERTEX<Airport>> @@pair_vertices;
  SetAccum<VERTEX<Airport>> @@triple_vertices;
  SetAccum<UndirectedLink> @@links;
  SumAccum<UINT> @@clique_count;

  All = {Airport.*};
  BuildTopology = SELECT a
                  FROM All:a -(Flight>:e)- Airport:b
                  ACCUM a.@neighbors += b,
                        b.@neighbors += a,
                        @@links += UndirectedLink(a.id, b.id),
                        @@links += UndirectedLink(b.id, a.id);

  BuildPairs = SELECT a FROM All:a
               ACCUM
                 FOREACH b IN a.@neighbors DO
                   IF a.id < b.id THEN
                     b.@pairs += CliquePair(a.id, b.id),
                     @@pair_vertices += b
                   END
                 END;

  PairEnds = {@@pair_vertices};
  BuildTriples = SELECT b FROM PairEnds:b
                 ACCUM
                   FOREACH c IN b.@neighbors DO
                     FOREACH p IN b.@pairs DO
                       IF p.b_id < c.id
                          AND @@links.contains(UndirectedLink(p.a_id, c.id)) THEN
                         c.@triples += CliqueTriple(p.a_id, p.b_id, c.id),
                         @@triple_vertices += c
                       END
                     END
                   END;

  TripleEnds = {@@triple_vertices};
  CloseCliques = SELECT c FROM TripleEnds:c
                 ACCUM
                   FOREACH d IN c.@neighbors DO
                     FOREACH p IN c.@triples DO
                       IF p.c_id < d.id
                          AND @@links.contains(UndirectedLink(p.a_id, d.id))
                          AND @@links.contains(UndirectedLink(p.b_id, d.id)) THEN
                         @@clique_count += 1
                       END
                     END
                   END;

  PRINT @@clique_count AS four_airport_clique_count;
}

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.

gsql

/* Q15. Two-Hop Airport Connections */
CREATE OR REPLACE DISTRIBUTED QUERY Q15() FOR GRAPH ontime SYNTAX v2 {
  SumAccum<UINT> @first_leg_count;
  MapAccum<STRING, SumAccum<UINT>> @@connection_counts;

  All = {Airport.*};
  Source = SELECT source FROM All:source
           WHERE source.code == "IND";

  Intermediate = SELECT middle
                 FROM Source:source -(Flight>:first)- Airport:middle
                 WHERE first.Year == 2020
                 ACCUM middle.@first_leg_count += 1;

  CountConnections = SELECT middle
                     FROM Intermediate:middle -(Flight>:second)- Airport:dest
                     WHERE second.Year == 2020
                       AND dest.code != "IND"
                     ACCUM @@connection_counts +=
                             ((middle.code + "->" + dest.code)
                              -> middle.@first_leg_count);

  PRINT @@connection_counts AS connection_counts;
}

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.

gsql

/* Q16. Three-Hop Line Pattern */
CREATE OR REPLACE DISTRIBUTED QUERY Q16() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<INT a_id, INT b_id> Path2;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id> Path3;
  TYPEDEF TUPLE<INT a_id, INT b_id, INT c_id, INT d_id> Path4;

  SetAccum<Path2> @path2;
  SetAccum<Path3> @path3;
  SetAccum<Path4> @path4;
  SumAccum<UINT> @@path_count;

  All = {Airport.*};
  Hop1 = SELECT b
         FROM All:a -(Flight>:e)- Airport:b
         WHERE a.id != b.id
         ACCUM b.@path2 += Path2(a.id, b.id);

  Hop2 = SELECT c
         FROM Hop1:b -(Flight>:e)- Airport:c
         ACCUM
           FOREACH p IN b.@path2 DO
             IF p.a_id != c.id AND p.b_id != c.id THEN
               c.@path3 += Path3(p.a_id, p.b_id, c.id)
             END
           END;

  Hop3 = SELECT d
         FROM Hop2:c -(Flight>:e)- Airport:d
         ACCUM
           FOREACH p IN c.@path3 DO
             IF p.a_id != d.id AND p.b_id != d.id AND p.c_id != d.id THEN
               d.@path4 += Path4(p.a_id, p.b_id, p.c_id, d.id)
             END
           END
         POST-ACCUM @@path_count += d.@path4.size();

  PRINT @@path_count AS three_hop_simple_path_count;
}

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.

gsql

/* Q17. Airport Degree Summary */
CREATE OR REPLACE DISTRIBUTED QUERY Q17() FOR GRAPH ontime SYNTAX v2 {
  SetAccum<INT> @destinations;
  SumAccum<UINT> @@airport_count;
  SumAccum<UINT> @@out_degree_sum;
  MinAccum<INT> @@min_out_degree;
  MaxAccum<INT> @@max_out_degree;

  All = {Airport.*};
  Degrees = SELECT s
            FROM All:s -(Flight>:e)- Airport:t
            ACCUM s.@destinations += t.id;

  Stats = SELECT s FROM All:s
          ACCUM @@airport_count += 1,
                @@out_degree_sum += s.@destinations.size(),
                @@min_out_degree += s.@destinations.size(),
                @@max_out_degree += s.@destinations.size();

  PRINT @@airport_count AS airport_count,
        (1.0 * @@out_degree_sum / @@airport_count) 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.

gsql

/* Q18. State-Level Degree Summary */
CREATE OR REPLACE DISTRIBUTED QUERY Q18() FOR GRAPH ontime SYNTAX v2 {
  SetAccum<INT> @destinations;
  MapAccum<STRING, SumAccum<UINT>> @@airport_counts;
  MapAccum<STRING, AvgAccum> @@avg_out_degrees;
  MapAccum<STRING, MinAccum<INT>> @@min_out_degrees;
  MapAccum<STRING, MaxAccum<INT>> @@max_out_degrees;

  All = {Airport.*};
  Degrees = SELECT s
            FROM All:s -(Flight>:e)- Airport:t
            ACCUM s.@destinations += t.id;

  Stats = SELECT s FROM All:s
          ACCUM @@airport_counts += (s.state -> 1),
                @@avg_out_degrees += (s.state -> s.@destinations.size()),
                @@min_out_degrees += (s.state -> s.@destinations.size()),
                @@max_out_degrees += (s.state -> s.@destinations.size());

  PRINT @@airport_counts AS airport_counts,
        @@avg_out_degrees AS avg_out_degrees,
        @@min_out_degrees AS min_out_degrees,
        @@max_out_degrees AS max_out_degrees;
}

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.

gsql

/* Q19. Flight Edge Distance Summary */
CREATE OR REPLACE DISTRIBUTED QUERY Q19() FOR GRAPH ontime SYNTAX v2 {
  SumAccum<UINT> @@flight_count;
  SumAccum<DOUBLE> @@distance_sum;
  MinAccum<DOUBLE> @@min_distance;
  MaxAccum<DOUBLE> @@max_distance;
  SumAccum<DOUBLE> @@arrival_delay_sum;

  All = {Airport.*};
  Stats = SELECT s
          FROM All:s -(Flight>:e)- Airport:t
          WHERE e.Year == 2020
          ACCUM @@flight_count += 1,
                @@distance_sum += e.Distance,
                @@min_distance += e.Distance,
                @@max_distance += e.Distance,
                @@arrival_delay_sum += e.ArrDelayMinutes;

  PRINT @@flight_count AS flight_count,
        (@@distance_sum / @@flight_count) AS avg_distance,
        @@min_distance AS min_distance,
        @@max_distance AS max_distance,
        (@@arrival_delay_sum / @@flight_count) 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.

gsql

/* Q20. State Pair Flight Statistics */
CREATE OR REPLACE DISTRIBUTED QUERY Q20() FOR GRAPH ontime SYNTAX v2 {
  MapAccum<STRING, SumAccum<UINT>> @@flight_counts;
  MapAccum<STRING, AvgAccum> @@avg_distances;
  MapAccum<STRING, AvgAccum> @@avg_arrival_delays;

  All = {Airport.*};
  Stats = SELECT s
          FROM All:s -(Flight>:e)- Airport:t
          WHERE e.Year == 2020
          ACCUM @@flight_counts +=
                  ((s.state + "->" + t.state) -> 1),
                @@avg_distances +=
                  ((s.state + "->" + t.state) -> e.Distance),
                @@avg_arrival_delays +=
                  ((s.state + "->" + t.state) -> e.ArrDelayMinutes);

  PRINT @@flight_counts AS flight_counts,
        @@avg_distances AS avg_distances,
        @@avg_arrival_delays AS avg_arrival_delays;
}

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.

gsql

/* Q21. Route Counts by Path Length */
CREATE OR REPLACE DISTRIBUTED QUERY Q21() FOR GRAPH ontime SYNTAX v2 {
  SumAccum<UINT> @paths0;
  SumAccum<UINT> @paths1;
  SumAccum<UINT> @paths2;
  SumAccum<UINT> @paths3;
  SumAccum<UINT> @@path_count_1;
  SumAccum<UINT> @@path_count_2;
  SumAccum<UINT> @@path_count_3;

  All = {Airport.*};
  Source = SELECT s FROM All:s
           WHERE s.code == "IND"
           POST-ACCUM s.@paths0 += 1;

  Hop1 = SELECT t
         FROM Source:s -(Flight>:e)- Airport:t
         ACCUM t.@paths1 += s.@paths0;
  Stats1 = SELECT t FROM Hop1:t
           ACCUM @@path_count_1 += t.@paths1;

  Hop2 = SELECT t
         FROM Hop1:s -(Flight>:e)- Airport:t
         ACCUM t.@paths2 += s.@paths1;
  Stats2 = SELECT t FROM Hop2:t
           ACCUM @@path_count_2 += t.@paths2;

  Hop3 = SELECT t
         FROM Hop2:s -(Flight>:e)- Airport:t
         ACCUM t.@paths3 += s.@paths2;
  Stats3 = SELECT t FROM Hop3:t
           ACCUM @@path_count_3 += t.@paths3;

  PRINT @@path_count_1 AS path_count_1,
        Hop1.size() AS distinct_target_count_1,
        @@path_count_2 AS path_count_2,
        Hop2.size() AS distinct_target_count_2,
        @@path_count_3 AS path_count_3,
        Hop3.size() AS distinct_target_count_3;
}

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.

gsql

/* Q22. Route Counts by Length and Target State */
CREATE OR REPLACE DISTRIBUTED QUERY Q22() FOR GRAPH ontime SYNTAX v2 {
  SumAccum<UINT> @paths0;
  SumAccum<UINT> @paths1;
  SumAccum<UINT> @paths2;
  SumAccum<UINT> @paths3;
  MapAccum<STRING, SumAccum<UINT>> @@path_counts_1;
  MapAccum<STRING, SumAccum<UINT>> @@path_counts_2;
  MapAccum<STRING, SumAccum<UINT>> @@path_counts_3;
  MapAccum<STRING, SumAccum<UINT>> @@target_counts_1;
  MapAccum<STRING, SumAccum<UINT>> @@target_counts_2;
  MapAccum<STRING, SumAccum<UINT>> @@target_counts_3;

  All = {Airport.*};
  Source = SELECT s FROM All:s
           WHERE s.code == "IND"
           POST-ACCUM s.@paths0 += 1;

  Hop1 = SELECT t
         FROM Source:s -(Flight>:e)- Airport:t
         ACCUM t.@paths1 += s.@paths0;
  Stats1 = SELECT t FROM Hop1:t
           ACCUM @@path_counts_1 += (t.state -> t.@paths1),
                 @@target_counts_1 += (t.state -> 1);

  Hop2 = SELECT t
         FROM Hop1:s -(Flight>:e)- Airport:t
         ACCUM t.@paths2 += s.@paths1;
  Stats2 = SELECT t FROM Hop2:t
           ACCUM @@path_counts_2 += (t.state -> t.@paths2),
                 @@target_counts_2 += (t.state -> 1);

  Hop3 = SELECT t
         FROM Hop2:s -(Flight>:e)- Airport:t
         ACCUM t.@paths3 += s.@paths2;
  Stats3 = SELECT t FROM Hop3:t
           ACCUM @@path_counts_3 += (t.state -> t.@paths3),
                 @@target_counts_3 += (t.state -> 1);

  PRINT @@path_counts_1 AS path_counts_1,
        @@target_counts_1 AS distinct_target_counts_1,
        @@path_counts_2 AS path_counts_2,
        @@target_counts_2 AS distinct_target_counts_2,
        @@path_counts_3 AS path_counts_3,
        @@target_counts_3 AS distinct_target_counts_3;
}

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.

gsql

/* Q23. Airports Serving Two Destination States */
CREATE OR REPLACE DISTRIBUTED QUERY Q23() FOR GRAPH ontime SYNTAX v2 {
  OrAccum @serves_in;
  OrAccum @serves_il;

  All = {Airport.*};
  MarkStates = SELECT origin
               FROM All:origin -(Flight>:e)- Airport:dest
               ACCUM IF dest.state == "IN" THEN origin.@serves_in += TRUE END,
                     IF dest.state == "IL" THEN origin.@serves_il += TRUE END;

  Matches = SELECT origin FROM All:origin
            WHERE origin.@serves_in AND origin.@serves_il
            ORDER BY origin.code;

  PRINT Matches[Matches.id AS airport_id,
                Matches.code AS code,
                Matches.city AS city,
                Matches.state AS state];
}

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.

gsql

/* Q24. Top Airports by Degree Centrality */
CREATE OR REPLACE DISTRIBUTED QUERY Q24() FOR GRAPH ontime SYNTAX v2 {
  SetAccum<INT> @neighbors;

  All = {Airport.*};
  BuildUndirectedTopology = SELECT a
                            FROM All:a -(Flight>:e)- Airport:b
                            ACCUM a.@neighbors += b.id,
                                  b.@neighbors += a.id;

  Scores = SELECT a FROM All:a
           ORDER BY a.@neighbors.size() DESC;

  PRINT Scores[Scores.id AS airport_id,
               Scores.code AS code,
               Scores.city AS city,
               Scores.@neighbors.size() AS degree];
}

Q25. Airport PageRank

Subcategory: T5.2 PageRank

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

gsql

/* Q25. Airport PageRank
   Requires: IMPORT PACKAGE GDBMS_ALGO */
CALL GDBMS_ALGO.centrality.pagerank(
  "Airport", "Flight", 0.001, 25, 0.85, 379, TRUE, "", "", FALSE
)

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.

gsql

/* Q26. Weakly Connected Airport Components
   Requires: IMPORT PACKAGE GDBMS_ALGO */
CALL GDBMS_ALGO.community.wcc(
  ["Airport"], ["Flight"], 0, TRUE, "", ""
)

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.

gsql

/* Q27. Same-day Connections */
CREATE OR REPLACE DISTRIBUTED QUERY Q27() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<DATETIME flight_date,
                INT arr_time,
                INT month> FlightInfo;

  ListAccum<FlightInfo> @r1_list;
  MapAccum<INT, INT> @@monthly_counts;

  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.id == 12892;

  Intermediate = SELECT t
                 FROM Source:s -(Flight>:e)- Airport:t
                 WHERE e.Year == 2020
                 ACCUM t.@r1_list += FlightInfo(e.FlightDate,
                                                str_to_int(e.CRSArrTime),
                                                e.Month);

  Dest = SELECT t
         FROM Intermediate:s -(Flight>:e)- Airport:t
         WHERE e.Year == 2020
           AND t.id == 12953
         ACCUM
           FOREACH r1 IN s.@r1_list DO
             IF r1.flight_date == e.FlightDate
                AND r1.arr_time + 100 <= str_to_int(e.CRSDepTime) THEN
               @@monthly_counts += (r1.month -> 1)
             END
           END;

  PRINT @@monthly_counts AS monthly_stats;
}

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.

gsql

/* Q28. Missed Connecting Flights */
CREATE OR REPLACE DISTRIBUTED QUERY Q28() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<DATETIME flight_date,
                INT sched_arr_time,
                INT actual_arr_time,
                INT month> FlightInfo;

  ListAccum<FlightInfo> @r1_list;
  MapAccum<INT, INT> @@monthly_counts;

  Start = {Airport.*};
  Source = SELECT s FROM Start:s
           WHERE s.id == 12892;

  Intermediate = SELECT t
                 FROM Source:s -(Flight>:e)- Airport:t
                 WHERE e.Year == 2020
                 ACCUM t.@r1_list += FlightInfo(e.FlightDate,
                                                str_to_int(e.CRSArrTime),
                                                str_to_int(e.ArrTime),
                                                e.Month);

  Dest = SELECT t
         FROM Intermediate:s -(Flight>:e)- Airport:t
         WHERE e.Year == 2020
           AND t.id == 12953
         ACCUM
           FOREACH r1 IN s.@r1_list DO
             IF r1.flight_date == e.FlightDate
                AND r1.sched_arr_time + 100 <= str_to_int(e.CRSDepTime)
                AND r1.actual_arr_time >= str_to_int(e.DepTime) THEN
               @@monthly_counts += (r1.month -> 1)
             END
           END;

  PRINT @@monthly_counts AS monthly_stats;
}

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.

gsql

/* Q29. Top 10 Aircraft Turnarounds */
CREATE OR REPLACE DISTRIBUTED QUERY Q29() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<STRING tail_num, INT arr_time> IncomingPlane;

  ListAccum<IncomingPlane> @incoming_list;
  SetAccum<STRING> @transit_planes;

  Start = {Airport.*};
  Intermediate = SELECT t
                 FROM Start:s -(Flight>:e)- Airport:t
                 WHERE e.FlightDate == TO_DATETIME("2020-12-25 00:00:00")
                   AND e.Tail_Number != ""
                 ACCUM t.@incoming_list += IncomingPlane(
                           e.Tail_Number, str_to_int(e.CRSArrTime));

  Results = SELECT s
            FROM Intermediate:s -(Flight>:e)- Airport:t
            WHERE e.FlightDate == TO_DATETIME("2020-12-25 00:00:00")
              AND e.Tail_Number != ""
            ACCUM
              FOREACH incoming IN s.@incoming_list DO
                IF incoming.tail_num == e.Tail_Number
                   AND incoming.arr_time < str_to_int(e.CRSDepTime) THEN
                  s.@transit_planes += incoming.tail_num
                END
              END
            ORDER BY s.@transit_planes.size() DESC
            LIMIT 10;

  PRINT Results[Results.id AS airport_id,
                Results.city AS city,
                Results.code AS code,
                Results.@transit_planes.size() AS cnt];
}

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.

gsql

/* Q30. Three-Leg Flight Sequences */
CREATE OR REPLACE DISTRIBUTED QUERY Q30() FOR GRAPH ontime SYNTAX v2 {
  TYPEDEF TUPLE<STRING tail_num, INT arr_time> IncomingPlane;

  ListAccum<IncomingPlane> @hop1_list;
  ListAccum<IncomingPlane> @hop2_list;
  SetAccum<STRING> @@valid_tails;

  Start = {Airport.*};
  Hop1 = SELECT t
         FROM Start:s -(Flight>:e)- Airport:t
         WHERE e.FlightDate == TO_DATETIME("2020-12-25 00:00:00")
           AND e.Tail_Number != ""
         ACCUM t.@hop1_list += IncomingPlane(
                   e.Tail_Number, str_to_int(e.CRSArrTime));

  Hop2 = SELECT t
         FROM Hop1:s -(Flight>:e)- Airport:t
         WHERE e.FlightDate == TO_DATETIME("2020-12-25 00:00:00")
           AND e.Tail_Number != ""
         ACCUM
           FOREACH incoming IN s.@hop1_list DO
             IF incoming.tail_num == e.Tail_Number
                AND incoming.arr_time < str_to_int(e.CRSDepTime) THEN
               t.@hop2_list += IncomingPlane(
                 e.Tail_Number, str_to_int(e.CRSArrTime))
             END
           END;

  Hop3 = SELECT t
         FROM Hop2:s -(Flight>:e)- Airport:t
         WHERE e.FlightDate == TO_DATETIME("2020-12-25 00:00:00")
           AND e.Tail_Number != ""
         ACCUM
           FOREACH incoming IN s.@hop2_list DO
             IF incoming.tail_num == e.Tail_Number
                AND incoming.arr_time < str_to_int(e.CRSDepTime) THEN
               @@valid_tails += e.Tail_Number
             END
           END;

  PRINT @@valid_tails.size() 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;