FORUM SQL Habit Lesson 108 Homework Check?

Lesson 108 Homework Check?

Does this look correct for the homework at the bottom of lesson 108 where it asks to add country to the CTE?

I chose inner join instead of left join but i dont think it would matter if i did left join?

WITH customers AS (
  SELECT
    a.user_id,
    b.country,
    MIN(a.created_at) AS first_purchased_at
  FROM purchases a
  inner join users b
  on a.user_id = b.id
  WHERE
    refunded = FALSE
  GROUP BY 1,2
  ORDER BY 3 DESC
)		

SELECT *
FROM customers

REPLIES

Yep, in that specific case (⚠ where we’re querying the purchases table and joining the users table) INNER JOIN and LEFT JOIN will produce the same result (because for all purchases records there’s a correspondent record in the users table).

The situation will change if you’re querying the users table and joining the purchases table. Compare the results of these 2 queries:

INNER JOIN

SELECT COUNT(*)
FROM users u
INNER JOIN purchases p
  ON u.id = p.user_id

LEFT JOIN

SELECT COUNT(*)
FROM users u
LEFT JOIN purchases p
  ON u.id = p.user_id  

❓ What would you need to add to the LEFT JOIN query to make it produce the same result as the INNER JOIN? 🤔

WRITE A REPLY