Great! The final report type we'll talk about in this part shows what proportion of the whole each group represents.

Suppose that we want to create a report that shows information about the customers who placed orders in July 2016 and the percentage of total monthly revenue each customer generated. We can use the following code:

WITH total_sales AS (
  SELECT 
    SUM(quantity * unit_price) AS july_sales 
  FROM order_items oi
  JOIN orders o
    ON o.order_id = oi.order_id
  WHERE order_date >= '2016-07-01' AND order_date < '2016-08-01'
)
SELECT 
  c.customer_id, 
  SUM(quantity * unit_price) AS revenue, 
  ROUND(SUM(quantity * unit_price) / CAST(total_sales.july_sales AS decimal) * 100, 2) AS revenue_percentage
FROM total_sales,
  customers c
JOIN orders o
  ON c.customer_id = o.customer_id 
JOIN order_items oi
  ON oi.order_id = o.order_id
WHERE order_date >= '2016-07-01' AND order_date < '2016-08-01'
GROUP BY c.customer_id, total_sales.july_sales;

In the CTE, we simply calculate the total monthly revenue from July 2016. In the outer query, we show each customer_id alongside that customer's revenue in July 2016. But note what happens in the last column: We divide the customer's revenue (from the previous column) by the july_sales value from the CTE. This gives us the revenue percentage generated by a given customer.

Note that we had to add the july_sales column to the GROUP BY clause because it wasn't used with any aggregate function.

Naturally, the outer query only makes sense when it has the same WHERE clause as the inner query. Also, note that we join the total_sales and the customers tables in the following way:

FROM total_sales, customers c

This ensures that all rows (here, the only row) from the total_sales CTE are combined with all rows from the customers table. As a result, the july_sales value is available in all rows of the total_sales-customers combination.