CTEs & Recursive Queries

Q: Difference between CTE and subquery?

A:

• Readability: CTEs are defined at the top, making the main query much cleaner.
• Reusability: A CTE can be referenced multiple times within the same query, whereas a subquery must be repeated.
• Recursion: CTEs support recursion, which subqueries do not.

Q: When should a CTE be avoided?

A: In some older database versions, CTEs acted as “optimization fences,” meaning the database would materialize the entire CTE result in memory even if only a few rows were needed. Modern engines (PostgreSQL 12+) have largely fixed this.

Q: What are the two parts of a Recursive CTE?

A:

1. Anchor Member: The initial query that returns the base result.
2. Recursive Member: The query that joins against the CTE itself to find the next level of data.

1. Generate employee hierarchy (Recursive CTE).

WITH RECURSIVE OrgChart AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN OrgChart oc ON e.manager_id = oc.id
)
SELECT * FROM OrgChart;

2. Deduplicate table keeping latest record.

WITH RankedRows AS (
    SELECT id, email, created_at,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn
    FROM users
)
DELETE FROM users 
WHERE id IN (SELECT id FROM RankedRows WHERE rn > 1);

3. Find top 3 salaries per department.

WITH SalRanking AS (
    SELECT name, department_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk
    FROM employees
)
SELECT * FROM SalRanking WHERE rnk <= 3;

4. Implement sessionization (30-min gap).

WITH LaggedEvents AS (
    SELECT user_id, event_time,
           LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) as prev_event
    FROM events
),
NewSessions AS (
    SELECT *,
           CASE WHEN event_time - prev_event > INTERVAL '30 minutes' 
                OR prev_event IS NULL THEN 1 ELSE 0 END as is_new_session
    FROM LaggedEvents
)
SELECT user_id, event_time, SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) as session_id
FROM NewSessions;