The SQL you'd write on paper. Now actually works.

SELECT
  user_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY ts DESC
  ) AS rn
FROM payments;

Streaming partition walk on the index scan. No spool, no extra storage.

SELECT id, name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'open'
);

Rewritten as a semi-join. No row materialisation for the subquery.

SELECT id,
  CASE
    WHEN balance > 10000 THEN 'high'
    WHEN balance > 1000  THEN 'mid'
    ELSE 'low'
  END AS tier
FROM customers;

Branch-free expression in the planner. Pushes down with the rest of the WHERE.

SELECT
  customer_id,
  SUM(qty * price) AS revenue
FROM line_items
GROUP BY customer_id
HAVING SUM(qty * price) > 1000;

Aggregate wraps any expression - not just bare columns.