CTEs

WITH active_accounts AS (
  SELECT * FROM account WHERE status = 'OPEN'
)
SELECT product_code, COUNT(*) AS n
FROM active_accounts
GROUP BY product_code;

WITH step1_open AS (
  SELECT * FROM account WHERE account_status = 'OPEN'
),
step2_has_balance AS (
  SELECT * FROM step1_open WHERE current_balance > 0
),
step3_min_limit AS (
  SELECT * FROM step2_has_balance WHERE credit_limit >= 2000
),
step4_tenure AS (
  SELECT * FROM step3_min_limit
  WHERE open_date <= CURRENT_DATE - INTERVAL '6 months'
)
SELECT 'All open accounts' AS step, COUNT(*) AS n FROM step1_open
UNION ALL
SELECT 'Has balance > 0', COUNT(*) FROM step2_has_balance
UNION ALL
SELECT 'Limit >= 2000', COUNT(*) FROM step3_min_limit
UNION ALL
SELECT 'Open 6+ months', COUNT(*) FROM step4_tenure;

WITH account_activity AS (
  SELECT
    account_id,
    COUNT(txn_id) AS txn_count,
    COALESCE(SUM(amount), 0) AS total_spend
  FROM transactions
  WHERE txn_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY account_id
),
classified AS (
  SELECT *,
    CASE
      WHEN total_spend >= 5000 THEN 'HIGH_VALUE'
      WHEN total_spend >= 1000 THEN 'MEDIUM_VALUE'
      WHEN total_spend > 0 THEN 'LOW_VALUE'
      ELSE 'ZERO_SPEND'
    END AS segment
  FROM account_activity
)
SELECT
  segment,
  COUNT(*) AS accounts,
  ROUND(AVG(total_spend), 2) AS avg_spend,
  ROUND(AVG(txn_count), 1) AS avg_txns
FROM classified
GROUP BY segment;

WITH RECURSIVE hierarchy AS (
  SELECT
    employee_id, employee_name, manager_id,
    employee_name AS path,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id, e.employee_name, e.manager_id,
    h.path || ' > ' || e.employee_name,
    h.level + 1
  FROM employees e
  INNER JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy ORDER BY path;