Final steps of Mock Interview 1 - clarification
Hi,
I got a little lost towards the end of the Mock Interview 1 (SQL for Data Science Interviews | 365 DataScience). As far as I understood, the goal of the final correction is to change the aggregate function used in the outer query from Average to Count/Count.
After replacing the 'second' part of the query with the new code, the obtained results did not change. Is that to be expected? Or did I misunderstand the question or code?
Original query:
WITH created_events
AS (SELECT created_at,
Count(CASE
WHEN event_name = 'post' THEN 1
ELSE NULL
END) * 1.00 / Count(CASE
WHEN event_name = 'enter' THEN 1
ELSE NULL
END) * 100 AS perc_success
FROM interviews.post_events
GROUP BY created_at
ORDER BY created_at)
SELECT Extract(dow FROM created_at) AS dow,
Avg(perc_success)
FROM created_events
GROUP BY 1
ORDER BY 2 ASC;
Query after the final modification:
WITH created_events
AS (SELECT created_at,
Count(CASE
WHEN event_name = 'post' THEN 1
ELSE NULL
END) * 1.00 / Count(CASE
WHEN event_name = 'enter' THEN 1
ELSE NULL
END) * 100 AS perc_success
FROM interviews.post_events
GROUP BY created_at
ORDER BY created_at)
SELECT Extract(dow FROM created_at) AS dow,
Count(CASE
WHEN event_name = 'post' THEN 1
ELSE NULL
END) * 1.00 / Count(CASE
WHEN event_name = 'enter' THEN 1
ELSE NULL
END) * 100 AS perc_success
FROM interviews.post_events
GROUP BY 1
ORDER BY 2 ASC;
Output for both queries:
3 0
1 33.333333333333336
2 50