Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Basic CTE for readability
WITH high_value_customers AS (
  SELECT user_id, SUM(total) AS lifetime_value
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
  HAVING SUM(total) > 1000
),
customer_details AS (
  SELECT u.id, u.name, u.email, h.lifetime_value
  FROM users u
  JOIN high_value_customers h ON h.user_id = u.id
)
SELECT * FROM customer_details ORDER BY lifetime_value DESC;

-- RECURSIVE CTE — traverse a category tree
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 AS depth, name::TEXT AS path
  FROM categories
  WHERE parent_id IS NULL

UNION ALL

-- Recursive case: children
  SELECT c.id, c.name, c.parent_id,
         ct.depth + 1,
         ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT id, depth, path FROM category_tree ORDER BY path;

-- EXISTS vs IN
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 500
);