DuckPGQ

https://duckpgq.org
Admin
DuckPGQ is a DuckDB community extension that enables high-performance SQL/PGQ-based graph queries...
Tokens:8,194
Snippets:101
Trust Score:5.9
Update:2 months ago
Show doc for...
Tokens:
Raw
### Setup Financial Data Graph

Source: https://duckpgq.org/

Attaches the FinBench financial dataset and creates a property graph schema for financial analysis. Requires DuckPGQ to be installed and loaded.

```sql
ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/finbench.duckdb' AS finbench;
use finbench;
INSTALL duckpgq FROM community;
LOAD duckpgq; 

CREATE OR REPLACE PROPERTY GRAPH finbench
VERTEX TABLES (
  Account, Company, Loan, 
  Medium, Person
)
EDGE TABLES (
  AccountRepayLoan        SOURCE KEY (accountId) REFERENCES Account (accountId)
                          DESTINATION KEY (loanId) REFERENCES Loan (loanId) 
                          LABEL repay,
  AccountTransferAccount  SOURCE KEY (fromId) REFERENCES Account (accountId)
                          DESTINATION KEY (toId) REFERENCES Account (AccountId) 
                          LABEL transfer,
  AccountWithdrawAccount  SOURCE KEY (fromId) REFERENCES Account (accountId)
                          DESTINATION KEY (toId) REFERENCES Account (AccountId) 
                          LABEL withdraw,
  CompanyApplyLoan        SOURCE KEY (companyId) REFERENCES Company (companyId)
                          DESTINATION KEY (loanId) REFERENCES Loan (loanId) 
                          LABEL companyApply,
  CompanyGuaranteeCompany SOURCE KEY (fromId) REFERENCES Company (companyId)

```

--------------------------------

### Install and Load DuckPGQ

Source: https://duckpgq.org/

Commands to install and load the DuckPGQ extension in different environments.

```SQL
INSTALL duckpgq FROM community; 
LOAD duckpgq; 

```

```Python
import duckdb
conn = duckdb.connect()
conn.install_extension("duckpgq", repository="community")
conn.load_extension("duckpgq")

```

```Node.js
import { DuckDBInstance } from '@duckdb/node-api';
const instance = await DuckDBInstance.create();
const connection = await instance.connect();
await connection.run("INSTALL duckpgq FROM community");
await connection.run("LOAD duckpgq");

```

```R
library(duckdb)
con <- dbConnect(duckdb())
dbExecute(con, "INSTALL duckpgq FROM community")
dbExecute(con, "LOAD duckpgq")

```

```Java
Connection conn = DriverManager.getConnection("jdbc:duckdb:");
Statement st = conn.createStatement();
st.execute("INSTALL duckpgq FROM community");
st.execute("LOAD duckpgq");

```

--------------------------------

### Setup Social Network Graph

Source: https://duckpgq.org/

Attaches a social network dataset and creates a property graph schema for DuckPGQ. Ensure the dataset is accessible and DuckPGQ is installed and loaded.

```sql
ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/snb.duckdb';

use snb;
install duckpgq from community; 
load duckpgq;

CREATE or replace PROPERTY GRAPH snb
VERTEX TABLES (
  Person, Forum
)
EDGE TABLES (
  Person_knows_person     SOURCE KEY (Person1Id) REFERENCES Person (id)
                          DESTINATION KEY (Person2Id) REFERENCES Person (id)
                          LABEL knows,
  Forum_hasMember_Person  SOURCE KEY (ForumId) REFERENCES Forum (id)
                          DESTINATION KEY (PersonId) REFERENCES Person (id)
                          LABEL hasMember
);
```

--------------------------------

### Install and Load DuckPGQ Community Version

Source: https://duckpgq.org/documentation/loading

Use these SQL commands to install and load the stable community version of the DuckPGQ extension.

```sql
INSTALL 'duckpgq' FROM 'community';
LOAD 'duckpgq';
```

--------------------------------

### Setup Airline Data Graph

Source: https://duckpgq.org/

Attaches a small airline dataset and creates a property graph schema for flight-related queries. Ensures the dataset is accessible and DuckPGQ is installed.

