Screen Link:
My Code: <!–’’’%%sql
WITH total_sales AS
(
SELECT
c.customer_id,
c.support_rep_id,
SUM(i.total) total
FROM invoice i
INNER JOIN customer c ON c.customer_id = i.customer_id
Group BY 1
)
SELECT
e.first_name ||' '||e.last_name employee_name,
e.hire_date,
ts.total total_sales
FROM total_sales ts
INNER JOIN employee e on ts.support_rep_id = e.employee_id
GROUP BY 1
'''-->
**DQ solution**
%%sql
WITH customer_support_rep_sales AS
(
SELECT
i.customer_id,
c.support_rep_id,
SUM(i.total) total
FROM invoice i
INNER JOIN customer c ON i.customer_id = c.customer_id
GROUP BY 1,2
)
SELECT
e.first_name || " " || e.last_name employee,
e.hire_date,
SUM(csrs.total) total_sales
FROM customer_support_rep_sales csrs
INNER JOIN employee e ON e.employee_id = csrs.support_rep_id
GROUP BY 1;
What I expected to happen: Why the SUM() is used twice (in the subquery and main query) in the DQ solution??
What actually happened: Using the SUM () only once in the subquery gives different values .
Replace this line with the output/error