Unnecessary join increases total_spent?

Q&A DQ Courses
, ,
#1

So I inadvertently included an unnecessary join when writing my SQL query to classify spenders as big, small, or regular. I am having a hard time visualizing why this made everyone’s total_spent so much higher than it should be.

Screen Link:

My Code: 

SELECT
    c.first_name || ' ' || c.last_name customer_name,
    COUNT(iv.customer_id) number_of_purchases,
    SUM(iv.total) total_spent,
    CASE
        WHEN SUM(iv.total) < 40 THEN 'small spender'
        WHEN SUM(iv.total) > 100 THEN 'big spender'
        WHEN 40 < SUM(iv.total) < 100 THEN 'regular'
        END 
        AS customer_category
FROM customer c
LEFT JOIN invoice iv ON iv.customer_id = c.customer_id
LEFT JOIN invoice_line il ON il.invoice_id = iv.invoice_id
GROUP BY 1
ORDER BY 1;

The correct code:

My Code: 

SELECT
    c.first_name || ' ' || c.last_name customer_name,
    COUNT(iv.customer_id) number_of_purchases,
    SUM(iv.total) total_spent,
    CASE
        WHEN SUM(iv.total) < 40 THEN 'small spender'
        WHEN SUM(iv.total) > 100 THEN 'big spender'
        WHEN 40 < SUM(iv.total) < 100 THEN 'regular'
        END 
        AS customer_category
FROM customer c
LEFT JOIN invoice iv ON iv.customer_id = c.customer_id
GROUP BY 1
ORDER BY 1;

So I’m basically just trying to figure out what is going on behind the scenes when I accidentally included the invoice line table as well. I’m not changing how it calculating the sum… I know I must be missing something really obvious, but I have no idea.

Thanks!

#2

Similar situation where someone joined extra table invoice_line, creating duplicated rows after join to inflate sum.

What happens if you just break down the sql and go back step by step through the sql execution order? https://sqlbolt.com/lesson/select_queries_order_of_execution

Remove order by and print
Remove Group by and print (print your own summary stats if too many rows, some demonstrations in link above)
Remove join and print

1 Like