Inner join affecting answers for some reason

Screen Link: https://app.dataquest.io/m/190/building-and-organizing-complex-queries/8/challenge-each-countrys-best-customer

Hello all,

I attempted with challenge question (Each Country’s Best Customer) and here’s my code.

 WITH 
    customer_info AS
    (
        SELECT 
            c.first_name || " " || c.last_name customer_name,
            c.country,
            SUM(i.total) total_purchased
        FROM customer c
        INNER JOIN invoice i ON c.customer_id = i.customer_id
        GROUP BY c.customer_id
    ),
    country_max AS
    (
        SELECT 
            country,
            MAX(total_purchased) max_purchased
        FROM customer_info
        GROUP BY country
    ),
    country_best_customer AS
    (
        SELECT 
            cm.country,
            (
                SELECT 
                    ci.customer_name
                FROM customer_info ci
                WHERE ci.country = cm.country AND cm.max_purchased = ci.total_purchased 
             ) customer_name,
             cm.max_purchased total_purchased
        FROM country_max cm
    )

SELECT 
    * 
FROM country_best_customer

With this code, I get a number of missing customers in the customer_name column.

However, I figure out that if I switch the positions of customer c and invoice in my customer_info subquery (i.e. FROM invoice i INNER JOIN customer c), this somehow gets me the full table without missing customers (the correct final answer).

   (
       SELECT 
           c.first_name || " " || c.last_name customer_name,
           c.country,
           SUM(i.total) total_purchased
       FROM invoice i
       INNER JOIN customer c ON c.customer_id = i.customer_id
       GROUP BY c.customer_id
   )

I’m glad that I managed to get the final answer but I don’t understand why switching the position makes a difference since it is an inner join. Does anyone have any idea why this is happening? Thanks in advanced!

Hey, Jocelyn.

Thank you for this question. I was able to reproduce this; it blew me away.

This shouldn’t happen, I believe this is a bug with SQLite. Nothing in the code should make this change yield different results.

To make sure I wasn’t missing anything, I even tried to reproduce it in Postgres and I wasn’t able to, both versions of the code yielded the same result, as they should.

Nice catch!

3 Likes

Thanks for the feedback Bruno! I spent a lot of time trying to figure out what was going on. Glad that it was just a bug.