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

Follow this topic

Posted on:

11 Jul 2021

Final steps of Mock Interview 1 - clarification

0 answers ( 0 marked as helpful)