Demos for Arvind Shyamsundar's SQL Graph session at Future Data Driven 2022

12dots.sql

drop table if exists n;
go

-- create a NODE table containing the various "dots"
CREATE TABLE n (
id INT,
x INT,
y INT
) AS NODE;
INSERT n (id, x, y)
VALUES (0, 1, 4),
(1, 3, 4),
(2, 0, 3),
(3, 2, 3),
(4, 4, 3),
(5, 1, 2),
(6, 3, 2),
(7, 0, 1),
(8, 2, 1),
(9, 4, 1),
(10, 1, 0),
(11, 3, 0);
go

drop table if exists e;
go

-- create an EDGE table representing the various "lines"
CREATE TABLE e (
dist decimal(10, 2)
) AS EDGE;
go

-- populate the "lines" with every possible connection between 2 dots in this grid
INSERT e ($FROM_ID, $TO_ID, dist)
SELECT n1.$NODE_ID,
n2.$NODE_ID,
sqrt(power(p1.x - p2.x, 2) + power(p1.y - p2.y, 2)) AS dist
FROM n AS p1 CROSS JOIN n AS p2
INNER JOIN
n AS n1
ON p1.id = n1.id
INNER JOIN
n AS n2
ON p2.id = n2.id
WHERE p1.id != p2.id;

-- create a helper UDF to "sort and concatenate" values in a row
CREATE OR ALTER FUNCTION dbo.SortedConcat
(@i1 INT, @i2 INT, @i3 INT, @i4 INT)
RETURNS VARCHAR (100)
with schemabinding
AS
BEGIN
    RETURN (SELECT STRING_AGG(i, ',') WITHIN GROUP (ORDER BY i)
            FROM   (VALUES (@i1), (@i2), (@i3), (@i4)) AS MyTab(i));
END
GO

-- create a helper UDF to count distinct values within a single row
CREATE OR ALTER FUNCTION dbo.DistinctCount
(@i1 INT, @i2 INT, @i3 INT, @i4 INT)
RETURNS INT
with schemabinding
AS
BEGIN
    RETURN (SELECT COUNT(DISTINCT i)
            FROM   (VALUES (@i1), (@i2), (@i3), (@i4)) AS MyTab(i));
END;
go

-- Final query
-- Part 1: define a CTE using MATCH to naively identify any possible 4-edged shape
WITH allsquares AS 
(
SELECT row_number() OVER (ORDER BY (SELECT NULL)) AS squareid,
n1.id AS id1,
n2.id AS id2,
n3.id AS id3,
n4.id AS id4,
e1.dist + e2.dist + e3.dist + e4.dist AS dist
FROM n AS n1, e AS e1, n AS n2, e AS e2, n AS n3, e AS e3, n AS n4, e AS e4
WHERE MATCH(n1-(e1)->n2
		AND n2-(e2)->n3
		AND n3-(e3)->n4
		AND n4-(e4)->n1)
AND e1.dist = e2.dist
AND e2.dist = e3.dist
AND e3.dist = e4.dist
)
-- Part 2: select only those "shapes" which join 4 distinct dots
SELECT distinct dbo.SortedConcat(id1, id2, id3, id4), dist
FROM allsquares
WHERE dbo.DistinctCount(id1, id2, id3, id4) = 4
ORDER BY dist;
GO

gistfile1.txt

-- Implementation of the LDBC Interactive Complex Query #3 (Reference: http://ldbc.github.io/ldbc_snb_docs/ldbc-snb-specification.pdf)
DECLARE @country1 AS BIGINT = (SELECT pl_placeid
                               FROM   place
                               WHERE  pl_name = 'Honduras');

DECLARE @country2 AS BIGINT = (SELECT pl_placeid
                               FROM   place
                               WHERE  pl_name = 'Estonia');

DECLARE @personId AS BIGINT = 2199023288279;

-- convert a UNIX timestamp to actual datetime
DECLARE @startDate AS DATE = DATEADD(SECOND, 1306886400, '1970-01-01');

-- 47 days from the start date
DECLARE @endDate AS DATE = DATEADD(DAY, 47, @startDate);

WITH     FriendQuery
AS       (SELECT LAST_VALUE(Person2.p_personid) WITHIN GROUP ( GRAPH PATH) AS friendId,
                 LAST_VALUE(Person2.p_firstname) WITHIN GROUP ( GRAPH PATH) AS friendFirstName,
                 LAST_VALUE(Person2.p_lastname) WITHIN GROUP ( GRAPH PATH) AS friendLastName,
                 LAST_VALUE(Person2.p_placeid) WITHIN GROUP ( GRAPH PATH) AS friendCountryId
          FROM   person AS Person1, knows FOR PATH AS k, person FOR PATH AS Person2
          WHERE  MATCH(SHORTEST_PATH(Person1(-(k)->Person2){1, 2}))
                 AND Person1.p_personid = @personId)
SELECT   friendFirstName,
         friendLastName,
         SUM(CASE WHEN m.m_locationId = @country1 THEN 1 ELSE 0 END) AS xCount,
         SUM(CASE WHEN m.m_locationId = @country2 THEN 1 ELSE 0 END) AS yCount
FROM     FriendQuery AS fq
         INNER JOIN
         message AS m
         ON fq.friendId = m.m_creatorId
            AND m.m_creationDate BETWEEN @startDate AND @endDate
            AND (m.m_locationId = @country1
                 OR m.m_locationId = @country2)
            AND friendCountryId != @country1
            AND friendCountryId != @country2
GROUP BY friendFirstName, friendLastName;
GO