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
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;
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.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.
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?
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 .
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;
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?
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)