We will take the common example of Customer, Transactions and the Tender used in the Transactions. Here we have two implied relationships:
- Customer may use a Tender (mode of payment e.g. Credit Card, Gift Card etc);
- A Tender (mode of payment) can be used by Customer
Here the the relationship is bi-directional with different context. Note that Customer may use several different Tenders in different transactions e.g. The Customer may use a Gift Card for Transaction 1 and Credit Card for Transaction 2. Also Note that a Single Tender may be used by several Customer for e.g. a Husband and Wife using the same Gift Card to pay for two different Transactions
This can be represented in a Graph as following:
Customer and Tender are the Nodes and two Edges are customer_paid_using and tender_used_by
The question we want to answer this what customers shared Tenders for e.g. Husband and Wife sharing a Gift Card to pay for different transactions.
Let’s start by generating some sample data:
CREATE TABLE customer (
customer_id NUMBER
, first_name VARCHAR(400)
);
CREATE TABLE tender (
card_number NUMBER
, expiration_date DATE
);
create table transactions (
transaction_id number
, customer_id number
, card_number number
);
insert into CUSTOMER values (111, 'Saqib');
insert into CUSTOMER values (222, 'Scott');
insert into CUSTOMER values (333, 'Angela');
insert into CUSTOMER values (444, 'Ali');
insert into tender values(6223423834321111, DATE '2028-01-01');
insert into tender values(6223423834322222, DATE '2028-02-01');
insert into tender values(6223423834323333, DATE '2028-03-01');
insert into TRANSACTIONS values (1, 111, 6223423834321111);
insert into TRANSACTIONS values (2, 222, 6223423834321111);
insert into TRANSACTIONS values (3, 111, 6223423834322222);
insert into TRANSACTIONS values (4, 333, 6223423834322222);
insert into TRANSACTIONS values (5, 444, 6223423834323333);
Let’s build a Property Graph based on the aforementioned relationship:
CREATE or replace PROPERTY GRAPH PAYMENT_TENDER_GRAPH
VERTEX TABLES (
customer KEY (customer_id) PROPERTIES ARE ALL COLUMNS,
tender KEY (card_number) PROPERTIES ARE ALL COLUMNS
)
EDGE TABLES (
transactions as customer_paid_using
KEY (transaction_id)
SOURCE KEY (customer_id ) REFERENCES customer(customer_id)
DESTINATION KEY (card_number) REFERENCES tender(card_number)
PROPERTIES ARE ALL COLUMNS
, transactions as tender_used_by
KEY ( transaction_id)
SOURCE KEY (card_number) REFERENCES tender(card_number)
DESTINATION KEY ( customer_id ) REFERENCES customer(customer_id)
PROPERTIES ARE ALL COLUMNS
);
Graph Query (GQL) to retrieve all the Customers that shared a Tender.
SELECT distinct *
FROM GRAPH_TABLE( PAYMENT_TENDER_GRAPH
MATCH (customer1 is customer) - [e1 is customer_paid_using] ->
(shared_tender is tender) - [e2 is tender_used_by] ->
(customer2 is customer)
where customer1.customer_id != customer2.customer_id
COLUMNS ( customer1.first_name as customer_1_name
, shared_tender.card_number as shared_tender
, customer2.first_name as customer_2_name)
MATCH clause is used to define the pattern to find in the graph, using the visual syntax: (node) – [edge] -> (node) -> [edge] -> (node)