PostgreSQL solving
Intuition\n Describe your first thoughts on how to solve this problem. \nThe main ideas for solving this problem:\n1. Find date of first login for each player (
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nThe main ideas for solving this problem:\n1. Find date of first login for each player (now we have pairs *player_id*: *first_login_date*)\n2. Add one day to this date (pairs *player_id*: *next_day_after_first_login_date*)\n3. Count number of these dates, existed in the source data (if any pair exists in db -> this user logged in again on the day after the day they first logged in)\n4. Count target fraction of users by diving calculated number by the total number of unique players.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\nFollow the steps had described earlier:\n1. Subquery to find pairs of *player_id* and first date of their login:\n```postgresql []\nSELECT player_id, MIN(event_date)\nFROM Activity\nGROUP BY player_id\n```\n2. For return the next day after first user login instead of first login date, modify previous query as follows:\n```postgresql []\nSELECT player_id, MIN(event_date) + 1\nFROM Activity\nGROUP BY player_id\n```\n3. Now we can calculate number of users logged in again on the day after the day they first logged in as following:\n```postgresql []\nSELECT COUNT(player_id)\nFROM Activity\nWHERE (player_id, event_date) IN (\n    SELECT player_id, MIN(event_date) + 1\n    FROM Activity\n    GROUP BY player_id\n)\n```\n4. But we don\'t want to find number of this users itself. So we need to modify this query. We need to find full number of unique players. We can do it by subquery:\n```postgresql []\nSELECT COUNT(DISTINCT player_id)\nFROM Activity\n```\nFor calculating the final result insert this subquery into final query. Add **ROUND()** function in according to task condition:\n```postgresql []\nSELECT ROUND(\n    1.0 * COUNT(player_id) / \n    (SELECT COUNT(DISTINCT player_id)\n    FROM Activity), 2) AS fraction\nFROM Activity\nWHERE (player_id, event_date) IN (\n    SELECT player_id, MIN(event_date) + 1\n    FROM Activity\n    GROUP BY player_id\n)\n```\n\n\n\n# Code\n```postgresql []\nSELECT ROUND(\n    1.0 * COUNT(player_id) / \n    (SELECT COUNT(DISTINCT player_id)\n    FROM Activity), 2) AS fraction\nFROM Activity\nWHERE (player_id, event_date) IN (\n    SELECT player_id, MIN(event_date) + 1\n    FROM Activity\n    GROUP BY player_id\n)\n```
['PostgreSQL']