SQL Technique: Users Who Consecutively Logged In For Multiple Times

Working with Sql to query a database is both simple and complex but is an alluring experience for everybody. For the simple requirement it is quite simple and straight forward, but for complex scenarios you have to give a fair amount of thought before finalizing your desired query. Below we’ll walk you through some simple steps to get consecutive login rows by the users. Let’s get our feet a little wet in building Transact-Sql query for this purpose.

The Problem Statement

The system stores the login information of the authorized users in an Sql database. You’re required to query for three or more consecutive logins by the same users.

Here’s the table whose schema contains four columns (for the sake of simplicity) to store login information.

Let’s try to follow a simple approach to comprehend and build the final query. First off we find some way to find out the next and then the next to the next row.

LEAD() window function is quite handy to get column values of the adjacent rows. We’ll use this function. So let’s start with a simple query using LEAD() function and OVER() clause.

SELECT *, 
CASE WHEN UserName = LEAD(UserName) OVER (ORDER BY LoginDate)
	AND UserName = LEAD(UserName, 2) OVER (ORDER BY LoginDate)
	THEN UserName
	ELSE Null
END AS RequiredUsers
FROM User_Login

Here we want to check if the next and then the further next UserName values match the one in the current row. We need to order rows by LoginDate to fulfil consecutive records condition. Here’s the result.

You can see that RequireUsers column values successfully demonstrate consecutive logins for Lamb and John, whereas they’re null for the other cases. We’re quite close the final result.

Since we’re only interested in the three or more consecutive logins, we enclose this query as a sub-query and get our required result by using DISTINCT clause and NOT NULL condition under WHERE clause on this intermediate result. Here’s the final query.

SELECT DISTINCT  UserName 
FROM (
SELECT *, 
CASE WHEN UserName = LEAD(UserName) OVER (ORDER BY LoginDate)
	AND UserName = LEAD(UserName, 2) OVER (ORDER BY LoginDate)
	THEN UserName
	ELSE Null
END AS RequiredUsers
FROM User_Login ) X
WHERE X.RequiredUsers IS NOT NULL;

and here’s the final result.