I have one dimension table contains business units and employee names as below.
table: dim
business | emp_name
-----------|-----------
sales | A
sales | B
marketing | C
product | D
IT | E
Also, a fact table that contains transactions between employees as below.
table: fct
client_emp_name | provider_emp_name | value
-----------------|--------------------|--------
A | C | 10
C | D | 5
C | B | 10
I would like to write a query to find sum of the values for each business as both clients and providers (including busines units with no transactions)
desired result
business | provider_total_value | client_total_value
-----------|-----------------------|---------------------
sales | 10 | 10
marketing | 15 | 0
product | 0 | 5
IT | 0 | 0
What is the best way to structure the SQL query for this case? I have the following query that gives accurate results but I'm not sure if it is the optimum way to solve this problem.
With provider as (
SELECT
d.business,
SUM(f.value) as provider_total_value
FROM fct as f
right join dimension as d on d.emp_name = f.provider_emp_name
Group by 1
),
client as (
SELECT
d.business,
SUM(f.value) as client_total_value
FROM fct as f
right join dimension as d on d.emp_name = f.client_emp_name
Group by 1
)
Select
p.business,
COALESCE(p.provider_total_value, 0) as provider_total_value,
COALESCE(c.client_total_value, 0) as client_total_value
From provider as p
Inner join client as c on c.business = p.business
Thank you!
