✔✔✔easy to understand query with simple explanation - MS SQL Server⚡⚡⚡
Approach\n1. Identify First Login Date:\n\n- Create a temporary table (v1 CTE) to find the first login date for each player using the MIN function grouped by pl
# Approach\n1. Identify First Login Date:\n\n- Create a temporary table (v1 CTE) to find the first login date for each player using the MIN function grouped by player_id.\n 2. Calculate Consecutive Logins:\n\n- Use the main query to join the v1 CTE with the original Activity table (a alias).\n- Use the DATEDIFF function to calculate the difference in days between the first login date (first_login) and subsequent login dates (event_date).\n- Apply a CASE statement to count the number of consecutive logins where the difference is 1 day.\n\n3. Calculate Fraction:\n\n- Use the ROUND function to round the result to 2 decimal places.\n- Calculate the fraction by dividing the sum of consecutive logins by the count of distinct player IDs in the v1 CTE.\n# Code\n```\nWITH v1 AS(\nSELECT player_id, MIN(event_date) AS first_login\nFROM activity\nGROUP BY player_id\n)\n\nSELECT ROUND(\n    CAST(SUM(CASE WHEN DATEDIFF(day, first_login, event_date) = 1 THEN 1 ELSE 0 END) AS float)\n    / COUNT(DISTINCT v1.player_id), 2) AS fraction\nFROM v1\nJOIN activity AS a ON a.player_id = v1.player_id\n```
['MS SQL Server']