Using FILTER in a window (OVER)

filter-over.sql

/*
    Using FILTER in a window (OVER)
    
    PostgreSQL version: 16.2
    
    Bill Wallis, 2025-05-12
*/

drop table if exists events;
create table events (
    event_id int,
    event_ts timestamp,
    event_name varchar,
    user_id int
);
insert into events
    with recursive e(id, ts) as (
            select 1, current_timestamp - interval '7 days'
        union all
            select id + 1, ts + make_interval(secs:=random() * 1000)
            from e
            where ts < current_timestamp
    )

    select
        id,
        ts,
        case
            when random() < 0.3 then 'login'
            when random() < 0.6 then 'logout'
            when random() < 0.8 then 'login failed'
                                else 'password reset'
        end,
        round(10 * random())
    from e
    order by id
;


select
    *,

    /* FILTER with an aggregate function in a window */
    event_id is not distinct from (
        min(event_id)
        filter (where event_name = 'login')
        over (
            partition by user_id, event_ts::date
            order by event_id
        )
    ) as is_first_login,

    /* Still use a conditional for "window-only" functions */
    case when event_name = 'login failed'
        then row_number() over (
            partition by user_id, event_ts::date, event_name
            order by event_id
        )
    end as login_failed_occurrence

from events
order by event_id
;