MySQL solution: multiple ways to approach it
I personally think it\'s such a great question to really think about how to report the same query usiing different methods. To me, this is a much more effective
I personally think it\'s such a great question to really think about how to report the same query usiing different methods. To me, this is a much more effective learning experience than just writing a query to pass the test case. \n\nThere are two broad way of approching it, and one with join and one with subquery. I will start with the one with subquery. \n\n## Solution one: no join\n### Temp table for solution 1\n```\n-- The purpose of this table is to \n-- 1) without collapsing any rows, \n-- 2) generate new column called next_day for each user and each day, and \n-- 3) generate new column called first_login, which\'s is the first login day for each user\n\n WITH lead_table AS(\n     SELECT player_id,\n         LEAD(event_date, 1) OVER(PARTITION BY player_id ORDER BY event_date) AS next_day,\n         MIN(event_date) OVER(PARTITION BY player_id) AS first_login\n     FROM Activity)\n\n```\n\n\n### Main query for solution 1, method 1: using subquery  \n```\n-- The reason why I think of subquery, is because the criteria stated in the WHERE clause, \n-- will ONLY apply to my nominator, but NOT the denominator\n-- Therefore, my COUNT(player_id) will simply give me the count when the "where" statement is true\n-- And my subquery will go back to the temp table and count the distinct player_id for me. \n-- Please note that in the subquery, I must count distinct, as I did not collapse rows in my temp table\n\nSELECT ROUND(\n    COUNT(player_id) /\n    (SELECT COUNT(DISTINCT player_id) FROM lead_table)    \n    ,2) AS fraction\nFROM lead_table \nWHERE DATEDIFF(next_day, first_login) = 1; \n\n```\n### Main query for solution 1, method 2: using CASE WHEN (still using the same temp table) \n```\n-- The idea is similar to the first main query, the objective is to obtain two numbers, \n-- the count of player meeting criteria, and the count of total player \n-- That\'s how CASE WHEN come into play, adding all players who have second login right after their first login\n-- Again, distinct is needed as the temp table did not collapse rows \n\nSELECT \n    ROUND(\n    SUM(CASE WHEN DATEDIFF(next_day, first_login) = 1 THEN 1 ELSE 0 END) /\n    COUNT(DISTINCT player_id)\n    , 2) AS fraction\nFROM lead_table \n\n```\n\n## Solution 2: using LEFT JOIN \n```\n-- This temp table uses left join, and the idea is \n-- 1) the left table will retain all users, regardless of whether they login after first login or not \n-- 2) the right table will only contain users who meet the criteria \n-- The window function finding the first_login date is needed to filter out later logins for each user \n\t--> for example, we don\'t care whether the user have consecutive login for their 2nd login. \nWITH joined AS(\n    SELECT d1.player_id AS day1_player,\n        d1.event_date,\n        d2.player_id AS day2_player, \n        MIN(d1.event_date) OVER(PARTITION BY d1.player_id) AS first_login \n    FROM Activity d1 \n    LEFT JOIN Activity d2\n    ON d1.player_id = d2.player_id\n        AND DATEDIFF(d2.event_date, d1.event_date) = 1)\n\nSELECT ROUND(\n        COUNT(day2_player) /\n        COUNT(day1_player)\n        , 2) AS fraction \nFROM joined \nWHERE event_date = first_login
['Sliding Window']