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
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 | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 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 | 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 | 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 | US |
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;| 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.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario