Top products

SELECT p.name, p.category,
       sum(oi.quantity) AS units,
       round(sum(oi.quantity * p.price)) AS revenue
  FROM sample.order_items oi
  JOIN sample.products p ON p.id = oi.product_id
  JOIN sample.orders o ON o.id = oi.order_id
 WHERE o.status = 'completed'
   AND (coalesce({{category}}, '') = '' OR p.category = {{category}})
 GROUP BY 1, 2
 ORDER BY revenue DESC
 LIMIT 25