I have an event table in the form:
| User | Event | Date |
| User A | Failed Log In | ... |
| User A | Failed Log In | ... |
| User B | Failed Log In | |
| User A | Successful Log In | |
| User B | Successful Log In | |
What I would like to do is to count all the 'failed log ins' for each user since the last 'successful log in' so I can create a view in the form:
| User | Failed Log in attempts |
| User A | 2 |
| User B | 1 |
Is this possible to do in SQL? Or do I need to pull this logic out into my app?
SQL tables represent unordered sets, so there is no "last" or "before" unless a column specifies the ordering. So, this answer assumes some sort of eventdatetime column with that information, although an auto incrementing id would work just as well (and possibly better).
I think this does what you want:
select e.user, count(*) as NumFailedAttempts
from events e
where e.event = 'Failed Log In' and
e.eventdatetime > (select max(e2.eventdatetime)
from events e2
where e2.user = e.user and e2.event = 'Successful Log In'
);
If you want to include zeros (those with no failed logins), then you can left join this to the users table.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments