SQL Hierarchical Queries

Traverse parent-child nodes from roots to descendants with a recursive CTE.

What & Why

Hierarchical queries repeatedly follow a stable parent-child key. The relationship may be self-referencing, or related tables can first be normalized into one node set with compatible node_id and parent_id values.

Queryflo uses campaigns as root nodes and attaches each lead through the real marketing.leads.campaign_id relationship, then the recursive member advances one depth at a time.

See How It Works

BUSINESS QUESTION

Marketing wants a campaign-to-lead hierarchy built from the real campaigns.id to leads.campaign_id relationship.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

EXAMPLE QUERY

WITH RECURSIVE nodes AS (
  SELECT
    'campaign:' || c.id AS node_id,
    NULL::text AS parent_id,
    c.name AS node_name,
    'campaign'::text AS node_type
  FROM marketing.campaigns c
  UNION ALL
  SELECT
    'lead:' || l.id,
    'campaign:' || l.campaign_id,
    l.email,
    'lead'::text
  FROM marketing.leads l
  WHERE l.campaign_id IS NOT NULL
),
hierarchy AS (
  SELECT node_id, parent_id, node_name, node_type, 0 AS depth
  FROM nodes
  WHERE parent_id IS NULL
  UNION ALL
  SELECT child.node_id, child.parent_id, child.node_name, child.node_type, parent.depth + 1
  FROM nodes child
  JOIN hierarchy parent ON child.parent_id = parent.node_id
)
SELECT node_id, parent_id, node_name, node_type, depth
FROM hierarchy
ORDER BY SPLIT_PART(COALESCE(parent_id, node_id), ':', 2)::int, depth, node_id;

RESULT — campaigns and their lead children

node_id	parent_id	node_name	node_type	depth
campaign:1	NULL	Spring Launch	campaign	0
lead:301	campaign:1	ana@example.com	lead	1
lead:302	campaign:1	ben@example.com	lead	1
campaign:2	NULL	Retention Webinar	campaign	0
lead:303	campaign:2	chloe@example.com	lead	1
campaign:3	NULL	Finance Retargeting	campaign	0
lead:304	campaign:3	dev@example.com	lead	1
campaign:4	NULL	Enterprise Search	campaign	0

The recursive hierarchy starts with four campaign roots and places each related lead one level beneath its campaign.

Advanced business practice