Facebook
From Harrison, 1 Week ago, written in SQL.
Embed
Download Paste or View Raw
Hits: 35
  1. WITH regional_sales AS (
  2.         SELECT region, SUM(amount) AS total_sales
  3.         FROM orders
  4.         GROUP BY region
  5.      ), top_regions AS (
  6.         SELECT region
  7.         FROM regional_sales
  8.         WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
  9.      )
  10. SELECT region,
  11.        product,
  12.        SUM(quantity) AS product_units,
  13.        SUM(amount) AS product_sales
  14. FROM orders
  15. WHERE region IN (SELECT region FROM top_regions)
  16. GROUP BY region, product;