Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Method One
Method Two
Method Three
8. Method 3 – explanation
Summary

Instruction

Finally, let's take a look at Method 3. It's a bit more complicated. Method 3 is typically the best solution for more complex reports, such as ones with percentages or multi-level aggregation.

For each employee, we want to show the percentage of orders that have already been shipped that are going to Germany and the USA. This is what we want to see:

first_name last_name germany_perc usa_perc
Nancy Davolio 100 95.24
Andrew Fuller 92.86 100
Janet Leverling 100 100
... ... ... ...

And here's the query:

WITH germany_orders AS (
  SELECT
    employee_id,
    COUNT(CASE
      WHEN shipped_date IS NOT NULL
        THEN order_id
    END) AS count_shipped,
    COUNT(order_id) AS count_all
  FROM orders o
  WHERE o.ship_country = 'Germany'
  GROUP BY employee_id
),
usa_orders AS (
  SELECT
    employee_id,
    COUNT(CASE
      WHEN shipped_date IS NOT NULL
        THEN order_id
    END) AS count_shipped,
    COUNT(order_id) AS count_all
  FROM orders o
  WHERE o.ship_country = 'USA'
  GROUP BY employee_id
)
SELECT
  e.first_name,
  e.last_name,
  ROUND(ge_or.count_shipped / CAST(ge_or.count_all AS decimal) * 100, 2) AS germany_perc,
  ROUND(us_or.count_shipped / CAST(us_or.count_all AS decimal) * 100, 2) AS usa_perc
FROM germany_orders ge_or
FULL OUTER JOIN usa_orders us_or
  ON ge_or.employee_id = us_or.employee_id
JOIN employees e
  ON ge_or.employee_id = e.employee_id;

The general idea is to use separate CTEs to calculate metrics for each group, and then combine the results in the outer query.

Here, we have separate CTEs for German and American (USA) orders. In each CTE, we group orders by the employee_id column and calculate the numerator and denominator needed to find the percentages. In the outer query, we join both CTEs on the employee_id column and perform division to get the results.

Note the use of a FULL OUTER JOIN. A simple join only shows rows with matching column values in both tables. This means that employees who only shipped orders to one of the two countries wouldn't be shown at all. A FULL OUTER JOIN solves this problem.

Exercise

For each ship_country, we want to see the percentage of revenue for all orders before discount that has been generated by the employees with the IDs 1 and 2. Show three columns:

  1. The country to which an order is being shipped (ship_country).
  2. percentage_employee_1 – the percentage of pre-discount revenue generated by the employee with ID 1.
  3. percentage_employee_2 – the percentage of pre-discount revenue generated by the employee with ID 2.

Round the percentages to two decimal places.

Stuck? Here's a hint!

Create two CTEs, revenue_employee_1 and revenue_employee_2. In each of them, return the following columns: ship_country, employee_revenue, total_revenue. In the outer query, join the results on the ship_country column.