SQL's GROUPS BETWEEN alternative

ddl.sql

/*
    SQL's GROUPS BETWEEN alternative

    Bill Wallis, 2025-08-05
*/

drop table if exists trials;
create table trials (
    trial_id int,
    trial_date date,
    participant_id varchar(8) not null,
    trial_result varchar(8) not null check (trial_result in ('success', 'fail')),
    primary key (trial_id, participant_id)
);
insert into trials
values
    (1, '2024-01-02', '9023ab18', 'fail'),
    (1, '2024-01-02', '1d4056ab', 'fail'),
    (1, '2024-01-02', '88c59041', 'success'),
    (2, '2024-01-17', 'b35d437d', 'success'),
    (2, '2024-01-17', '9ffce896', 'fail'),
    (2, '2024-01-17', '8e72d937', 'success'),
    (2, '2024-01-17', 'f0801c58', 'fail'),
    (3, '2024-01-24', '586c6503', 'fail'),
    (3, '2024-01-24', '0766f7cd', 'success'),
    (3, '2024-01-24', '55420742', 'success'),
    (4, '2024-02-03', '1287f987', 'success'),
    (4, '2024-02-03', 'c32c5da5', 'fail'),
    (5, '2024-02-09', '7b99ffbc', 'success'),
    (5, '2024-02-09', 'f6108e6c', 'success')
;

postgres.sql

/* GROUPS BETWEEN */
select
    trial_id,
    trial_date,
    participant_id,
    trial_result,
    sum(case when trial_result = 'success' then 1 end) over running_trials as succeeded,
    sum(case when trial_result = 'fail'    then 1 end) over running_trials as failed
from trials
window running_trials as (
    order by trial_id
    groups unbounded preceding
)
order by
    trial_id,
    participant_id
;


/* Correlated Subquery (lateral join) */
select
    trials.trial_id,
    trials.trial_date,
    trials.participant_id,
    trials.trial_result,
    running_trials.succeeded,
    running_trials.failed
from trials
    cross join lateral (
        select
            sum(case when trial_result = 'success' then 1 end) as succeeded,
            sum(case when trial_result = 'fail'    then 1 end) as failed
        from trials as trials_i
        where trials_i.trial_id <= trials.trial_id
    ) as running_trials
order by
    trials.trial_id,
    trials.participant_id
;

tsql.sql

/* Correlated Subquery (cross apply) */
select
    trials.trial_id,
    trials.trial_date,
    trials.participant_id,
    trials.trial_result,
    running_trials.succeeded,
    running_trials.failed
from trials
    cross apply (
        select
            sum(case when trial_result = 'success' then 1 end) as succeeded,
            sum(case when trial_result = 'fail'    then 1 end) as failed
        from trials as trials_i
        where trials_i.trial_id <= trials.trial_id
    ) as running_trials
order by
    trials.trial_id,
    trials.participant_id
;


/* CTE and join */
with trial_summary as (
    select
        trial_id,
        sum(sum(case when trial_result = 'success' then 1 end)) over running_trials as succeeded,
        sum(sum(case when trial_result = 'fail'    then 1 end)) over running_trials as failed
    from trials
    group by trial_id
    window running_trials as (
        order by trial_id
        rows unbounded preceding
    )
)

select
    trials.trial_id,
    trials.trial_date,
    trials.participant_id,
    trials.trial_result,
    trial_summary.succeeded,
    trial_summary.failed
from trials
    inner join trial_summary
        on trials.trial_id = trial_summary.trial_id
order by
    trials.trial_id,
    trials.participant_id
;

This is part of the following LinkedIn post:

• https://www.linkedin.com/posts/bill-wallis_sqlwithbill-sql-activity-7359449071335415808-JXtc