✅ Beats 90% | Easiest Explanation | Why DATE BEFORE first login?
Steps: \n1. First Login Date for Each Player\n2. Players Logging In on the Day Before Their First Login\n3. Fraction of Players Logging In on the Day Before The
# Steps: \n1. `First Login Date` for Each Player\n2. Players Logging In on the Day _Before_ Their First Login\n3. `Fraction` of Players Logging In on the Day Before Their First Login\n\n# Code\n```\n# Write your MySQL query statement below\nSELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) as fraction\nFROM Activity\nWHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))\nIN (SELECT player_id, MIN(event_date) AS first_login FROM ACTIVITY GROUP BY player_id)\n```\n\n## Step 1: Find the First Login Date for Each Player\n```\nSELECT \n    player_id,\n    MIN(event_date) AS first_login\nFROM \n    Activity\nGROUP BY \n    player_id;\n```\n\n## Result:\n\n```\n+-----------+------------+\n| player_id | first_login |\n+-----------+------------+\n| 1         | 2016-03-01 |\n| 2         | 2017-06-25 |\n| 3         | 2016-03-02 |\n+-----------+------------+\n```\n\n## Step 2: Identify Players Logging In on the Day Before Their First Login\n\n```\nSELECT \n    player_id,\n    DATE_SUB(first_login, INTERVAL 1 DAY) \nFROM \n    (\n        -- Subquery: First login date for each player\n        SELECT \n            player_id,\n            MIN(event_date) AS first_login\n        FROM \n            Activity\n        GROUP BY \n            player_id\n    ) a;\n```\n\n## Result:\n\n```\n+-----------+------------------------+\n| player_id | day_after_first_login |\n+-----------+------------------------+\n| 1         | 2016-02-29             |\n| 2         | 2017-06-24             |\n| 3         | 2016-03-01             |\n+-----------+------------------------+\n```\n\n## Step 3: Count the Fraction of Players Logging In on the Day Before Their First Login\n\n```\nSELECT \n    ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) as fraction\nFROM \n    Activity\nWHERE \n    (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))\n    IN \n    (\n        -- Subquery: Players and their first login date\n        SELECT \n            player_id,\n            MIN(event_date) AS first_login\n        FROM \n            Activity\n        GROUP BY \n            player_id\n    );\n```\n\n## Result:\n\n```\n+-----------+\n| fraction  |\n+-----------+\n| 0.33      |\n+-----------+\n```\n\n---\n\n# Detailed breakdown of #step 2: \n\nActivity Table: \n\n```\n+-----------+-----------+------------+--------------+\n| player_id | device_id | event_date | games_played |\n+-----------+-----------+------------+--------------+\n| 1         | 2         | 2016-03-01 | 5            |\n| 1         | 2         | 2016-03-02 | 6            |\n| 2         | 3         | 2017-06-25 | 1            |\n| 3         | 1         | 2016-03-02 | 0            |\n| 3         | 4         | 2018-07-03 | 5            |\n+-----------+-----------+------------+--------------+\n```\n\nTo make it even simple, run the query:\n\n```\nSELECT\n    a.player_id,\n    a.event_date,\n    DATE_SUB(b.first_login, INTERVAL 1 DAY) AS day_before_first_login\nFROM\n    Activity a\nJOIN (\n    SELECT player_id, MIN(event_date) AS first_login\n    FROM Activity\n    GROUP BY player_id\n) b ON a.player_id = b.player_id;\n```\n\nIt performs the same thing as: \n\n```\nWHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))\nIN (SELECT player_id, MIN(event_date) AS first_login FROM ACTIVITY GROUP \n```\n\nResult: \n\n```\n+-----------+------------+------------------------+\n| player_id | event_date | day_before_first_login |\n+-----------+------------+------------------------+\n| 1         | 2016-03-01 | 2016-02-29             |\n| 1         | 2016-03-02 | 2016-03-01             |\n| 2         | 2017-06-25 | 2017-06-24             |\n| 3         | 2016-03-02 | 2016-03-01             |\n| 3         | 2018-07-03 | 2018-07-02             |\n+-----------+------------+------------------------+\n```\n\nHere we can see clearly that when we subtract a day from every first login dates. For `player 1` **there\'s a date common in _event date_ and _day_before_first_login column_. That is `2016-03-01`**\n\nWhich proves that the player logged in on 2016-03-01 and 2016-03-02, making it two consecutive days.\n\n---- \n_Is it necessary to use Date_sub? Can\'t we use Date_Add?_\n\n> If you just replace Date_Sub with Date_Add, it\'ll fetch you result as 0 because we are grouping by min(event_date) which is 2016-03-01, add 1 to it and you don\'t have a date to compare.\n\nWhat if we remove min(event_date)?\n
['MySQL']