```sql
ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/airline-data/datasets/airline-data-small.duckdb' as airline;

use airline;
install duckpgq from community;
load duckpgq; 

CREATE PROPERTY GRAPH flight_graph
  VERTEX TABLES (
    aircrafts_data, airports_data,
    bookings, flights,
    tickets, seats
  )
  EDGE TABLES (
    route
      SOURCE KEY (departure_airport) REFERENCES airports_data(airport_code)
      DESTINATION KEY (arrival_airport) REFERENCES airports_data(airport_code),
    ticket_flights
      SOURCE KEY (ticket_no) REFERENCES tickets(ticket_no)
      DESTINATION KEY (flight_id) REFERENCES flights(flight_id),
    bookings_tickets
      SOURCE KEY (book_ref) REFERENCES bookings(book_ref)
      DESTINATION KEY (ticket_no) REFERENCES tickets(ticket_no),
    boarding_passes 
      SOURCE KEY (ticket_no) REFERENCES tickets(ticket_no)
      DESTINATION KEY (seat_no) REFERENCES seats(seat_no)
);
```

--------------------------------

### Example: Create Property Graph 'snb'

Source: https://duckpgq.org/documentation/property_graph

This example demonstrates creating a property graph named 'snb' using vertex tables (Person, Message, Forum) and edge tables (Person_knows_person, Forum_hasMember_Person, Person_likes_Message) from the SNB dataset.

```sql
CREATE PROPERTY GRAPH snb 
VERTEX TABLES (
  Person,
  Message,
  Forum
)
EDGE TABLES (
  Person_knows_person     SOURCE KEY (Person1Id) REFERENCES Person (id)
                          DESTINATION KEY (Person2Id) REFERENCES Person (id)
                          LABEL Knows,
  Forum_hasMember_Person  SOURCE KEY (ForumId) REFERENCES Forum (id)
                          DESTINATION KEY (PersonId) REFERENCES Person (id)
                          LABEL hasMember,
  Person_likes_Message    SOURCE KEY (PersonId) REFERENCES Person (id)
                          DESTINATION KEY (id) REFERENCES Message (id)
                          LABEL likes_Message
);

```

--------------------------------

### Configure Repository and Load Latest DuckPGQ (CLI)

Source: https://duckpgq.org/documentation/loading

Set the custom extension repository and then force install and load the latest DuckPGQ version within the DuckDB CLI.

```sql
SET custom_extension_repository = 'http://duckpgq.s3.eu-north-1.amazonaws.com';
FORCE INSTALL 'duckpgq';
LOAD 'duckpgq';
```

--------------------------------

### Start DuckDB CLI with Unsigned Flag

Source: https://duckpgq.org/documentation/loading

Begin the DuckDB CLI session with the 'unsigned' flag enabled to allow loading custom extensions.

```bash
duckdb -unsigned
```

--------------------------------

### Calculate Weakly Connected Component

Source: https://duckpgq.org/documentation/graph_functions

Syntax and example for identifying connected components in an undirected graph.

```sql
SELECT *
FROM weakly_connected_component(<property graph>, <vertex label>, <edge label>);
```

```sql
SELECT *
FROM weakly_connected_component(snb, Person, Knows);
```

--------------------------------

### Edge Table Definition Example

Source: https://duckpgq.org/documentation/property_graph

Example of defining an edge table, specifying the table name, source key referencing the Person table, destination key referencing the Message table, and the label 'likes_Message'.

```sql
Person_likes_Message  SOURCE KEY (PersonId) REFERENCES Person (id)
                      DESTINATION KEY (id) REFERENCES Message (id)
                      LABEL likes_Message

```

--------------------------------

### Calculate PageRank

Source: https://duckpgq.org/documentation/graph_functions

Syntax and example for calculating node importance in a directed graph using a damping factor of 0.85.

```sql
SELECT *
FROM pagerank(<property graph>, <vertex label>, <edge label>);
```

```sql
SELECT *
FROM pagerank(snb, Person, Knows);
```

--------------------------------

### Query Property Graph Data

Source: https://duckpgq.org/documentation/property_graph

Query data from a defined property graph using `FROM GRAPH_TABLE`. This example matches Person vertices and Knows edges, selecting their IDs.

```sql
FROM GRAPH_TABLE (snb
    MATCH (p:Person)-[k:Knows]->(p2:Person)
    COLUMNS (p.id, p2.id)
    )
LIMIT 1;
```

