Sequential Logins

The "Consecutive Logins" question in an SQL interview is designed to assess your skills in crafting complex SQL queries. In this scenario, you might be provided with a table containing user IDs, login dates, and possibly other relevant details for each entry. Your task could involve determining which users logged in on consecutive days for a given duration, such as identifying those who logged in for three or more consecutive days. To tackle this type of problem, start by understanding the dataset, then pinpoint the column that will help you calculate consecutive logins (e.g., login_date). You might also need to convert the date into a workable format. Next, you would perform calculations to determine the differences between each user's consecutive login dates. Using these differences, you would then identify users who logged in for the specified number of consecutive days.

Assuming you have a table named logins that contains user_id and login_date , you can execute the following query: SELECT user_id , MIN ( login_date ) , MAX ( login_date ) FROM ( SELECT user_id , login_date , DATEDIFF ( day , ROW_NUMBER ( ) OVER ( PARTITION BY user_id ORDER BY login_date ) , login_date ) as grp FROM logins ) as SubQueryAlias GROUP BY user_id , grp HAVING COUNT ( * ) >= 3 This query first assigns a number to each user's login dates. The DATEDIFF function in the subquery is crucial, as it calculates the difference between the login date and the row number. If this difference remains constant for a set of dates, it indicates that those dates are consecutive. Using this grp , you can group all consecutive dates together and select only those series that have a count of three or more.

It is assumed that the table user_logins exclusively records unique daily logins for each user. WITH dates AS ( SELECT user_id , login_date , CASE WHEN LAG ( login_date , 1 ) OVER ( PARTITION BY user_id ORDER BY login_date ) = DATE_SUB ( login_date , INTERVAL 1 DAY ) THEN 0 ELSE 1 END AS is_new_streak FROM user_logins ) , streaks AS ( SELECT user_id , login_date , SUM ( is_new_streak ) OVER ( PARTITION BY user_id ORDER BY login_date ) AS streak_id FROM dates ) , long_streaks AS ( SELECT user_id , COUNT ( * ) AS streak_length FROM streaks GROUP BY user_id , streak_id HAVING streak_length >= 3 ) SELECT COUNT ( DISTINCT user_id ) FROM long_streaks ; This query will allow you to determine the number of users who have experienced at least one "streak" consisting of 3 or more consecutive logins. It is important to note that the LAG function retrieves the result from the preceding row within a specified PARTITION . This approach is somewhat intricate, utilizing common table expressions to form temporary views for our calculations. Simpler methods may be available depending on the specific features of your dataset.