Window Functions and Ranking Interviews

-- 1) Reconcile the ranking input to the raw item grain: total revenue in
--    category_customer must equal the raw line_total sum WITHOUT the products
--    join (returns a row only on mismatch — i.e., the join dropped or
--    duplicated item rows, or you summed order_total instead of line_total)
WITH category_customer AS (
  SELECT p.category, o.customer_id, SUM(oi.line_total) AS revenue
  FROM ecom_orders o
  JOIN ecom_order_items oi ON oi.order_id = o.order_id
  JOIN ecom_products p ON p.product_id = oi.product_id
  WHERE o.order_status = 'completed'
  GROUP BY p.category, o.customer_id
),
raw_items AS (
  SELECT SUM(oi.line_total) AS raw_revenue
  FROM ecom_orders o
  JOIN ecom_order_items oi ON oi.order_id = o.order_id
  WHERE o.order_status = 'completed'
)
SELECT
  ROUND((SELECT SUM(revenue) FROM category_customer), 2) AS aggregated_revenue,
  ROUND(raw_revenue, 2) AS raw_revenue
FROM raw_items
WHERE ROUND((SELECT SUM(revenue) FROM category_customer), 2) <> ROUND(raw_revenue, 2);

-- 2) Ensure final output never contains ranks above 3
WITH category_customer AS (
  SELECT p.category, o.customer_id, SUM(oi.line_total) AS revenue
  FROM ecom_orders o
  JOIN ecom_order_items oi ON oi.order_id = o.order_id
  JOIN ecom_products p ON p.product_id = oi.product_id
  WHERE o.order_status = 'completed'
  GROUP BY p.category, o.customer_id
),
ranked AS (
  SELECT
    category,
    customer_id,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
  FROM category_customer
)
SELECT category, COUNT(*) AS rows_above_rank_3
FROM ranked
WHERE revenue_rank > 3
GROUP BY category;