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

Hi @midlindner,
I have the same question as @the_g1016. The query in question is roughly like this:

select browse_date
from browse
WITH funnels AS (
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 AS ‘p’
ON p.user_id = c.user_id)
SELECT COUNT(*) AS ‘num_browse’,
SUM(is_checkout) AS ‘num_checkout’,
SUM(is_purchase) AS ‘num_purchase’,
1.0 * SUM(is_checkout) / COUNT(user_id) AS ‘browse_to_checkout’,
1.0 * SUM(is_purchase) / SUM(is_checkout) AS ‘checkout_to_purchase’,
FROM funnels
group by browse_date
order by browse_date;

I know it doesn’t make sense, but could you tell me how to justify it?
Thanks

@ronaldhehe try remove the first 2 rows (select from) and leave the with clause, which is the start of your chained query.

1 Like

I would like to count the conversions without a calculator or excel.

I was thinking to inner join on a column with different times, I discovered the Lag window clause.

However, editor keeps empty with a message to write a query.

Could you tell what is wrong with this :


WITH funnels AS (
  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 AS 'p'
    ON p.user_id = c.user_id
),

q as (
SELECT 
     browse_date,
     COUNT(*) AS 'num_browse',
     SUM(is_checkout) AS 'num_checkout',
     SUM(is_purchase) AS 'num_purchase',
     1.0 * SUM(is_checkout) / COUNT(user_id) AS 'browse_to_checkout',
     1.0 * SUM(is_purchase) / SUM(is_checkout) AS 'checkout_to_purchase',
     
  FROM funnels
  group by browse_date
  order by browse_date
)

select 
  lag (browse_to_checkout, 1, 0) over (
    order by browse_date
  ) as conversion_checkout,
  lag (checkout_to_purchase, 1, 0) over (
    order by browse_date
  ) as conversion_purchase

from q;

ref: https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-lag/

How do I plug values from codecademy to a spreadsheet and get results?

2 Likes

Please I’m stuck here. I need help.
Plug these values into a spreadsheet program like Microsoft Excel or Google Sheets.

How has the conversion from checkout to purchase changed over time?

Please how do I go about it?

1 Like

I have the same problem , how to advance in the course?

Hey @somkyd @michaelaltermann7150 , if you hit the “run” key at the bottom, it will give you the checkmark to keep going.
At least that’s what I did, after copying and pasting it into excel.

6 Likes

Hi @ronaldhehe & @the_g1016

From the query I see the issues I can find are:

1.0 * SUM(is_purchase) / SUM(is_checkout) AS ‘checkout_to_purchase’,
SELECT browse_date FROM browse

you have a comma after purchase’ when there is no other calculations being done, so rather then grabbing the information FROM funnels its assuming that FROM funnels is a calculation that is bring ran.

the second issue is SELECT browse_date FROM browse (first 2 lines) doesn’t have ; at the end.
The WITH statement is saying ‘funnels’ is = to what is in the brackets, so you do not need the first two lines unless you wanted to bring up that table as well.

I hope that helps!

Hi @ronaldhehe and @the_g1016. Here is the solutions. There are a few mistakes in your query that you should correct.

WITH funnels AS (

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 AS ‘p’
ON p.user_id = c.user_id)
SELECT browse_date,
COUNT(*) AS ‘num_browse’,
SUM(is_checkout) AS ‘num_checkout’,
SUM(is_purchase) AS ‘num_purchase’,
1.0 * SUM(is_checkout) / COUNT(user_id) AS ‘P%-browse_to_checkout’,
1.0 * SUM(is_purchase) / SUM(is_checkout) AS ‘P%-checkout_to_purchase’
FROM funnels
GROUP BY browse_date
ORDER BY browse_date;

@somykq I cannot figure out what we are supposed to be pasting into Excel or Google Sheets. I copy the clipboard like I read to do but all it pastes into excel is the query itself which is just words. I do not get values or anything. Did you figure this out?

5 Likes

I’m really stuck on number 9. none of the answers above helped me.

I can’t figure it out what is wrong with my code:

SELECT browse_date
FROM browse
WITH funnels AS (
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 AS ‘p’
ON p.user_id = c.user_id)
SELECT COUNT(*) AS ‘num_browse’,
SUM(is_checkout) AS ‘num_checkout’,
SUM(is_purchase) AS ‘num_purchase’,
1.0 * SUM(is_checkout) / COUNT(user_id) AS ‘browse_to_checkout’,
1.0 * SUM(is_purchase) / SUM(is_checkout) AS ‘checkout_to_purchase’
FROM funnels
GROUP BY browse_date
ORDER BY browse_date;

So, we’ve created a funnel for Mattresses and More’s purchase process! It looks like:

WITH funnels AS (
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 AS ‘p’
ON p.user_id = c.user_id)
SELECT COUNT(*) AS ‘num_browse’,
SUM(is_checkout) AS ‘num_checkout’,
SUM(is_purchase) AS ‘num_purchase’,
1.0 * SUM(is_checkout) / COUNT(user_id) AS ‘browse_to_checkout’,
1.0 * SUM(is_purchase) / SUM(is_checkout) AS ‘checkout_to_purchase’
FROM funnels;

Edit the code in test.sqlite so that the first column in the result is browse_date .

Then, use GROUP BY so that we calculate num_browse , num_checkout , and num_purchase for each browse_date .

Also be sure to ORDER BY browse_date .

1 Like

WITH funnels AS (
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 AS ‘p’
ON p.user_id = c.user_id)
SELECT browse_date,
COUNT(*) AS ‘num_browse’,
SUM(is_checkout) AS ‘num_checkout’,
SUM(is_purchase) AS ‘num_purchase’,
1.0 * SUM(is_checkout) / COUNT(user_id) AS ‘browse_to_checkout’,
1.0 * SUM(is_purchase) / SUM(is_checkout) AS ‘checkout_to_purchase’
FROM funnels
GROUP BY browse_date
ORDER BY browse_date;

I’m trying the same code as you. I get this error:
Error: near line 1: no such column: b.browse_date

Which makes me confused, because the column does exist. Anyone knows whats wrong?

Thanks! I was spending SO LONG trying to click that stupid box but this worked like a charm.

with wp as(
select q.user_id as ‘user_id’
,
h.user_id is not null ‘is_home_try_on’,p.user_id is not null ‘is_purchase’,h.number_of_pairs
from quiz as q
left join home_try_on as h
on q.user_id=h.user_id
left join purchase as p
on p.user_id=h.user_id)
select count(*) as ‘quiz_conversion’,
sum(distinct case
when is_home_try_on =‘true’ then user_id
) as ‘home_conversion’
,sum(distinct case
when is_purchase =‘true’ then user_id)as ‘purchase_conversion’
from wp
;
I wrote this code for the warby parker project exercise 6. here i have created a table with true and false values for the is_home_try_on column. I don’t understand why is it not working?

Same here too, did it finally work for you?

Yeah, I found out that somehow i missed end at the end of each “case”

I’m not sure how to plug the resulting values from the query in Mattresses and More automatically into a spreadsheet. I am working directly on Codecademy’s platform can anyone give me additional details please? (i.e. Part 9 - Question 2)

2 Likes

Am I supposed to copy the code or the table? I clicked the copy to clipboard icon but it just copied my SQL code and I still can’t click on next.

Hello microcoder, I’ve managed to go to the next section by running the code again.