Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Which orders beat their own store's average?

Which orders beat their own store's average? Each order is compared with a different number, its own store's average. Work out every store's average first, then join it back to the orders and compare.

Example

Each group's average, then compare

WITH store_avg AS (
  SELECT store, AVG(revenue) AS avg_revenue
  FROM orders
  GROUP BY store
)
SELECT o.order_id, o.store, o.revenue
FROM orders o
JOIN store_avg a ON o.store = a.store
WHERE o.revenue > a.avg_revenue

Result:

Step by step

1. Only the ones above their store's average.

   WITH store_avg AS (SELECT store, AVG(revenue) AS avg_revenue FROM orders GROUP BY store) SELECT o.order_id, o.store, o.revenue FROM orders o JOIN store_avg a ON o.store = a.store WHERE o.revenue > a.avg_revenue

2. The averages come from orders too.

   WITH store_avg AS (SELECT store, AVG(revenue) AS avg_revenue FROM orders GROUP BY store) SELECT o.order_id, o.store, o.revenue FROM orders o JOIN store_avg a ON o.store = a.store WHERE o.revenue > a.avg_revenue

3. WHERE o.revenue > a.avg_revenue.

   WITH store_avg AS (SELECT store, AVG(revenue) AS avg_revenue FROM orders GROUP BY store) SELECT o.order_id, o.store, o.revenue FROM orders o JOIN store_avg a ON o.store = a.store WHERE o.revenue > a.avg_revenue

4. A CTE called store_avg, joined back on store.

   WITH store_avg AS (SELECT store, AVG(revenue) AS avg_revenue FROM orders GROUP BY store) SELECT o.order_id, o.store, o.revenue FROM orders o JOIN store_avg a ON o.store = a.store WHERE o.revenue > a.avg_revenue

Practice

Try each one in your head first, then open the answer.

WITH store_avg AS (SELECT store, AVG(revenue) AS avg_revenue FROM orders GROUP BY store) SELECT o.order_id, o.store, o.revenue FROM orders o JOIN store_avg a ON o.store = a.store WHERE o.revenue > a.avg_revenue

SELECT order_id, store, revenue FROM orders WHERE revenue > (SELECT AVG(revenue) FROM orders)

WITH product_avg AS (SELECT product, AVG(revenue) AS avg_revenue FROM orders GROUP BY product) SELECT o.order_id, o.product, o.revenue FROM orders o JOIN product_avg a ON o.product = a.product WHERE o.revenue > a.avg_revenue

WITH spend AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id) SELECT customer_id, spend FROM spend WHERE spend > (SELECT AVG(spend) FROM spend)

SELECT store, AVG(revenue) AS avg_revenue FROM orders GROUP BY store HAVING AVG(revenue) > (SELECT AVG(revenue) FROM orders)