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.