Game Play Analysis IV: Sequential Login and Consecutive Days

This challenge focuses on analyzing user behavior by identifying players who log in on consecutive days. Understanding sequential login patterns is crucial for game developers to measure player engagement, identify retention issues, and tailor in-game events or rewards.

Problem Description

Given a table Logins that records each player's login events, your task is to find players who logged in on at least two consecutive days. You need to count how many such players exist.

Requirements:

• Identify login events that occur on consecutive days for the same player.
• A player is considered to have logged in on consecutive days if there exists at least one pair of login dates for that player where the second login date is exactly one day after the first login date.
• Your final output should be a single number representing the total count of distinct players who have logged in on consecutive days.

Expected Behavior:

• If a player logs in on Day 1, Day 2, and Day 3, this counts as one player with consecutive logins (Day 1 -> Day 2, and Day 2 -> Day 3). You only count the player once.
• If a player logs in on Day 1 and Day 3, but not Day 2, this does not count as consecutive logins.
• If a player logs in multiple times on the same day, this only counts as one login for that day.

Edge Cases:

• A player with only one login event cannot have consecutive logins.
• An empty Logins table should result in a count of 0.

Examples

Example 1:

Input:
Logins Table:
| player_id | event_date |
|-----------|------------|
| 1         | 2020-01-01 |
| 1         | 2020-01-02 |
| 2         | 2020-01-01 |
| 3         | 2020-01-01 |
| 3         | 2020-01-03 |
| 1         | 2020-01-03 |

Output:
1

Explanation:
Player 1 logged in on 2020-01-01 and 2020-01-02, which are consecutive days.
Player 2 has only one login.
Player 3 logged in on 2020-01-01 and 2020-01-03, which are not consecutive days.
Therefore, only Player 1 satisfies the condition, resulting in a count of 1.

Example 2:

Input:
Logins Table:
| player_id | event_date |
|-----------|------------|
| 1         | 2020-01-01 |
| 1         | 2020-01-02 |
| 1         | 2020-01-03 |
| 2         | 2020-01-02 |
| 2         | 2020-01-03 |
| 2         | 2020-01-04 |

Output:
2

Explanation:
Player 1 logged in on 2020-01-01, 2020-01-02, and 2020-01-03. This includes consecutive days (01-01 to 01-02 and 01-02 to 01-03).
Player 2 logged in on 2020-01-02, 2020-01-03, and 2020-01-04. This includes consecutive days (01-02 to 01-03 and 01-03 to 01-04).
Both players satisfy the condition, so the count is 2.

Example 3: (Edge Case - No Consecutive Logins)

Input:
Logins Table:
| player_id | event_date |
|-----------|------------|
| 1         | 2020-01-01 |
| 2         | 2020-01-01 |
| 3         | 2020-01-05 |

Output:
0

Explanation:
No player has logged in on two days that are exactly one day apart.

Constraints

• The Logins table contains at least 1 row and at most 10^6 rows.
• player_id is an integer.
• event_date is a date string in the format 'YYYY-MM-DD'.
• The date values are within a reasonable range (e.g., 2020-01-01 to 2020-12-31).
• The solution should be efficient, ideally running within a few seconds for the given constraints.

Notes

• You may need to consider how to handle duplicate event_date entries for the same player_id. The problem implies we are interested in unique login days per player.
• Think about how to compare dates and calculate the difference between them.
• The core of this problem is finding pairs of login dates for each player where the difference is exactly one day.