# What wrong with my code? SQL project

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.

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 , thanks a lot!!

1 Like

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