We are given a table with details of login_id, user_name and login_date. This data is from an e commerce portal where we want to know if a user logged in 3 times or more in a day.
We can see that Stewart logged in 3 times on Feb 16 hence that is our expected result.
Idea:
If the user name in current row is equal to the user name in the next row (LEAD) AND user name is current row is equal to the user name in the second next row (LEAD) AND same condition for login date, then that would be the user name we are looking for
This is how we can accomplish it in a query
You can see here we are using LEAD(user_name,1) for the next row and LEAD(user_name,2) for the next to next row.
Now, since the hard part is already completed, it is a matter of using WHERE to choose everything in the repeat_user column that is NOT NULL to get our answer.
Same result can also be obtained by using another idea...by selecting 1 row above and 1 row below the current row and matching user name
Like this...
No comments:
Post a Comment