HELP I can't for the life of me get my head around the SQL funnels!

So basically,

Ive been learning SQlite for about a week, when I hit a road block, I’ve always been able to fiqure it out and get my head around it. But, with the funnel lesson:

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-usage-funnels/lessons/sql-funnels/exercises/multiple-tables-ii

I can understand why a funnel is used and created, but I cannot understand

  1. The user_id ‘IS NOT NULL’ clause

  2. The way each table is referenced towards a column

  3. Inside the code which is taught in the lesson, it takes the checkout user_id and purchase user_id where its NOT NULL and counts it as true of false. 0 being false and 1 being true. But to try and get around this I viewed a limited amount of all columns on the tables, and NONE of the user_id records are NULL, so why are we grabbing the NOT NULL records, isnt that all of them? so in turn, why do we need the IS NOT NULL statement?

  4. Maybe not such a big issue, it kind of makes sense, but in previous lessons when joining tables ive been taught to reference columns in tables as eg. ‘browse.user_id’ and ‘checkout.user_id’ but without mentioning anywhere, the text engine doesnt accept this in this lesson, instead it only accepts ‘b.user_id’ and ‘c.user_id’. Which should I use moving forward, are both genreally accepted but just not in this lesson?

  5. One other thing about these tables, aren’t checkout and purchase the same thing? I feel like I am missing something here.

See code example:

SELECT DISTINCT b.browse_date,
  b.user_id,
  c.user_id IS NOT NULL AS 'is_checkout',
  p.user_id IS NOT NULL AS 'is_purchased'
FROM browse AS 'b'
LEFT JOIN checkout AS 'c'
  ON b.user_id = c.user_id
LEFT JOIN purchase AS 'p'
  ON b.user_id = p.user_id
LIMIT 50;

Thanks for any help offered in advance!

it is setting user_id to false if NULL or true if NOT NULL (it will make sense at answer #3.

I’m not sure what you mean by that, sorry. Are you talking about the use of alias? AS ‘c’, AS ‘p’?

You will not find any user_id == NULL on the tables, however you will find in the table that you are build in this query that you copied.
As you can see, you are using LEFT JOINS… this will join the tables, even if they don’t have matching user_ids, and this is where the user_id == NULL will appear.
You can remove 'IS NOT NULL AS ‘is_checkout’" and “IS NOT NULL AS ‘is_purchased’” from the query and run it… you will see a bunch of NULLS… those are the ones that would turn FALSE after the LEFT JOIN.
Essentially, if a user_id appears on checkout table, but the same user_id doesn’t appear on purchase, it will return null for p.user_id when you perform the LEFT JOIN, confirming that the user went to checkout, but didn’t purchase.

Is common practice to use the alias when referencing and joining tables, but both should work. If it is not working, is some bug on the platform. But get used to use alias on your tables, you will code faster and it is quicker to read the query as they get shorter.

in this scenario, checkout means: “the customer put things on the cart” or “the customer went to checkout line/page”… something like that.
purchase: “the customer paid for the stuff and completed the transaction”.
You can think in a scenario that you went to the checkout and, at the moment you had to pay, your card got declined, you decided to think a little more, you found a better deal on another online shop and closed the browser without completing the transaction… you got the idea :slight_smile:

2 Likes

Thank you so much!

You answered all my questions!

I knew I was missing something obvious with regards to the checkout/purchase difference.

But after reading your answers, it seems that I was missing something obvious on most points! how a fresh pair of eyes helps.

Much appreciated, moving on in the path :slight_smile:

Thanks.

1 Like