Partition Record Counter (ROW_NUMBER())

-- Basic ROW_NUMBER
SELECT
  ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num,
  name, created_at
FROM users;

-- ROW_NUMBER per partition
SELECT
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank,
  name, department, salary
FROM employees;

-- Top 3 products per category using ROW_NUMBER
SELECT category, name, price FROM (
  SELECT
    cat.name AS category,
    p.name,
    p.price,
    ROW_NUMBER() OVER (
      PARTITION BY p.category_id
      ORDER BY p.price DESC
    ) AS rn
  FROM products p
  JOIN categories cat ON p.category_id = cat.id
  WHERE p.is_active = TRUE
) ranked
WHERE rn <= 3;

-- Pagination with ROW_NUMBER (works in all databases)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM products
) r WHERE rn BETWEEN 21 AND 40;

-- Remove duplicate emails (keep the newest account per email)
WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

-- Latest order per customer (one row per customer)
WITH latest_orders AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT
  u.name, u.email,
  lo.order_number, lo.total_amount, lo.created_at AS last_order
FROM users u
JOIN latest_orders lo ON u.id = lo.user_id AND lo.rn = 1;