Get sum of values for multiple categories for a given list of dates

Solution 1:

Try this:

with u as
(select id as categoryId from Category),
v as
(select distinct createdAt from Item),
w as
(select * from u cross join v),
x as
(select createdAt, 
categoryId, 
(select value 
from Item 
where categoryId = w.categoryId and createdAt <= w.createdAt 
order by createdAt desc 
limit 1) as value
from w)
select createdAt, sum(value) as total
from x
group by createdAt

Basically getting all the combinations of the creation dates with the categoryIds, then using a subquery to get the value of the closest or equal date for each categoryId.

A Fiddle.