What wrong with my code? SQL project

[# Usage Funnels with Warby Parker] (https://www.codecademy.com/paths/data-science/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-usage-funnels/projects/analyze-data-sql-usage-funnels-with-warby-parker)

table funnels was true, but the ‘quiz_to_try’ is like this, resulted as 1.0 . please answer me. THanks. link

edit: hi, i figure out because i use group by (1) so it only count user_id of each kind then result as 1.0, but then i change denominator ‘count(user_id)’ to 1000 it resulted as 0.0, so what is the right code?

number_of_pairs quiz_to_try try_to_purchase
0.0
3 pairs 1.0 0.530343007915567
5 pairs 1.0 0.792452830188679

with funnels as (SELECT DISTINCT q.user_id,
h.user_id IS NOT NULL AS is_home_try_on,
h.number_of_pairs,
p.user_id IS NOT NULL AS is_purchase
FROM quiz q
LEFT JOIN home_try_on h
ON q.user_id = h.user_id
LEFT JOIN purchase p
ON p.user_id = q.user_id)

select number_of_pairs,
1.0*(sum(is_home_try_on)/count(user_id))as quiz_to_try,
1.0*sum(is_purchase)/sum(is_home_try_on) as try_to_purchase
from funnels
group by 1;

If the user has chosen a pair to try, he converted.
So, quiz_to_try will always be 1.
Your code is correct.

i know but the result should be <1, ;quiz to try’ funnels, so i ask for solutions.

frequently, Code academy, use sum, then tell us to use excel to calculate the ‘rate’

then i change ‘count(user_id)’ to 1000 (just a number) then it result as 0.0? why?

You did multiplicated for 1.0 to get a float number?

1.0 * SUM(is_home_try_on)/1000

You’ll get 0.75 that’s quizz to home try convertion

1 Like

yes, i’ve already used still 0.0 at that column

with funnels as (SELECT DISTINCT q.user_id,
h.user_id IS NOT NULL AS is_home_try_on,
h.number_of_pairs,
p.user_id IS NOT NULL AS is_purchase
FROM quiz q
LEFT JOIN home_try_on h
ON q.user_id = h.user_id
LEFT JOIN purchase p
ON p.user_id = q.user_id)

select number_of_pairs,
1.0*(sum(is_home_try_on)/1000) as quiz_to_try,
1.0*sum(is_purchase)/sum(is_home_try_on) as try_to_purchase
from funnels
group by 1;

You’ve used () in your code. 1.0 x 0 = 0
You wrote:

1.0*(sum(is_home_try_on)/1000) as quiz_to_try,

What you should wrote:

1.0*sum(is_home_try_on)/1000 as quiz_to_try,

Ohhhh, so smart :laughing::laughing:, thanks a lot!!

1 Like

This topic was automatically closed 18 hours after the last reply. New replies are no longer allowed.