gap filling in off-the-shelf postgres

results.md

Schema (PostgreSQL v12)

CREATE TABLE test (ts timestamp, email varchar, title varchar);
insert into test values
('2017-01-01', 'me@me.com', 'Old title'),
('2017-01-02', 'me@me.com', null),
('2017-01-03', 'me@me.com', 'New Title'),
('2017-01-04', 'me@me.com', null),
('2017-01-05', 'me@me.com', null),
('2017-01-06', 'me@me.com', 'Newer Title'),
('2017-01-07', 'me@me.com', null),
('2017-01-08', 'me@me.com', null);

 -- The built in function coalesce is not a aggregate function, nor is variadic.
 -- It might just be a compiler construct.
 -- So we define our own version
 CREATE FUNCTION f_coalesce(a anyelement, b anyelement) RETURNS anyelement AS '
    SELECT COALESCE(a,b);
 ' LANGUAGE SQL PARALLEL SAFE;
 -- Aggregate colasce that keeps first non-null value it sees
CREATE AGGREGATE agg_coalesce (anyelement)
(
    sfunc = f_coalesce,
    stype = anyelement
);

---

Query #1

SELECT
    ts,
    email,
    
    array_agg(title) FILTER (WHERE title is not null ) OVER ( 
        order by ts desc ROWS BETWEEN current row and unbounded following 
    ) as title_array,
    (array_agg(title) FILTER (WHERE title is not null ) OVER ( 
        order by ts desc ROWS BETWEEN current row and unbounded following )
    )[1] as title,
    COALESCE(
        agg_coalesce(title) OVER ( 
            order by ts desc ROWS BETWEEN current row and unbounded following 
        ),
      	(select title from test 
         	where title is not null 
         	and ts < '2017-01-02'
         	order by ts desc limit 1 )
    )as title_locf 
from test
where ts >= '2017-01-02'
order by ts desc;

ts	email	title_array	title	title_locf
2017-01-08T00:00:00.000Z	me@me.com	Newer Title,New Title	Newer Title	Newer Title
2017-01-07T00:00:00.000Z	me@me.com	Newer Title,New Title	Newer Title	Newer Title
2017-01-06T00:00:00.000Z	me@me.com	Newer Title,New Title	Newer Title	Newer Title
2017-01-05T00:00:00.000Z	me@me.com	New Title	New Title	New Title
2017-01-04T00:00:00.000Z	me@me.com	New Title	New Title	New Title
2017-01-03T00:00:00.000Z	me@me.com	New Title	New Title	New Title
2017-01-02T00:00:00.000Z	me@me.com			Old title

---

View on DB Fiddle