--------------------------------

### Calculate Local Clustering Coefficient

Source: https://duckpgq.org/documentation/graph_functions

Syntax and example for calculating the LCC, which measures neighbor connectivity in an undirected graph.

```sql
SELECT * 
FROM local_clustering_coefficient(<property graph>, <vertex label>, <edge label>);
```

```sql
SELECT *
FROM local_clustering_coefficient(snb, Person, Knows);
```

--------------------------------

### Configure Repository and Load Latest DuckPGQ (Python)

Source: https://duckpgq.org/documentation/loading

Execute SQL commands via the Python connection to set the custom extension repository and load the latest DuckPGQ version.

```python
conn.execute("SET custom_extension_repository = 'http://duckpgq.s3.eu-north-1.amazonaws.com';")
conn.execute("FORCE INSTALL 'duckpgq';")
conn.execute("LOAD 'duckpgq';")
```

--------------------------------

### Run the extension

Source: https://duckpgq.org/documentation/building_duckpgq

Execute the DuckDB shell binary to run the extension code.

```bash
./build/<build mode>/duckdb
```

--------------------------------

### Locate build binaries

Source: https://duckpgq.org/documentation/building_duckpgq

Paths to the generated DuckDB shell, test runner, and extension binary.

```text
./build/<build mode>/duckdb
./build/<build mode>/test/unittest
./build/<build mode>/extension/duckpgq/duckpgq.duckdb_extension
```

--------------------------------

### Create Property Graph

Source: https://duckpgq.org/documentation/graph_functions

Initializes the Person and Person_knows_person tables and defines the property graph structure.

```sql
-- Create the Person table
CREATE TABLE Person AS 
SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv';

-- Create the Person_knows_person table
CREATE TABLE Person_knows_person AS 
SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/81c32c9002d4059c2c3073dbca155275/raw/8b440e810a48dcaa08c07086e493ec0e2ec6b3cb/person_knows_person-sf0.003.csv';

-- Create the property graph
CREATE PROPERTY GRAPH snb
  VERTEX TABLES (
    Person
  )
  EDGE TABLES (
    Person_knows_person 
      SOURCE KEY (Person1Id) REFERENCES Person (id)
      DESTINATION KEY (Person2Id) REFERENCES Person (id)
      LABEL Knows
  );
```

--------------------------------

### Build the extension

Source: https://duckpgq.org/documentation/building_duckpgq

Commands to compile the extension in release or debug modes, with optional Ninja support.

```makefile
make release
```

```makefile
make debug
```

```makefile
make <debug> GEN=ninja
```

--------------------------------

### Run SQL tests

Source: https://duckpgq.org/documentation/contribution_guide

Execute the primary SQL-based test suite for the extension.

```bash
make test
```

--------------------------------

### Connect to DuckDB with Unsigned Extensions Enabled (Python)

Source: https://duckpgq.org/documentation/loading

Establish a DuckDB connection in Python, enabling the 'allow_unsigned_extensions' configuration.

```python
import duckdb

conn = duckdb.connect(config={"allow_unsigned_extensions": "true"})
```

--------------------------------

### Build the project in debug mode

Source: https://duckpgq.org/documentation/contribution_guide

Use these commands to compile the project with debug symbols enabled.

```bash
make debug
make debug GEN=ninja
```

--------------------------------

### Run specific or all SQL tests

Source: https://duckpgq.org/documentation/contribution_guide

Use the unittest executable to run individual test files or the entire test suite.

```bash
cd build/debug/test
./unittest --test-dir ../../.. test/sql/<path_to_specific_test_file>
```

```bash
./unittest --test-dir ../../.. test/sql/*
```

--------------------------------

### CREATE PROPERTY GRAPH Syntax

Source: https://duckpgq.org/documentation/property_graph

Use this syntax to create a property graph. Vertex tables are mandatory, while edge tables are optional. Property graphs are transient by default but can be persistent.

```sql
CREATE [ OR REPLACE ] PROPERTY GRAPH [ IF NOT EXISTS ] (<property graph name> 
VERTEX TABLES (
    <vertex table>
[, <vertex table> ]
)
[ EDGE TABLES ( 
    <edge table> 
[, <edge table ] ) ];

```

