Users With 3 Consecutive Logins

Users With 3 Consecutive Logins

Hard Programming Interview DBMS 45 views
Explanation Complexity

Problem Statement

Table: Logins(user_id, day). day is a date. Find users who logged in on 3 consecutive days.

Input Format

SQL tables are described in the question.

Output Format

Write the SQL query.

Input / Output Format

Input Format
SQL tables are described in the question.
Output Format
Write the SQL query.

Example Solution (Public)

Programming Interview 
WITH x AS (
  SELECT user_id, day,
         ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY day) AS rn
  FROM Logins
), g AS (
  SELECT user_id, day, rn,
         DATEADD(day, -rn, day) AS grp
  FROM x
)
SELECT DISTINCT user_id
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Official Solution Code

WITH x AS (
  SELECT user_id, day,
         ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY day) AS rn
  FROM Logins
), g AS (
  SELECT user_id, day, rn,
         DATEADD(day, -rn, day) AS grp
  FROM x
)
SELECT DISTINCT user_id
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.