Complex Queries | TYFSADIK

Objective

Write advanced SQL with window functions, recursive CTEs, and optimize queries with EXPLAIN.

Tools & Technologies

window functions
subqueries
GROUP BY
HAVING
CTE
EXPLAIN

Key Commands

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)

WITH RECURSIVE

EXPLAIN ANALYZE SELECT ...

LAG(value) OVER (ORDER BY date)

Architecture Overview

flowchart TD QUERY[Complex Query] --> AGG[Aggregation\nGROUP BY + HAVING] QUERY --> WIN[Window Functions\nROW_NUMBER / RANK / LAG] QUERY --> SUB[Subqueries\ncorrelated + derived tables] QUERY --> CTE[Common Table Expressions\nWITH clause] QUERY --> RECUR[Recursive CTEs\nhierarchical data] AGG --> OPT[EXPLAIN ANALYZE\nOptimize] WIN --> OPT style QUERY fill:#1a1a2e,stroke:#00d4ff,color:#e0e0e0 style OPT fill:#1a1a2e,stroke:#00ff88,color:#e0e0e0

Step-by-Step Process

Window Functions

Analyse data across rows without GROUP BY collapsing.

-- Running total
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

-- Rank by dept
SELECT
  name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Month-over-month change
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_sales;

EXPLAIN ANALYZE

Understand and optimize query performance.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

-- Look for:
-- Seq Scan on large tables → add index
-- Nested Loop with large tables → consider Hash Join
-- High 'actual rows' vs 'estimated rows' → run ANALYZE

Challenges & Solutions

Window functions cannot be used in WHERE — use a CTE or subquery to filter
EXPLAIN without ANALYZE shows plan; EXPLAIN ANALYZE actually runs the query

Key Takeaways

Window functions eliminate most need for self-joins in analytics queries
ANALYZE updates statistics that the query planner uses