-- Optimize GROUP BY query to retrieve latest row per user

latest_row_per_user.sql

-- Optimize GROUP BY query to retrieve latest row per user
--https://dbfiddle.uk/men99GVd
begin;
create table users(
    user_id bigint primary key generated always as identity
    ,username text not null
);

insert into users(username) select 'usr_'::text || g 
from generate_series(1,1000) g; 


create table log(
    user_id bigint
    ,log_date date
    ,payload bigint
);

insert into log(log_date,user_id,payload)
select '2010-01-01'::date
    + g/50 +
    case when random() > 0.9 then (random() ^ 3 *100)::int
    else 0
    end
    ,1 + trunc(random() * 1000)::int
    ,(random() * 1000 )::int
from generate_series(1,100000) g;

create index log_combo_covering_idx
on log(user_id,log_date desc nulls last, payload); 

create type combo as (log_date date, payload int);
commit;

-- 1st-----------------------------------------------------------------------------
-- Without table "users"
-- 1a rCTE with LATERAL
-- no need to pay attention nulls for lateral join 
with recursive cte as(
(select user_id, log_date,payload from log
where log_date <= '2015-01-07'
and user_id > 990
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
    and l.log_date <= '2015-01-07'
    order by l.user_id,l.log_date desc nulls last
    limit  1
    ) l 
)
table cte order by user_id;

--2nd-------------------------------------------------------------
--recursve correlate subquery.
-- need to take care of null cases. 
with recursive cte as(
(select l as my_row from log l
    where log_date <= '2015-01-07' 
    and user_id > 990
    order by user_id,log_date desc nulls last
    limit 1
)
union all
select(
    select l 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    
)
select (my_row).* from cte  --decompose
    where (my_row).user_id is not null order by (my_row).user_id;
   
---3rd-----------------------------------------------
-- With table "users"
-- JOIN LATERAL
select u.user_id, t.log_date, t.payload
from users u
cross join lateral(
    select l.log_date, l.payload
    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
) t
where u.user_id > 990;

-----4th--------------------------------------

--  Correlated subquery
select user_id, (combo).* from 
(select
    u.user_id
    ,(select (l.log_date, l.payload)::combo as 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
    )
from users u
where u.user_id > 990
) sub order by (combo).log_date;