SQL's RANGE BETWEEN alternative

ddl.sql

/*
    SQL's RANGE BETWEEN alternative

    Bill Wallis, 2025-08-02
*/

drop table if exists events;
create table events (
    event_id int primary key,
    user_id int not null,
--     event_datetime datetime not null,  /* SQL Server (T-SQL) */
--     event_datetime timestamp not null, /* PostgreSQL */
    event_type varchar(50) not null
);
insert into events (event_id, user_id, event_datetime, event_type)
values
    (1, 1, '2024-01-01 11:43:20', 'login'),
    (2, 1, '2024-01-01 12:12:10', 'logout'),
    (3, 5, '2024-01-01 12:13:14', 'login'),
    (4, 2, '2024-01-01 12:18:04', 'login'),
    (5, 4, '2024-01-01 12:59:43', 'logout'),
    (6, 4, '2024-01-01 13:09:52', 'login'),
    (7, 5, '2024-01-01 14:09:54', 'logout'),
    (8, 3, '2024-01-01 14:21:04', 'logout'),
    (9, 4, '2024-01-03 03:10:07', 'login'),
    (10, 3, '2024-01-03 03:32:27', 'login'),
    (11, 1, '2024-01-03 03:56:32', 'login failed'),
    (12, 5, '2024-01-03 04:45:28', 'login'),
    (13, 5, '2024-01-03 04:54:16', 'login failed'),
    (14, 2, '2024-01-03 05:04:36', 'login failed'),
    (15, 4, '2024-01-03 05:11:35', 'login failed'),
    (16, 1, '2024-01-03 10:11:49', 'login'),
    (17, 5, '2024-01-03 10:30:36', 'login'),
    (18, 3, '2024-01-03 11:59:35', 'login'),
    (19, 4, '2024-01-03 12:31:27', 'login'),
    (20, 2, '2024-01-03 15:24:48', 'logout'),
    (21, 4, '2024-01-03 15:45:28', 'logout'),
    (22, 1, '2024-01-03 17:24:32', 'logout'),
    (23, 3, '2024-01-03 17:43:41', 'logout'),
    (24, 4, '2024-01-03 23:39:02', 'logout'),
    (25, 2, '2024-01-03 23:56:14', 'logout'),
    (26, 1, '2024-01-04 00:25:02', 'logout'),
    (27, 3, '2024-01-04 00:48:21', 'logout'),
    (28, 2, '2024-01-04 22:02:48', 'login failed'),
    (29, 3, '2024-01-04 22:36:27', 'login'),
    (30, 5, '2024-01-04 23:03:00', 'login'),
    (31, 1, '2024-01-04 23:09:47', 'login failed'),
    (32, 5, '2024-01-05 00:08:28', 'login failed'),
    (33, 4, '2024-01-05 00:11:45', 'login failed'),
    (34, 2, '2024-01-05 00:24:27', 'login'),
    (35, 1, '2024-01-05 00:24:52', 'login'),
    (36, 2, '2024-01-05 20:11:41', 'login'),
    (37, 3, '2024-01-05 21:06:39', 'login'),
    (38, 1, '2024-01-05 21:21:15', 'login'),
    (39, 5, '2024-01-05 21:29:54', 'login'),
    (40, 4, '2024-01-05 21:49:07', 'login'),
    (41, 5, '2024-01-06 04:00:32', 'logout'),
    (42, 2, '2024-01-06 04:16:27', 'logout'),
    (43, 1, '2024-01-06 05:40:40', 'logout'),
    (44, 4, '2024-01-06 06:44:23', 'logout'),
    (45, 1, '2024-01-09 15:54:53', 'logout'),
    (46, 2, '2024-01-09 16:47:32', 'logout'),
    (47, 5, '2024-01-09 17:22:04', 'logout'),
    (48, 4, '2024-01-09 17:25:13', 'logout'),
    (49, 3, '2024-01-11 22:41:19', 'login'),
    (50, 5, '2024-01-11 23:40:46', 'login'),
    (51, 4, '2024-01-12 12:42:19', 'login'),
    (52, 2, '2024-01-12 13:06:03', 'logout'),
    (53, 3, '2024-01-12 13:50:17', 'login'),
    (54, 5, '2024-01-12 14:12:24', 'logout'),
    (55, 1, '2024-01-12 14:17:18', 'login'),
    (56, 1, '2024-01-12 14:23:33', 'logout'),
    (57, 3, '2024-01-12 15:07:42', 'logout'),
    (58, 4, '2024-01-12 15:30:40', 'logout'),
    (59, 5, '2024-01-13 04:02:55', 'login'),
    (60, 1, '2024-01-13 04:10:23', 'login'),
    (61, 3, '2024-01-13 04:25:18', 'login'),
    (62, 4, '2024-01-13 04:37:22', 'login'),
    (63, 2, '2024-01-13 05:27:28', 'login'),
    (64, 3, '2024-01-13 10:24:47', 'login failed'),
    (65, 4, '2024-01-13 10:54:21', 'login failed'),
    (66, 5, '2024-01-13 11:15:32', 'login'),
    (67, 1, '2024-01-13 11:41:47', 'login'),
    (68, 2, '2024-01-13 12:24:50', 'login failed'),
    (69, 1, '2024-01-13 16:18:58', 'logout'),
    (70, 2, '2024-01-13 16:32:22', 'logout'),
    (71, 5, '2024-01-13 16:39:21', 'logout'),
    (72, 3, '2024-01-13 17:36:23', 'logout'),
    (73, 4, '2024-01-13 23:03:15', 'logout'),
    (74, 3, '2024-01-13 23:43:37', 'logout'),
    (75, 5, '2024-01-14 00:24:56', 'logout'),
    (76, 2, '2024-01-14 00:56:28', 'logout'),
    (77, 3, '2024-01-14 22:27:54', 'login'),
    (78, 1, '2024-01-14 23:23:52', 'login'),
    (79, 2, '2024-01-14 23:42:10', 'login'),
    (80, 4, '2024-01-15 00:39:55', 'login'),
    (81, 5, '2024-01-15 20:02:11', 'login'),
    (82, 4, '2024-01-15 20:22:53', 'login'),
    (83, 1, '2024-01-15 22:04:03', 'login'),
    (84, 3, '2024-01-15 22:18:03', 'login'),
    (85, 2, '2024-01-16 05:29:32', 'logout'),
    (86, 1, '2024-01-16 05:44:50', 'logout'),
    (87, 4, '2024-01-16 06:04:09', 'logout'),
    (88, 3, '2024-01-16 06:40:24', 'logout'),
    (89, 4, '2024-01-19 15:40:01', 'logout'),
    (90, 3, '2024-01-19 16:58:04', 'logout'),
    (91, 1, '2024-01-19 17:29:51', 'logout'),
    (92, 5, '2024-01-19 17:46:05', 'logout'),
    (93, 4, '2024-01-21 21:43:15', 'login'),
    (94, 5, '2024-01-21 22:55:02', 'login'),
    (95, 3, '2024-01-21 22:59:24', 'login'),
    (96, 1, '2024-01-21 23:19:22', 'login'),
    (97, 2, '2024-01-22 12:07:43', 'login failed'),
    (98, 2, '2024-01-22 12:25:40', 'login'),
    (99, 3, '2024-01-22 13:20:04', 'logout'),
    (100, 3, '2024-01-22 13:22:37', 'login'),
    (101, 3, '2024-01-22 13:32:24', 'password reset'),
    (102, 5, '2024-01-22 13:40:45', 'login failed'),
    (103, 5, '2024-01-22 13:40:47', 'password reset'),
    (104, 4, '2024-01-22 13:59:56', 'login failed'),
    (105, 1, '2024-01-22 14:01:48', 'login failed'),
    (106, 1, '2024-01-22 14:05:17', 'password reset'),
    (107, 1, '2024-01-22 14:18:06', 'logout'),
    (108, 3, '2024-01-22 14:38:17', 'login failed'),
    (109, 5, '2024-01-22 14:41:34', 'login'),
    (110, 5, '2024-01-22 14:44:17', 'logout'),
    (111, 1, '2024-01-22 14:54:08', 'login'),
    (112, 2, '2024-01-22 15:15:27', 'logout'),
    (113, 2, '2024-01-23 04:25:41', 'login'),
    (114, 5, '2024-01-23 04:35:16', 'login'),
    (115, 3, '2024-01-23 04:45:48', 'login'),
    (116, 1, '2024-01-23 05:08:56', 'login'),
    (117, 3, '2024-01-23 10:47:33', 'login'),
    (118, 1, '2024-01-23 11:20:47', 'login'),
    (119, 5, '2024-01-23 11:30:54', 'login'),
    (120, 4, '2024-01-23 11:33:54', 'login'),
    (121, 3, '2024-01-23 15:50:52', 'logout'),
    (122, 5, '2024-01-23 16:16:55', 'logout'),
    (123, 4, '2024-01-23 16:31:52', 'logout'),
    (124, 1, '2024-01-23 16:33:56', 'logout'),
    (125, 2, '2024-01-23 17:07:55', 'logout'),
    (126, 5, '2024-01-24 00:20:07', 'logout'),
    (127, 1, '2024-01-24 00:21:34', 'logout'),
    (128, 4, '2024-01-24 01:25:53', 'logout'),
    (129, 1, '2024-01-24 22:49:25', 'login'),
    (130, 4, '2024-01-25 00:41:53', 'login'),
    (131, 5, '2024-01-25 20:11:19', 'login'),
    (132, 4, '2024-01-25 21:26:59', 'login'),
    (133, 2, '2024-01-25 22:29:39', 'login'),
    (134, 4, '2024-01-26 05:02:01', 'logout'),
    (135, 1, '2024-01-26 05:25:49', 'logout'),
    (136, 3, '2024-01-26 05:49:49', 'logout'),
    (137, 5, '2024-01-26 06:21:59', 'logout'),
    (138, 2, '2024-01-26 06:34:34', 'logout'),
    (139, 3, '2024-01-29 15:14:13', 'logout'),
    (140, 4, '2024-01-29 15:37:13', 'logout'),
    (141, 1, '2024-01-29 15:44:01', 'logout'),
    (142, 4, '2024-01-31 21:16:11', 'login'),
    (143, 2, '2024-01-31 21:58:26', 'login'),
    (144, 5, '2024-01-31 22:07:22', 'login'),
    (145, 3, '2024-01-31 22:07:35', 'login'),
    (146, 1, '2024-01-31 22:26:01', 'login')
