FAQ: Usage Funnels - Build a Funnel from Multiple Tables 2

None able to help here??

Hello
It seems that this exercise makes the assumption that each user only completes one visit during the entire timeframe of the data recorded in the data base. Is that correct?
I’m interested in knowing how we should adapt the query in case a given user can have browsed the website multiple times.

Can we use WHERE instead of c.user_id IS NOT NULL AS 'is_checkout'? and why do use this c.user_id IS NOT NULL AS 'is_checkout' anyway? is there any other way to achieve this?

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_purchase'
FROM browse AS 'b'
LEFT JOIN checkout AS 'c'
ON b.user_id = c.user_id
LEFT JOIN purchase AS 'p'
ON p.user_id = c.user_id
LIMIT 50;

2 Likes

Would also be interested in this - I’ve used CASE but didn’t get any result. Curious to hear why

That’s what I wrote using CASE and got results:

SELECT DISTINCT b.browse_date AS 'Browse Date',
   b.user_id AS 'User ID',
   CASE
    WHEN c.user_id IS NOT NULL THEN 'True'
    ELSE 'False'
    END AS 'is_checkout',
   CASE
    WHEN p.user_id IS NOT NULL THEN 'True'
    ELSE 'False'
    END AS 'is_purchase'
FROM browse AS 'b'
LEFT JOIN checkout AS 'c'
  ON b.user_id = c.user_id
LEFT JOIN purchase AS 'p'
  ON c.user_id = p.user_id
LIMIT 50;

I’ve used WHERE as well and I get no result. As far as I might understand, the member who used CASE and got result means he/she is able to tell the program to distinguish between customers who did check-out and purchase the product and who did not. Meanwhile, using WHERE only CHOOSE people who did check-out and purchase (value is not null) while ignore who did not (value is null). For example, my code using WHERE did not return result I wanted.

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

Within the context of this exercise, why are we joining the three tables using user_id and not for example using item_id?

1 Like

I have included item_id to provide a clearer understanding of the browse-checkout-purchase process of user.

I also added few AND after ON to make sure browse’s item, user and date correspond to checkout ones and also checkout data correspond to purchase data.

SELECT b.item_id, b.browse_date, b.user_id, c.user_id IS NOT NULL AS ‘is_checkout’, p.user_id IS NOT NULL AS ‘is_purchase’

FROM browse AS “b”

LEFT JOIN checkout AS “c”

ON c.user_id = b.user_id AND c.checkout_date=b.browse_date AND c.item_id=b.item_id

LEFT JOIN purchase AS ‘p’

ON p.user_id = c.user_id AND c.checkout_date=p.purchase_date AND c.item_id=p.item_id

LIMIT 50;

1 Like

Hi there! Theoretical question, on the result the columns is_checkout/ is_purchase I understand that 0-1 means True-False but wherein the code is that order? Is it on IS NOT NULL? Can someone explain to me what’s the process in the program where that variable is transformed from date to 0-1.

Thanks guys!

Just so I am clear… in the previous step code it shows that the tables can be aliased using AS then in this code it shows that we can either use AS or just the alias name in ’ …’ - IS this why the code has an AS ‘b’ in the FROM browse AS ‘b’ and then not when aliasing checkout ‘c’ in the LEFT JOIN ???
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_purchase’
FROM browse AS ‘b’
LEFT JOIN checkout ‘c’
ON c.user_id = b.user_id
LEFT JOIN purchase ‘p’
ON p.user_id = b.user_id
LIMIT 50;

Hi, I actually had the same question and am confused regarding the input:

“c.user_id IS NOT NULL AS ‘is_checkout’, p.user_id IS NOT NULL AS ‘is_purchase’”

The examples I’ve seen so far have always used “IS (NOT) NULL” in a where clause, but never in a SELECT clause; if used in the context of a WHERE clause, I’d imagine the query would pull the actual user_id’s that are simply not null and not a binary. Why is it a binary when used in a SELECT statement?

1 Like

Is it possible that because there is a specific row for each item_id purchased (same user, same purchase date) a GROUP BY would return multiple checkouts and purchases for one transaction where the the use of DISTINCT would only count each cart and transaction once, regardless of the number of items?

