Review this generated category-order report before it reaches a multitenant production database.
For one tenant, traverse a category subtree, exclude blocked accounts, and return exactly two paid orders per category with stable ordering. Return only order_id, category_id, amount, and position.
SQL
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, tenant_id
FROM categories
WHERE id = :root_id
UNION
SELECT c.id, c.parent_id, c.tenant_id
FROM categories AS c
JOIN category_tree AS t ON c.parent_id = t.id
),
eligible_orders AS (
SELECT o.*
FROM orders AS o
WHERE o.category_id IN (SELECT id FROM category_tree)
AND o.account_id NOT IN (SELECT account_id FROM blocked_accounts)
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY amount DESC
) AS position
FROM eligible_orders
)
SELECT *
FROM ranked WHERE position <= 2;
generated code is illustrative, not from any one model