This community-built FAQ covers the “Compare Funnels For A/B Tests” exercise from the lesson “Usage Funnels”.
Paths and Courses
This exercise can be found in the following Codecademy content:
FAQs on the exercise Compare Funnels For A/B Tests
There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply () below.
If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.
Join the Discussion. Help a fellow learner on their journey.
Ask or answer a question about this exercise by clicking reply () below!
Agree with a comment or answer? Like () to up-vote the contribution!
SELECT modal_text,
COUNT(DISTINCT CASE
WHEN ab_group = ‘control’ THEN user_id
END) AS ‘control_clicks’
FROM onboarding_modals
GROUP BY 1
ORDER BY 1;
I can’t understand why, in line 2, DISTINCT is used. I thought that query will operate well without DISTINCT and it does. How this statement operates in this query?
Also a newbie here, so just a guess, but in theory the same user could visit and progress through the same stage (e.g. visiting/browsing/viewing cart etc.) more than once. They want the count of distinct users, not distinct clicks.
In practice though, there don’t appear to be any repeated users, so it doesn’t change the answer and I agree it’s confusing.
I think the reason has to do with the next task in this page rather than the one you point to - WHERE does work as long as we’re only interested in the control group. But as soon as we want to summarise both ‘control’ and ‘variant’, we can’t select only the ‘control’ rows anymore, because we need the data on ‘variant’ too. So we have to keep the full table and do the subset using the CASE syntax:
SELECT modal_text,
COUNT(DISTINCT CASE
WHEN ab_group = 'control' THEN user_id
END) AS 'control_clicks',
COUNT(DISTINCT CASE
WHEN ab_group = 'variant' THEN user_id
END) AS 'variant_clicks'
FROM onboarding_modals
GROUP BY 1
ORDER BY 1;
Hi i am a newbie, I just don’t get how the code in this exercise returns the number of users that have completed a modal. Because when using the “count” command we will also include the users that have closed at that modal hence we should also use a “where” statement so that we could really get those users that have completed a particular modal or else we would not get the number of people completing the fifth modal.
SELECT modal_text,
COUNT(DISTINCT CASE
WHEN ab_group = 'control' THEN user_id END) AS 'control_clicks',
COUNT(DISTINCT CASE
WHEN ab_group = 'variant' THEN user_id END) AS 'variant_clicks'
FROM onboarding_modals
WHERE user_action = 'Continue'
GROUP BY 1
ORDER BY 1;
You may find modal text 4,5 with the same number of control click and variant click:
Query Results
modal_text control_clicks variant_clicks
1 - Welcome to Mattresses and More! 301 394
2 - Browse our bedding selection 239 336
3 - Select items to add to your card 183 264
4 - View your cart by clicking on the icon 152 227
5 - Press 'Buy Now!' when you're ready to checkout 152 227
So, I think it would be more logical to think of as the user “reaching” which stage.
As I understood this THEN is a logical operator that does something if the previous WHEN condition is met, in this case (no pun intended) it returns all the user_id meeting the condition that was given (e.g group=“a”), and the COUNT then count the user_id’s being returned. CASE creates a sort of container with a result and we can save it as column by END AS ,or do something else with it, here we instead took that result and stored it in a column by COUNT AS.
In previous examples the return value in case was a text string that we defined ( WHEN rating > 10 THEN “EXCELLENT”) but here the result is simply itself after being filtered out. So it depends what we tells it to do.
Compare with python functions, (if , else and return, where we can say return some_variable in the end and then use it somewhere else)
Hope that helped! I am also a beginner so correct me if I’m wrong or was unclear.
As an experienced SQL programmer, I am pretty certain that you are correct about this. I tested with and without DISTINCT in all of the exercises and the answer comes out the same. That probably means their data doesn’t contain any repeat visitors. What the DISTINCT statement would do, if there were repeat users, would be to eliminate all but one of the repeat visits of all repeat users. At least this is what my gut says. I haven’t tested it with real repeat data.
I think that as we want to have 2 “new” columns to show the result (control/variant) this is the simplest way to do it. I cannot think an easier way to create a column and filter using WHERE clause other than that.
The number 1 refers to the first column we are selecting, in this case it is the modal_text column. You could also write ‘GROUP BY modal_text’ and ‘ORDER BY modal_text’, but using the numbers as shorthand can be easier. If we wanted to group by the second column we’d use GROUP BY 2 etc.
) below.
) to up-vote the contribution!