--------------------------------

### List Registered Property Graphs

Source: https://duckpgq.org/documentation/pragma

Use the `show_property_graphs` pragma to list all property graphs currently registered in the DuckPGQ extension. This is useful for inspecting the available graphs in the current database session.

```sql
CREATE PROPERTY GRAPH my_graph (
  VERTEX TABLE people KEY (id),
  EDGE TABLE knows SOURCE people KEY (source_id) DESTINATION people KEY (target_id)
);

PRAGMA show_property_graphs;

```

--------------------------------

### Verify extension linking

Source: https://duckpgq.org/documentation/contribution_guide

Check the CMake output to confirm the extension is correctly linked.

```text
...
-- Extensions linked into DuckDB: [duckpgq, parquet]
-- Tests loaded for extensions: [duckpgq]
...
```

--------------------------------

### Define Property Graph with Pre-defined PK-FK Relations

Source: https://duckpgq.org/documentation/property_graph

Use this syntax when primary and foreign key relationships are already defined in the schema, allowing the system to infer connections automatically.

```sql
CREATE TABLE a (
  id BIGINT PRIMARY KEY,
  name VARCHAR
);
CREATE TABLE b (
  id BIGINT PRIMARY KEY,
  description VARCHAR
);
CREATE TABLE edge_ab (
  id BIGINT PRIMARY KEY,
  src BIGINT REFERENCES a(id),
  dst BIGINT REFERENCES b(id)
);
```

```sql
CREATE PROPERTY GRAPH g_relationship
VERTEX TABLES (a, b)
EDGE TABLES (edge_ab SOURCE a DESTINATION b);
```

--------------------------------

### Clone the DuckPGQ repository

Source: https://duckpgq.org/documentation/building_duckpgq

Use the recurse-submodules flag to ensure the correct version of DuckDB is included.

```bash
git clone --recurse-submodules https://github.com/cwida/duckpgq-extension.git
```

--------------------------------

### CREATE PROPERTY GRAPH

Source: https://duckpgq.org/documentation/property_graph

Defines the syntax for creating a property graph, which maps existing database tables into a graph structure with vertex and edge tables.

```APIDOC
## CREATE PROPERTY GRAPH

### Description
Creates a property graph as a layer on top of existing database tables. Property graphs are persistent and synchronized between connections as of v0.1.0.

### Syntax
```sql
CREATE [ OR REPLACE ] PROPERTY GRAPH [ IF NOT EXISTS ] <property graph name> 
VERTEX TABLES (
    <vertex table> [, <vertex table> ]
)
[ EDGE TABLES ( 
    <edge table> [, <edge table> ] ) ];
```

### Parameters
- **property graph name** (string) - Required - The name of the graph to create.
- **VERTEX TABLES** (list) - Required - A list of tables to be treated as vertices. At least one is required.
- **EDGE TABLES** (list) - Optional - A list of tables to be treated as edges, defining source and destination relationships.

### Vertex Table Definition
`<table name> [ AS <table name alias> ] [ PROPERTIES (<Properties>) ] [ LABEL <Label> ]`

### Edge Table Definition
`<table name> SOURCE KEY (<source column>) REFERENCES <table name> (<column>) DESTINATION KEY (<dest column>) REFERENCES <table name> (<column>) [ LABEL <Label> ]`
```

--------------------------------

### Load LDBC SNB Dataset Tables

Source: https://duckpgq.org/documentation/sql_pgq

Loads the Person and Person_knows_person tables from specified URLs into DuckDB. Ensure the database is empty before running.

```sql
CREATE TABLE Person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv';
CREATE TABLE Person_knows_person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/81c32c9002d4059c2c3073dbca155275/raw/8b440e810a48dcaa08c07086e493ec0e2ec6b3cb/person_knows_person-sf0.003.csv';
```

--------------------------------

### Handle Ambiguous PK-FK Relationships

Source: https://duckpgq.org/documentation/property_graph

When multiple foreign keys exist between tables, explicit key definitions are required to avoid errors.

```sql
CREATE TABLE Person(
    id BIGINT PRIMARY KEY
);

CREATE TABLE Person_knows_Person(
    Person1Id BIGINT REFERENCES Person (id), 
    Person2Id BIGINT REFERENCES Person (id)
);
```

