By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table incoming (
week_number int,
product_category varchar(20),
incoming_amount int
);
insert into incoming values
(1, 'cat1', 5),
(4, 'cat2', 6),
(4, 'cat2', 2),
(4, 'cat3', 6),
(11, 'cat1', 6),
(11, 'cat3', 4);
create table outgoing (
week_number int,
product_category varchar(20),
outgoing_amount int
);
insert into outgoing values
(2, 'cat1', 5),
(3, 'cat2', 6),
(4, 'cat2', 1),
(4, 'cat2', 7),
(15, 'cat1', 6),
(15, 'cat1', 4);
12 rows affected
WITH cte_incoming AS (
SELECT week_number, product_category, SUM(incoming_amount) AS sum_incoming_amount
FROM incoming
GROUP BY week_number, product_category
),
cte_outgoing AS (
SELECT week_number, product_category, SUM(outgoing_amount) AS sum_outgoing_amount
FROM outgoing
GROUP BY week_number, product_category
)
SELECT COALESCE(i.week_number, o.week_number) AS week_number,
COALESCE(i.product_category, o.product_category) AS product_category,
sum_incoming_amount,
sum_outgoing_amount
FROM cte_incoming AS i
FULL OUTER JOIN cte_outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category
| week_number | product_category | sum_incoming_amount | sum_outgoing_amount |
|---|---|---|---|
| 1 | cat1 | 5 | null |
| 2 | cat1 | null | 5 |
| 3 | cat2 | null | 6 |
| 4 | cat2 | 8 | 8 |
| 4 | cat3 | 6 | null |
| 11 | cat1 | 6 | null |
| 11 | cat3 | 4 | null |
| 15 | cat1 | null | 10 |