E-Commerce Financial Aggregation without Join Fan-Out

WITH agg_discounts AS
  (SELECT order_id,
          sum(amount) AS total_discount
   FROM order_discounts
   GROUP BY order_id),
     agg_shipments AS
  (SELECT order_id,
          sum(shipping_fee) AS total_shipping
   FROM order_shipments
   GROUP BY order_id)
SELECT m.merchant_id,
       m.merchant_name,
       sum(o.base_amount) AS total_gross_sales,
       coalesce(sum(d.total_discount), 0.00) AS total_discount_amount,
       coalesce(sum(s.total_shipping), 0.00) AS total_shipping_fees
FROM merchants m
JOIN orders o ON m.merchant_id = o.merchant_id
LEFT JOIN agg_discounts d ON o.order_id = d.order_id
LEFT JOIN agg_shipments s ON o.order_id = s.order_id
GROUP BY m.merchant_id,
         m.merchant_name;

CREATE TABLE merchants (merchant_id INT, merchant_name VARCHAR);


CREATE TABLE orders (order_id INT, merchant_id INT, base_amount decimal(10, 2));


CREATE TABLE order_discounts (discount_id INT, order_id INT, amount decimal(10, 2));


CREATE TABLE order_shipments (shipment_id INT, order_id INT, shipping_fee decimal(10, 2));


INSERT INTO merchants
VALUES (1, 'Acme Electronics');


INSERT INTO orders
VALUES (101, 1, 100.00);

-- 2 discounts applied to Order 101 ($10 and $5 = $15 total)

INSERT INTO order_discounts
VALUES (1, 101, 10.00),
       (2, 101, 5.00);

-- 2 split shipments for Order 101 ($4 and $6 = $10 total)

INSERT INTO order_shipments
VALUES (1, 101, 4.00),
       (2, 101, 6.00);