```sql
CREATE PROPERTY GRAPH (snb 
VERTEX TABLES (Person)
EDGE TABLES (Person_knows_Person SOURCE Person DESTINATION Person); 
```

```sql
Invalid Error: Multiple primary key - foreign key relationships detected between Person_knows_Person and Person. Please explicitly define the primary key and foreign key columns using `SOURCE KEY <primary key> REFERENCES Person <foreign key>`
```

```sql
CREATE PROPERTY GRAPH snb
VERTEX TABLES (Person)
EDGE TABLES (Person_knows_Person 
             SOURCE KEY (Person1Id) REFERENCES Person(id) 
             DESTINATION KEY (Person2Id) REFERENCES Person(id));
```

--------------------------------

### Create Property Graph with Labels

Source: https://duckpgq.org/documentation/property_graph

Define a property graph specifying vertex and edge tables, including custom labels for edges. The label 'Knows' is assigned to the Person_knows_person edge table.

```sql
CREATE PROPERTY GRAPH snb
VERTEX TABLES (
  Person
)
EDGE TABLES (
    Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
                      DESTINATION KEY (Person2Id) REFERENCES Person (id)
                      LABEL Knows
);
```

--------------------------------

### Shortest Path Query

Source: https://duckpgq.org/documentation/sql_pgq

Finds the shortest path length between 'Jan' and the first five persons alphabetically in the 'snb' graph. Supports 'ANY SHORTEST' path, which is non-deterministic. The '+' syntax denotes a path with a lower bound of 1 and no upper bound.

```sql
FROM GRAPH_TABLE (snb
    MATCH p = ANY SHORTEST (a:Person WHERE a.firstName = 'Jan')-[k:knows]->+(b:Person)
    COLUMNS (path_length(p), b.firstName)
  )
ORDER BY firstName
LIMIT 5;
```

--------------------------------

### Retrieve shortest path metadata in DuckPGQ

Source: https://duckpgq.org/documentation/sql_pgq

Use element_id, vertices, edges, and path_length functions within a GRAPH_TABLE query to return path-specific information.

```sql
FROM GRAPH_TABLE (snb
    MATCH p = ANY SHORTEST (a:Person WHERE a.firstName = 'Jan')-[k:knows]->+(b:Person)
    COLUMNS (element_id(p), vertices(p), edges(p), path_length(p), b.firstName)
  )
ORDER BY firstName
LIMIT 5;
```

--------------------------------

### Describe Property Graph Schema

Source: https://duckpgq.org/documentation/property_graph

Use `DESCRIBE PROPERTY GRAPH` to view the schema details of a property graph, including table names, labels, vertex/edge status, and key references.

```sql
DESCRIBE PROPERTY GRAPH snb
```

--------------------------------

### Create Property Graph

Source: https://duckpgq.org/documentation/sql_pgq

Defines a property graph named 'snb' using the Person and Person_knows_person tables. This graph is persistent and reflects changes in the underlying data.

```sql
CREATE PROPERTY GRAPH snb
VERTEX TABLES (
    Person
  )
EDGE TABLES (
    Person_knows_person 
        SOURCE KEY ( person1id ) REFERENCES Person ( id )
        DESTINATION KEY ( person2id ) REFERENCES Person ( id )
        LABEL Knows
  );
```

--------------------------------

### Find Blocked Accounts via Transfers

Source: https://duckpgq.org/

Queries the graph to identify transfers between accounts where the destination account is flagged as blocked.

```sql
FROM GRAPH_TABLE (
  finbench 
  MATCH (src:Account where src.accountId = 16607023625929101)
    <-[e1:transfer]-(mid:Account)
    -[e2:transfer]->(dst:Account where dst.isBlocked = true) 
  COLUMNS (src.accountId as src_id, dst.accountId as dst_id)
) 
SELECT src_id, dst_id
WHERE src_Id <> dst_id; 
```

--------------------------------

### Find Shortest Path in Social Network

Source: https://duckpgq.org/

Finds the shortest path from a specified person to all other persons in the social network graph. Uses ANY SHORTEST pathfinding.

```sql
-- find the shortest path from one person to all other persons
FROM GRAPH_TABLE (snb
  MATCH p = ANY SHORTEST (p1:person WHERE p1.id = 14)-[k:knows]->*(p2:person)
  COLUMNS (p1.id, p2.id as other_person_id, element_id(p), path_length(p))
);
```