So I have the code for this exercise but I was wondering how I would add in a ‘count’ here to tell me how many people went through each phase? would love any help!

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_purchase’
FROM browse AS ‘b’
LEFT JOIN checkout ‘c’
ON c.user_id = b.user_id
LEFT JOIN purchase ‘p’
ON p.user_id = c.user_id
LIMIT 50;

:pray: :pray: :pray:HELP PLEASE, I HAVE INTERVIEW NEXT WEEK :pray:t4: :pray:t4: :pray:t4:
I am not able to understand these statements. I have no idea how these statements are working as no prior lessons were given on NOT NULL for 0 and 1 or TRUE or FALSE in return when used in SELECT statement :rage:

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_purchase’

I tried to use the CASE-WHEN-THEN clause but not getting any result :expressionless:. Please help me where I am wrong here, if anybody has tried CASE-WHEN-THEN clause* :pray:

SELECT b.browse_date, b.user_id
(CASE
WHEN c.user_id IS NOT NULL THEN ‘TRUE’
ELSE ‘FALSE’
END) AS ‘is_checkout’,
(CASE
WHEN p.user_id IS NOT NULL THEN ‘TRUE’
ELSE ‘FALSE’
END) AS ‘is_purchase’
FROM browse b
LEFT JOIN checkout c
ON b.user_id=c.user_id
LEFT JOIN purchase p
ON p.user_id=c.user_id
LIMIT 50;

Hey I think it should work - only case where it would not work with GROUP BYs is if a user were to browse the same item multiple times throughout the the day (so therefore the same date but multiple rows of purchased ones and only browsed ones) OR if a user were to browse multiple different items throughout the same day but only a portion of which is purchased. In this case I think the result set would arbitrarily take checkout/purchase results when there would be multiple to chose from?

I think all we’re looking to do here is find if a user reaches checkout/ makes purchase on a particular day regardless of what type of item or how many times they browsed.

This was the first time I came across the ‘IS NOT NULL AS’ returning a Boolean list so was a bit thrown off. There seems to be a lot of gaps in between these lessons. Was this the first time you came across this too? If not please let me know if I’ve missed a lesson where this is already covered.

Thanks!

This was the first time I came across the ‘IS NOT NULL AS’ returning a Boolean list so was a bit thrown off. There seems to be a lot of gaps in between these lessons. Was this the first time everyone else came across this too? If not please let me know if I’ve missed a lesson where this is already covered.

Thanks! :sunglasses:

2 Likes

why doesn’t my solution work:

select distinct b.browse_date, b.user_id,
c.user_id as 'is_checkout',
p.user_id as 'is_purchase' from 
browse as b left join checkout as c
on b.user_id = c.user_id
left join purchase as p
on p.user_id = b.user_id
where c.user_id is not null and
p.user_id is not null
limit 50;

whereas the below solution works, I don’t get it:

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_purchase'
FROM browse AS 'b'
LEFT JOIN checkout 'c'
  ON c.user_id = b.user_id
LEFT JOIN purchase 'p'
  ON p.user_id = c.user_id
LIMIT 50;

The solution code didnt work for me. But the code below did work for me. Maybe it helps you.

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_purchase’

FROM browse AS ‘b’
LEFT JOIN checkout AS ‘c’
ON c.user_id = b.user_id
LEFT JOIN purchase ‘p’
ON p.user_id = c.user_id
LIMIT 50;

Hello friends,

Quick question.

I’m wondering why we are removing the AS on the LEFT JOIN for ‘p’ and ‘c’

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_purchase’
FROM browse AS ‘b’
LEFT JOIN checkout ‘c’ (I had originally had LEFT JOIN checkout AS ‘c’)
ON b.user_id = c.user_id
LEFT JOIN purchase ‘p’ (I had originally had LEFT JOIN purchase AS ‘p’)
ON c.user_id = p.user_id
LIMIT 50;

Please help clarify. Thank you!

As far as I know the ‘AS’ keyword is not necessary for SQL, it is more for the readability. Same goes for the ‘AS’ keyword in the SELECT statement. I tested it and it makes no difference. However, I think it also depends on the language (MySQL, SQL, PostgreSQL, etc. if the keyword is needed.