Seller Revenue and Performance Dashboard

WITH seller_sales AS (SELECT p.seller_id, COUNT(oi.order_item_id) AS items_sold, SUM(oi.price * oi.quantity) AS total_revenue FROM products p INNER JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.seller_id), seller_ratings AS (SELECT p.seller_id, AVG(r.rating) AS avg_rating FROM products p INNER JOIN reviews r ON p.product_id = r.product_id GROUP BY p.seller_id) SELECT s.seller_name, ss.items_sold, ROUND(ss.total_revenue, 2) AS total_revenue, ROUND(sr.avg_rating, 2) AS avg_rating, RANK() OVER (ORDER BY ss.total_revenue DESC) AS revenue_rank FROM sellers s INNER JOIN seller_sales ss ON s.seller_id = ss.seller_id LEFT JOIN seller_ratings sr ON s.seller_id = sr.seller_id WHERE ss.items_sold >= 2 ORDER BY total_revenue DESC;

Why this works

Sales and ratings are aggregated independently before they are joined to sellers. That preserves the order-item grain and prevents multiple reviews from multiplying revenue. RANK then orders the eligible seller totals; BookWorld has no fulfilled line items and does not qualify.

Success check

3 sellers — HomeEssentials (29.92 rank 1), TechGear Pro (98.88 rank 2), GlobalElectronics (1.98 rank 3)