--------------------------------

### Define Inheritance with Labels

Source: https://duckpgq.org/documentation/property_graph

Use a type column to distinguish between entity types within a single table, enabling label-based filtering in queries.

```sql
Organisation LABEL Organisation IN typemask(company, university)
```

```sql
FROM GRAPH_TABLE (snb
  MATCH (a:person)-[w:worksAt]->(c:company)
  COLUMNS (a.firstName, c.name)
)
```

--------------------------------

### Create Vertex Table from Edge Table

Source: https://duckpgq.org/documentation/pragma

The `create_vertex_table` pragma automatically generates a vertex table. It derives vertex identifiers from the source and destination columns of a specified edge table, simplifying graph schema creation from relational data.

```sql
CREATE TABLE edges (src INT, dst INT);

```

```sql
PRAGMA create_vertex_table('edges', 'src', 'dst', 'vertices', 'id');

```

```sql
CREATE TABLE vertices AS
SELECT DISTINCT id FROM (
  SELECT src AS id FROM edges
  UNION ALL
  SELECT dst AS id FROM edges
);

```

--------------------------------

### Vertex Table Definition Syntax

Source: https://duckpgq.org/documentation/property_graph

Defines the structure for a vertex table within a property graph. Only the table name is required; aliases, properties, and labels are optional.

```sql
<table name> [ AS <table name alias> ] [ PROPERTIES (<Properties>) ] [ LABEL <Label> ] 

```

--------------------------------

### Drop Property Graph Safely

Source: https://duckpgq.org/documentation/property_graph

Use `DROP IF EXISTS PROPERTY GRAPH` to remove a property graph definition without raising an error if it does not exist.

```sql
DROP IF EXISTS PROPERTY GRAPH pg
```

--------------------------------

### Count Forums for Most Followed Person

Source: https://duckpgq.org/

Calculates the number of forums a person is a member of, after identifying the most followed person in the network. Combines follower count with forum membership.

```sql
-- Number of forums posted on by the most followed person
WITH
mfp AS (
  FROM GRAPH_TABLE (snb
    MATCH (follower:Person)-[follows:knows]->(person:Person)
    COLUMNS (person.id AS personID, person.firstname, follower.id AS followerID)
  )
SELECT personID, firstname, COUNT(followerID) AS numFollowers
GROUP BY ALL ORDER BY numFollowers DESC LIMIT 1
)
FROM
  mfp,
  GRAPH_TABLE (snb
    MATCH (person:Person)<-[fhm:hasMember]-(f:Forum)
    COLUMNS (person.id AS personID, f.id as forumId)
)
mem
SELECT mfp.personID, mfp.firstname, mfp.numFollowers, count(mem.forumId) forumCount
WHERE mfp.personID = mem.personID
GROUP BY ALL;
```

--------------------------------

### Pattern Matching Query for Friends of Jan

Source: https://duckpgq.org/documentation/sql_pgq

Finds friends of 'Jan' by matching a pattern in the 'snb' property graph. Returns the first names of the friends. DuckDB's syntax allows omitting 'SELECT' in favor of the 'COLUMNS' clause.

```sql
FROM GRAPH_TABLE(snb
    MATCH (a:Person WHERE a.firstName = 'Jan')-[k:Knows]->(b:Person)
    COLUMNS (b.firstName)
);
```

--------------------------------

### Drop Property Graph

Source: https://duckpgq.org/documentation/property_graph

Remove a property graph definition using `DROP PROPERTY GRAPH`. This command will fail if the property graph does not exist.

```sql
DROP PROPERTY GRAPH pg
```

--------------------------------

### Find Shortest Route Between Airports

Source: https://duckpgq.org/

Finds the shortest route between two specified airports using the airline flight data. Utilizes ANY SHORTEST pathfinding and joins with the route table.

```sql
FROM (
  SELECT unnest(flights) AS flights 
    FROM GRAPH_TABLE (
    flight_graph 
    MATCH o = ANY SHORTEST (a:airports_data WHERE a.airport_code = 'UKX')
      -[fr:route]->*
      (a2:airports_data WHERE a2.airport_code = 'CNN') 
    COLUMNS (edges(o) AS flights)
  )
) 
JOIN route f 
  ON f.rowid = flights;
```

