Suspicious login activity 🤔

Tip

Solution to the following problem:

• suspicious-login-activity.md

Result Set

Regardless of the database, the result set should look like:

user_id	consecutive_failures
1	7
3	8

Expand for the DDL

solution(user_id, consecutive_failures) as (
    values
        (1, 7),
        (3, 8)
)

Solution

Some SQL solutions per database are provided below.

DuckDB

with event_groups as (
    select
        *,
        (0
            + row_number() over (partition by user_id             order by event_id)
            - row_number() over (partition by user_id, event_type order by event_id)
        ) as event_group
    from events
)

select
    user_id,
    count(*) as consecutive_failures
from event_groups
where event_type = 'login failed'
group by user_id, event_group
having consecutive_failures >= 5
qualify consecutive_failures = max(consecutive_failures) over (partition by user_id)
order by user_id

SQL Server

with

event_groups as (
    select
        *,
        (0
            + row_number() over (partition by user_id             order by event_id)
            - row_number() over (partition by user_id, event_type order by event_id)
        ) as event_group
    from events
),

consecutive_failures as (
    select
        user_id,
        count(*) as consecutive_failures,
        max(count(*)) over (partition by user_id) as max_consecutive_failures
    from event_groups
    where event_type = 'login failed'
    group by user_id, event_group
    having count(*) >= 5
)

select
    user_id,
    consecutive_failures
from consecutive_failures
where 1=1
    and consecutive_failures >= 5
    and consecutive_failures = max_consecutive_failures
order by user_id