Posted on:

11 Jul 2021

0

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




0 answers ( 0 marked as helpful)

Submit an answer