--------------------------------

### Calculate Average Seat Price

Source: https://duckpgq.org/

Calculates the average price of seats across all bookings in the airline dataset. Groups results by seat number and orders by average amount.

```sql
FROM GRAPH_TABLE (
  flight_graph 
  MATCH (b:bookings)-[bt:bookings_tickets]->(t:tickets)-[bp:boarding_passes]->(s:seats)
)
SELECT round(avg(total_amount), 2) avg_amount, seat_no 
GROUP BY seat_no 
ORDER BY avg_amount DESC;
```

--------------------------------

### Define Graph Schema Relationships

Source: https://duckpgq.org/

Defines various edge relationships between entities like Company, Account, Loan, and Person within the graph schema.

```sql
                          DESTINATION KEY (toId) REFERENCES Company (companyId) 
                          LABEL companyGuarantee,
  CompanyInvestCompany    SOURCE KEY (investorId) REFERENCES Company (companyId)
                          DESTINATION KEY (companyId) REFERENCES Company (companyId) 
                          LABEL companyInvest,
  CompanyOwnAccount       SOURCE KEY (companyId) REFERENCES Company (companyId)
                          DESTINATION KEY (accountId) REFERENCES Account (accountId) 
                          LABEL companyOwn,
  LoanDepositAccount      SOURCE KEY (loanId) REFERENCES Loan (loanId)
                          DESTINATION KEY (accountId) REFERENCES Account (accountId) 
                          LABEL deposit,
  MediumSignInAccount     SOURCE KEY (mediumId) REFERENCES Medium (mediumId)
                          DESTINATION KEY (accountId) REFERENCES Account (accountId) 
                          LABEL signIn,
  PersonApplyLoan         SOURCE KEY (personId) REFERENCES Person (personId)
                          DESTINATION KEY (loanId) REFERENCES Loan (loanId) 
                          LABEL personApply,
  PersonGuaranteePerson   SOURCE KEY (fromId) REFERENCES Person (personId)
                          DESTINATION KEY (toId) REFERENCES Person (personId) 
                          LABEL personGuarantee,
  PersonInvestCompany     SOURCE KEY (investorId) REFERENCES Person (personId)
                          DESTINATION KEY (companyId) REFERENCES Company (companyId) 
                          LABEL personInvest,
  PersonOwnAccount        SOURCE KEY (personId) REFERENCES Person (personId)
                          DESTINATION KEY (accountId) REFERENCES Account (accountId) 
                          LABEL personOwn
);
```

--------------------------------

### Filter High-Value Transfers by Time

Source: https://duckpgq.org/

Filters transfer edges based on a specific timestamp range and a minimum transaction amount.

```sql
FROM GRAPH_TABLE (
  finbench 
  MATCH (src:Account)-[e:Transfer]->(dst:Account) 
  WHERE '2022-07-13 09:18:33.137' < e.createtime 
    AND e.createtime < '2022-09-03 02:31:47.812' 
    AND e.amount > 4829783
  );
```

--------------------------------

### Find Most Popular People in Social Network

Source: https://duckpgq.org/

Determines the top 3 most popular people based on the number of followers (connections) in the social network. Aggregates follower counts and orders results.

```sql
-- Find the 3 most popular people 
FROM GRAPH_TABLE (snb
  MATCH (follower:Person)-[follows:knows]->(person:Person)
  COLUMNS (person.id AS personID, person.firstname, person.lastname, follower.id AS followerID)
)
SELECT personID, firstname, lastname, COUNT(followerID) AS numFollowers
GROUP BY ALL 
ORDER BY numFollowers DESC 
LIMIT 3;
```

--------------------------------

### Find Mutual Friends in Social Network

Source: https://duckpgq.org/

Identifies mutual friends between two specified users in the social network. Requires users to be connected through a common friend.

```sql
-- Find mutual friends between two users
FROM GRAPH_TABLE (snb
  MATCH (p1:Person WHERE p1.id = 16)-[k:knows]->(p2:Person)<-[k2:knows]-(p3:Person WHERE p3.id = 32)
  COLUMNS (p2.firstName)
);
```
Tokens:
Raw