🚀 Detailed Explanation of my failed Approaches & Working One 👍✨
\n# Code Author Naveen Kumar Vadlamudi \n# I have evolved over final solution after trying multiple approaches.\n# Please Upvote if it helps \uD83D\uDC4D\n\n# 1
```\n# Code Author Naveen Kumar Vadlamudi \n# I have evolved over final solution after trying multiple approaches.\n# Please Upvote if it helps \uD83D\uDC4D\n\n# 1. Used Max and Min Event_Date, but it failed at 3 rd test case as i have assumed the problem statement in a wrong way.\n#  Here i was grouping by player and was trying to get the dates \n#  based on first test case, Which eventually failed \n\n    SELECT\n    ROUND\n    (\n        COUNT(DISTINCT PLAYER_ID) / \n        (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY) \n    , 2) AS FRACTION\n    FROM ACTIVITY \n    GROUP BY PLAYER_ID\n    HAVING ABS(MIN(EVENT_DATE) - MAX(EVENT_DATE)) = 1\n\n\n# 2. Used Difference Technique to arrive towards solution.\n# Here also i have failed to crack the solution and\n# it was doing the same defined work as above but i have not implemented the lag yet..\n\n\nSELECT \nROUND (SUM(A.PARTIAL) / (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY),2)  AS FRACTION\nFROM \n(\n    SELECT\n    COUNT(DISTINCT PLAYER_ID)  AS PARTIAL\n    FROM ACTIVITY \n    GROUP BY PLAYER_ID\n    HAVING ABS(MIN(EVENT_DATE) - MAX(EVENT_DATE)) = 1\n) AS A\n\n\n# 3. Implementing LAG for the problem...\n# hmm finally this approach must eventually work but it was failing too, then i figured out \n# that this code doesnt gaurantee the dates consecutively as mentioned in the statement.\n# So finally got an idea to implement RANK function to skim the first logged in date and next logged date...\n\nSELECT \nROUND (SUM(C.TOTAL_PLAYERS) / (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY),2) AS FRACTION\nFROM\n(\n     SELECT COUNT(DISTINCT PLAYER_ID) AS TOTAL_PLAYERS\n     FROM \n     (\n        SELECT \n        PLAYER_ID,\n        EVENT_DATE,\n        LAG(EVENT_DATE,1) OVER (PARTITION BY PLAYER_ID) AS DIFF\n        FROM \n        ACTIVITY \n        ) B\n        WHERE ( B.EVENT_DATE - B.DIFF = 1)\n) C\n\n# 4.  Final Working Approach \n# Here i have, used the Rank function to skim the first and second logged dates.\n# Thereafter i have used diff technique to check whether the difference is 1 or not.\n# finally i have counted the distinct player_id\'s that are filtered after all the operations... and has found the ratio..\n\nSELECT \nROUND (SUM(C.TOTAL_PLAYERS) / (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY),2) AS FRACTION\nFROM\n(\n     SELECT COUNT(DISTINCT PLAYER_ID) AS TOTAL_PLAYERS\n     FROM \n     (\n        SELECT \n        A.PLAYER_ID,\n        A.EVENT_DATE,\n        LAG(A.EVENT_DATE,1) OVER (PARTITION BY A.PLAYER_ID) AS DIFF\n        FROM \n        (\n            SELECT\n            PLAYER_ID,\n            EVENT_DATE,\n            DENSE_RANK() \n            OVER(PARTITION BY PLAYER_ID ORDER BY EVENT_DATE) as CRANK\n            FROM\n            ACTIVITY\n        ) A\n        WHERE A.CRANK IN (1,2)      \n        ) B\n        WHERE ( B.EVENT_DATE - B.DIFF = 1)\n) C\n\n```
['MySQL']