Skip to content

Instantly share code, notes, and snippets.

@swanandp
Last active September 4, 2020 09:21
Show Gist options
  • Save swanandp/d0252911bb7bb4dbe82a0f9ce8df9d1a to your computer and use it in GitHub Desktop.
Save swanandp/d0252911bb7bb4dbe82a0f9ce8df9d1a to your computer and use it in GitHub Desktop.
Fearless Joins Companion SQL
CREATE TABLE rumours
(
id BIGSERIAL PRIMARY KEY,
description TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE tidbits
(
id BIGSERIAL PRIMARY KEY,
factoid TEXT NOT NULL,
rumour_id BIGINT REFERENCES rumours (id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE sources
(
id BIGSERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE rumour_sources
(
id BIGSERIAL PRIMARY KEY,
rumour_id BIGINT REFERENCES rumours (id),
source_id BIGINT REFERENCES sources (id),
supplied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (rumour_id, source_id)
);
CREATE TABLE tidbit_sources
(
id BIGSERIAL PRIMARY KEY,
tidbit_id BIGINT REFERENCES tidbits (id),
source_id BIGINT REFERENCES sources (id),
supplied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (tidbit_id, source_id)
);
CREATE TABLE labels
(
id BIGSERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
parent_id BIGINT REFERENCES labels (id),
EXCLUDE USING btree (lower(name) WITH =)
);
CREATE TABLE numbers
(
letter CHARACTER,
num INTEGER
);
CREATE TABLE literals
(
num INTEGER,
word VARCHAR
);
INSERT INTO numbers (letter, num)
VALUES ('a', 1),
('b', 2);
INSERT INTO literals (num, word)
VALUES (1, 'one'),
(3, 'three');
SELECT rumours.description, s.name
FROM rumours
INNER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
INNER JOIN sources s ON rs.source_id = s.id;
SELECT rumours.description, s.name
FROM rumours
LEFT OUTER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
LEFT OUTER JOIN sources s ON rs.source_id = s.id
WHERE rs.id IS NULL;
SELECT *
FROM rumours
LEFT OUTER JOIN rumour_sources rs ON 1 = 1;
SELECT rumours.id, rumours.description, rs.id, rs.supplied_at
FROM rumours
LEFT OUTER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
AND rs.supplied_at < '2020-07-31';
SELECT rumours.id, rumours.description, ARRAY_agg(rs.source_id) AS source_ids
FROM rumours
INNER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
GROUP BY rumours.id, rumours.description;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment