SOLUTION WITH LEAD() OVER() ( SQL SERVER )
Intuition\r\n Describe your first thoughts on how to solve this problem. \r\n\r\n# Approach\r\n Describe your approach to solving the problem. \r\n\r\n# Complex
# Intuition\r\n<!-- Describe your first thoughts on how to solve this problem. -->\r\n\r\n# Approach\r\n<!-- Describe your approach to solving the problem. -->\r\n\r\n# Complexity\r\n- Time complexity:\r\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\r\n\r\n- Space complexity:\r\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\r\n\r\n# Code\r\n```\r\n/* Write your T-SQL query statement below */\r\n\r\n\r\n-- WITH CTE AS(\r\n-- \t\tSELECT\r\n-- \t\t\tplayer_id,\r\n-- \t\t\tMIN(event_date) event_date\r\n-- \t\tFROM\r\n-- \t\t\tActivity\r\n-- \t\tGROUP BY\r\n-- \t\t\tplayer_id\r\n-- \t\t--ORDER BY \r\n-- \t\t--\tplayer_id, event_date\r\n-- )\r\n-- SELECT ROUND ( (\r\n-- \t\tSELECT \r\n-- \t\t\tCOUNT( DISTINCT C.player_id) \r\n-- \t\tFROM\r\n-- \t\t\tCTE C\r\n-- \t\tOUTER APPLY\r\n-- \t\t\t(\r\n-- \t\t\t\tSELECT \r\n-- \t\t\t\t\tT.event_date\r\n-- \t\t\t\tFROM\r\n-- \t\t\t\t\tActivity T\r\n-- \t\t\t\tWHERE\r\n-- \t\t\t\t\tDATEADD(DAY,1,C.event_date) = T.event_date\t\r\n-- \t\t\t)R\r\n-- \t\tWHERE\r\n-- \t\t\tR.event_date IS NOT NULL\r\n-- ) / CAST((SELECT COUNT( DISTINCT player_id)\r\n-- \t\t        FROM\tActivity\r\n\t\t\t\t\t\t\r\n-- \t\t\t\t\t)\r\n-- \t\t\t\tAS FLOAT) , 2) fraction\r\n\r\n\r\n\r\nWITH CTE AS (SELECT\r\n\tplayer_id,\r\n\tevent_date,\r\n\tIIF( \r\n\t\tlead(event_date) OVER(partition by player_id order by player_id,event_date) = \r\n\t\tcast(dateadd(day,1,event_date) as date)\r\n\t\t,1,0) Isnext\r\nFROM \r\n\tActivity\r\n)\r\n\r\nSELECT\r\n\tROUND(  COUNT(DISTINCT C.player_id) \r\n  / CAST( (SELECT COUNT(DISTINCT player_id) FROM Activity) AS FLOAT) \r\n  ,\r\n   2) fraction\r\nFROM\r\n\tCTE C\r\nINNER JOIN\r\n\t(\r\n\t\tSELECT\t\r\n\t\t\tplayer_id,\r\n\t\t\tmin(event_date) mindate\r\n\t\tFROM\r\n\t\t\tActivity\r\n\t\tGROUP BY\r\n\t\t\tplayer_id\r\n\t)sub ON C.player_id = sub.player_id\r\n\tANd\r\n\t\tC.event_date = sub.mindate\r\nWHERE\r\n\tIsnext > 0\r\n\r\n```
['MS SQL Server']