;

postgres.sql

select
    event_id,
    user_id,
    event_datetime,
    event_type,
    count(*) over (
        partition by user_id
        order by event_datetime
        range between interval '1 hour' preceding
                  and interval '1 hour' following
    ) as count_events_within_hour
from events
order by event_id

tsql.sql

/* Correlated Subquery */
select
    event_id,
    user_id,
    event_datetime,
    event_type,
    (
        select count(*)
        from events as events_i
        where 1=1
            and events.user_id = events_i.user_id
            and events_i.event_datetime between dateadd(hour, -1, events.event_datetime)
                                            and dateadd(hour,  1, events.event_datetime)
    ) as count_events_within_hour
from events
order by event_id
;

/* LEFT JOIN and GROUP BY */
select
    events.event_id,
    max(events.user_id) as user_id,
    max(events.event_datetime) as event_datetime,
    max(events.event_type) as event_type,
    count(*) as count_events_within_hour
from events
    left join events as events_within_hour
        on  events.user_id = events_within_hour.user_id
        and events_within_hour.event_datetime between dateadd(hour, -1, events.event_datetime)
                                                  and dateadd(hour,  1, events.event_datetime)
group by events.event_id
order by events.event_id
;

/* LEFT JOIN and GROUP BY (alternative) */
select
    events.event_id,
    events.user_id,
    events.event_datetime,
    events.event_type,
    count(*) as count_events_within_hour
from events
    left join events as events_within_hour
        on  events.user_id = events_within_hour.user_id
        and events_within_hour.event_datetime between dateadd(hour, -1, events.event_datetime)
                                                  and dateadd(hour,  1, events.event_datetime)
group by
    events.event_id,
    events.user_id,
    events.event_datetime,
    events.event_type
order by events.event_id
;

This is part of the following LinkedIn post:

• https://www.linkedin.com/posts/bill-wallis_sqlwithbill-sql-activity-7358401152666718209-shP6