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?

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.

1 Like

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;