SQL's ASOF JOIN alternative

ddl.sql

drop table if exists users;
create table users (
    user_id int primary key,
    username varchar(50) not null,
    last_review_ts datetime not null
);
insert into users (user_id, username, last_review_ts)
values
    (1, 'Alex', '2023-12-01 10:00:00'),
    (2, 'Blake', '2024-01-20 21:30:00'),
    (3, 'Charlie', '2024-01-22 15:45:00'),
    (4, 'Dylan', '2024-02-02 08:15:00'),
    (5, 'Esra', '2024-01-15 10:30:00'),
    (6, 'Finn', '2024-01-25 12:00:00')
;

drop table if exists events;
create table events (
    event_id int primary key,
    user_id int not null,
    event_datetime datetime not null,
    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')
;

duckdb.sql

/* ASOF join */
select
    users.user_id,
    users.last_review_ts,
    events.event_id,
    events.event_datetime,
    events.event_type
from users
    asof left join events
        on  users.user_id = events.user_id
        and events.event_type = 'login failed'
        and users.last_review_ts >= events.event_datetime
order by users.user_id
;

/* Correlated Subquery (lateral join) */
select
    users.user_id,
    users.last_review_ts,
    latest_events.event_id,
    latest_events.event_datetime,
    latest_events.event_type
from users
    left join lateral (
        select *
        from events
        where events.user_id = users.user_id
          and events.event_type = 'login failed'
          and events.event_datetime <= users.last_review_ts
        order by events.event_datetime desc
        limit 1
    ) as latest_events on true
order by users.user_id
;

tsql.sql

/* Correlated Subquery (outer apply) */
select
    users.user_id,
    users.last_review_ts,
    latest_events.event_id,
    latest_events.event_datetime,
    latest_events.event_type
from users
    outer apply (
        select top 1 *
        from events
        where events.user_id = users.user_id
          and events.event_type = 'login failed'
          and events.event_datetime <= users.last_review_ts
        order by events.event_datetime desc
    ) as latest_events
order by users.user_id
;

/* CTE + row_number */
with latest_events as (
    select
        users.user_id,
        users.last_review_ts,
        events.event_id,
        events.event_datetime,
        events.event_type,
        row_number() over (
            partition by users.user_id
            order by events.event_datetime desc
        ) as event_rn
    from users
        left join events
            on  events.user_id = users.user_id
            and events.event_type = 'login failed'
            and events.event_datetime <= users.last_review_ts
)

select
    user_id,
    last_review_ts,
    event_id,
    event_datetime,
    event_type
from latest_events
where event_rn = 1
order by user_id
;

This is part of the following LinkedIn post:

• https://www.linkedin.com/posts/bill-wallis_sqlwithbill-sql-activity-7360230118490796033-RoZf