🐘PostgreSQL ❗ Beats 99.95% ❗ Simple solution with FIRST_VALUE window function
Approach\n\nThis SQL query calculates the fraction of players who have a difference of exactly 1 day between their first and subsequent event dates, rounded to 
# Approach\n\nThis SQL query calculates the fraction of players who have a difference of exactly 1 day between their first and subsequent event dates, rounded to two decimal places.\n\n1. **Subquery (`stb`)**:\n   - The subquery calculates the difference in days (`data_diff`) between each `event_date` and the first `event_date` for each player.\n   - This is achieved by using the window function `FIRST_VALUE(event_date) OVER (PARTITION BY player_id ORDER BY event_date)`.\n   - The result of this subquery is a table where each row contains a `player_id` and the difference in days (`data_diff`) from their first event date.\n\n2. **Main Query**:\n   - The main query calculates the fraction of players who have at least one event with a `data_diff` of exactly 1.\n   - `COUNT(player_id) FILTER (WHERE data_diff = 1)` counts the number of events where `data_diff` is 1.\n   - `COUNT(DISTINCT player_id)::numeric` counts the total number of distinct players.\n   - The division `COUNT(player_id) FILTER (WHERE data_diff = 1) / COUNT(DISTINCT player_id)::numeric` calculates the fraction of players.\n   - The `ROUND` function rounds this fraction to two decimal places.\n\n# Code\n```sql\nSELECT ROUND(COUNT(player_id) FILTER ( WHERE data_diff = 1 )\n                 / COUNT(DISTINCT player_id)::numeric, 2) fraction\nFROM (SELECT player_id,\n             event_date - FIRST_VALUE(event_date) OVER (PARTITION BY player_id\n                 ORDER BY\n                     event_date) data_diff\n      FROM Activity) stb\n```
['PostgreSQL']