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:

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
  OR us_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.