Scribd
Upload
23 views1 page

Query+5+ +script

This document contains a SQL query and table structure to find users from a login details table who logged in consecutively 3 or more times based on their user name and login date.

Uploaded by

Denny Lauw
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
23 views1 page

Query+5+ +script

This document contains a SQL query and table structure to find users from a login details table who logged in consecutively 3 or more times based on their user name and login date.

Uploaded by

Denny Lauw
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

-- Query 5:

From the login_details table, fetch the users who logged in consecutively 3 or more
times.

--Table Structure:

drop table login_details;


create table login_details(
login_id int primary key,
user_name varchar(50) not null,
login_date date);

delete from login_details;


insert into login_details values
(101, 'Michael', current_date),
(102, 'James', current_date),
(103, 'Stewart', current_date+1),
(104, 'Stewart', current_date+1),
(105, 'Stewart', current_date+1),
(106, 'Michael', current_date+2),
(107, 'Michael', current_date+2),
(108, 'Stewart', current_date+3),
(109, 'Stewart', current_date+3),
(110, 'James', current_date+4),
(111, 'James', current_date+4),
(112, 'James', current_date+5),
(113, 'James', current_date+6);

select * from login_details;

--Solution:

select distinct repeated_names


from (
select *,
case when user_name = lead(user_name) over(order by login_id)
and user_name = lead(user_name,2) over(order by login_id)
then user_name else null end as repeated_names
from login_details) x
where x.repeated_names is not null;

You might also like