TechShop — flagship business queries

SELECT c.name AS category, SUM(oi.qty * oi.price) AS revenue
FROM order_items oi
JOIN products p  ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY revenue DESC;

SELECT name, sold, rank
FROM (
  SELECT p.name, SUM(oi.qty) AS sold,
         RANK() OVER (ORDER BY SUM(oi.qty) DESC) AS rank
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.name
) top
WHERE rank <= 5;

WITH spend AS (
  SELECT u.id, u.name, SUM(o.total) AS total
  FROM users u JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.name
)
SELECT name, total,
       NTILE(4) OVER (ORDER BY total DESC) AS tier
FROM spend
ORDER BY total DESC;

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS delta
FROM monthly
ORDER BY month;