Analyse Data with SQL - Pro Skill Path
Usage Funnels Project - Lesson - Usage Funnels
I’m trying to understand the code here:
SELECT modal_text, COUNT(DISTINCT CASE WHEN ab_group = "control" THEN user_id END) AS "Control Group", COUNT(DISTINCT CASE WHEN ab_group = "variant" THEN user_id END) AS "Variant Group" FROM onboarding_modals GROUP BY modal_text ORDER BY 1; SELECT modal_text COUNT (DISTINCT ab_group = "control") FROM onboarding_modals GORUP BY 1;
This is my reading of it :
We couldn’t use the WHERE statement simply because we needed to perform another calculation, where there is a different condition to be met (upon which the execution would happen) - If we had put WHERE, the result would be zero - because the data for the second operation itself is not there.
I’m okay with the above, but I’m trying to understand the CASE statement.
In simple english, this is how I’m reading it:
COUNT the DISTINCT user_Id column WHEN the condition for ab_group column is “control” (as an example from first operation)
Now - What would you say is happening in the background? Is SQL filtering the data for where the ab_group is “Control” and counting the user_id column
Secondly, why didn’t we put ELSE here? Does this mean that its okay if we don’t put ELSE in a CASE statement (i.e its not a strict rule in SQL that ELSE should be there always when we are applying CASE statement?)
Third - I was wondering if there could be multiple condtions within this case statement in a hypothetical table with extensive data - a CASE statement like
COUNT(DISTINCT CASE WHEN ab_group = "Control2" THEN user_id2 WHEN ab_group = "Control3" THEN user_id3 ELSE user_id) AS "Example"
What I think is happening above is - the COUNT function will count a colum - yes, but that’s dependent on which condition gets met, if ab_group = Control 2 then what gets counted is user_id2, if nothing gets met then it just counts the user_id column.
Is that a correct understanding? (I do think there’s a flaw here, but unable to pinpoint)
Until this exercise, I was only aware of using CASE separately, that too to put a text where condition is met. Like,
CASE WHEN "Condition" THEN "Display This" WHEN "COndition 2" THEN "Display this" ELSE "Display this" END AS "Name of the column in output screen"
So would appreciate any guidance here.