SQL Editor & Query Architecture Formatter

1. Efficient Pagination via Window Functions & CTEs

Avoid high-offset OFFSET 100000 queries which force the database to read and discard thousands of rows. Instead, use indexed keyset pagination or row partitioning:

WITH RankedOrders AS (
  SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rank_num
  FROM orders
  WHERE order_date >= '2024-01-01'
)
SELECT 
  order_id,
  customer_id,
  order_date,
  total_amount
FROM RankedOrders
WHERE rank_num <= 3
ORDER BY customer_id, order_date DESC;

2. Recursive CTE for Organizational Hierarchies

WITH RECURSIVE OrgHierarchy AS (
  -- Anchor Member: Top-level executives with no manager
  SELECT employee_id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive Member: Join subordinate employees
  SELECT e.employee_id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  INNER JOIN OrgHierarchy o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgHierarchy ORDER BY depth, name;