✅Think like SQL Engine💻🔥Dynamic Solution Best practices | Easy to understand
\n\n# Approach\n\nThe query calculates the fraction of players who have at least 2 consecutive days of activity.\n\nThe subquery (select , datediff(day, min(eve
\n\n# Approach\n\nThe query calculates the fraction of players who have at least 2 consecutive days of activity.\n\nThe subquery (select *, datediff(day, min(event_date)over(partition by player_id order by player_id, event_date asc), event_date) +1 -row_number()over(partition by player_id order by player_id asc) rw from activity) calculates the number of consecutive days of activity for each player. The datediff() function calculates the difference in days between two dates. The over() clause partitions the data by player_id and orders the data by event_date in ascending order. The row_number() function assigns a row number to each row within each partition. The rw column is the difference between the number of days between the current date and the minimum event_date for the player, and the current row number.\n\nThe where rw = 0 clause filters the results to only include players who have at least 2 consecutive days of activity. The group by player_id clause aggregates the data by player_id.\n\nThe main query select ROUND((count(*) + 0.00)/ (select count(distinct player_id) from activity), 2) as fraction from (select player_id, count(*) consecutive_days from (select *, datediff(day, min(event_date)over(partition by player_id order by player_id, event_date asc), event_date) +1 -row_number()over(partition by player_id order by player_id asc) rw from activity) c where rw = 0 group by player_id) a where consecutive_days >= 2 calculates the fraction of players who have at least 2 consecutive days of activity. The count() function counts the number of rows in the subquery. The distinct keyword ensures that each player is only counted once. The round() function rounds the result to 2 decimal places.\n\nThe final result is the fraction of players who have at least 2 consecutive days of activity.\n\n# Code\n```\n/* Write your T-SQL query statement below */\n\nselect ROUND((count(*) + 0.00)/ (select count(distinct player_id) from activity), 2) as fraction\nfrom (\n    select player_id, count(*) consecutive_days\n    from (\n        select *, \n            datediff(day, min(event_date)over(partition by player_id order by player_id, event_date asc), event_date) +1 -row_number()over(partition by player_id order by player_id asc) rw\n        from activity\n    ) c\n    where rw = 0\n    group by player_id\n) a\nwhere consecutive_days >= 2 \n```
['MS SQL Server']