Find users who logged in on at least 2 consecutive days.

Problem Statement

<p>Find <code>users</code> who logged in on at least 2 consecutive days.</p>

Examples

Input: logins table: +---------+------------+ | user_id | login_date | +---------+------------+ | 1 | 2024-01-01 | | 1 | 2024-01-02 | | 1 | 2024-01-04 | | 2 | 2024-01-05 | | 2 | 2024-01-06 | | 2 | 2024-01-07 | | 3 | 2024-01-10 | +---------+------------+

Output: +---------+ | user_id | +---------+ | 1 | | 2 | +---------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Determine which tables contain the required columns and relate them using INNER JOIN on the corresponding foreign/primary keys. 💡 Hint 2: Write out the ON condition matching key fields (e.g. ON a.key = b.key) and apply any filtering in the WHERE clause. 💡 Hint 3: Select only the requested columns in the SELECT clause with clear aliases if needed: SELECT ... FROM ... INNER JOIN ... ON ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Consecutive Days Detection", we query the relational database engine using declarative SQL. The goal is to find users who logged in on at least 2 consecutive days. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply table joins to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT DISTINCT l1.user_id FROM logins l1 JOIN logins l2 ON l1.user_id = l2.user_id AND l1.login_date = l2.login_date - INTERVAL '1 day';

Complexity Analysis

Time Complexity O(N * M) worst-case, O(N + M) with hash/merge join on indexed keys.

Space Complexity O(N) for join buffer and result set.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Consecutive Days Detection

Hard

Find users who logged in on at least 2 consecutive days.

Example Scenarios

1Example 1

Input:

logins table

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-04
2	2024-01-05
2	2024-01-06
2	2024-01-07
3	2024-01-10

Output:

user_id
1
2

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.