clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805502 fiddles created (40741 in the last week).

CREATE TABLE users ( user_id serial PRIMARY KEY , username text NOT NULL ); INSERT INTO users (username) SELECT 'usr_'::text || g FROM generate_series(1, 1000) g; -- 1000 users CREATE TABLE log ( user_id int -- possibly with a FK to users , log_date date , payload int ); INSERT INTO log(log_date, user_id, payload) SELECT '2010-01-01'::date + g/50 -- asc dates with ~ 10 % exceptions + CASE WHEN random() > .9 THEN (random()^3 * 100)::int ELSE 0 END , 1 + trunc(random() * 1000)::int -- user_id 1 - 1000 , (random() * 1000)::int FROM generate_series(1, 100000) g; -- 100k log entries CREATE INDEX log_combo_covering_idx ON log (user_id, log_date DESC NULLS LAST, payload); -- type for simple query CREATE TYPE combo AS (log_date date, payload int);
1000 rows affected
100000 rows affected
 hidden batch(es)


-- Without table "users" -- 1a rCTE with LATERAL WITH RECURSIVE cte AS ( ( -- parentheses required SELECT user_id, log_date, payload FROM log WHERE log_date <= '2015-01-07' AND user_id > 990 -- limitfor demo ORDER BY user_id, log_date DESC NULLS LAST LIMIT 1 ) UNION ALL SELECT l.* FROM cte c CROSS JOIN LATERAL ( SELECT l.user_id, l.log_date, l.payload FROM log l WHERE l.user_id > c.user_id -- lateral reference AND l.log_date <= '2015-01-07' -- repeat condition ORDER BY l.user_id, l.log_date DESC NULLS LAST LIMIT 1 ) l ) TABLE cte ORDER BY user_id;
user_id log_date payload
991 2014-11-30 633
992 2014-12-29 451
993 2014-11-19 325
994 2014-12-22 488
995 2015-01-01 361
996 2014-12-22 436
997 2014-12-26 673
998 2015-01-05 383
999 2014-11-28 67
1000 2014-12-22 622
 hidden batch(es)


-- 1b Correlated subquery WITH RECURSIVE cte AS ( ( -- parentheses required SELECT l AS my_row -- whole row FROM log l WHERE log_date <= '2015-01-07' AND user_id > 990 -- limit for demo ORDER BY user_id, log_date DESC NULLS LAST LIMIT 1 ) UNION ALL SELECT (SELECT l -- whole row FROM log l WHERE l.user_id > (c.my_row).user_id AND l.log_date <= '2015-01-07' ORDER BY l.user_id, l.log_date DESC NULLS LAST LIMIT 1) FROM cte c WHERE (c.my_row).user_id IS NOT NULL -- note parentheses ) SELECT (my_row).* -- decompose row FROM cte WHERE (my_row).user_id IS NOT NULL ORDER BY (my_row).user_id;
user_id log_date payload
991 2014-11-30 633
992 2014-12-29 451
993 2014-11-19 325
994 2014-12-22 488
995 2015-01-01 361
996 2014-12-22 436
997 2014-12-26 673
998 2015-01-05 383
999 2014-11-28 67
1000 2014-12-22 622
 hidden batch(es)


-- With table "users" -- 2a JOIN LATERAL SELECT u.user_id, l.log_date, l.payload FROM users u CROSS JOIN LATERAL ( SELECT l.log_date, l.payload FROM log l WHERE l.user_id = u.user_id -- lateral reference AND l.log_date <= '2015-01-07' ORDER BY l.log_date DESC NULLS LAST LIMIT 1 ) l WHERE u.user_id > 990; -- limit for demo
user_id log_date payload
991 2014-11-30 633
992 2014-12-29 451
993 2014-11-19 325
994 2014-12-22 488
995 2015-01-01 361
996 2014-12-22 436
997 2014-12-26 673
998 2015-01-05 383
999 2014-11-28 67
1000 2014-12-22 622
 hidden batch(es)


-- 2b Correlated subquery SELECT user_id, (combo1).* -- note parentheses FROM ( SELECT u.user_id , (SELECT (l.log_date, l.payload)::combo FROM log l WHERE l.user_id = u.user_id AND l.log_date <= '2015-01-07' ORDER BY l.log_date DESC NULLS LAST LIMIT 1) AS combo1 FROM users u WHERE u.user_id > 990 -- for demo ) sub;
user_id log_date payload
991 2014-11-30 633
992 2014-12-29 451
993 2014-11-19 325
994 2014-12-22 488
995 2015-01-01 361
996 2014-12-22 436
997 2014-12-26 673
998 2015-01-05 383
999 2014-11-28 67
1000 2014-12-22 622
 hidden batch(es)