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:
I can understand why a funnel is used and created, but I cannot understand
The user_id ‘IS NOT NULL’ clause
The way each table is referenced towards a column
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?
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?
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!