SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE events (
  created_at,
  account_id,
  strategy_id
);
✓
insert into events 
VALUES ('2022-10-07 12:53:53', 4801323843, 7),
('2022-10-07 08:10:07', 4801323843, 5),
('2022-10-07 15:00:45', 4801323843, 8),
('2022-10-10 13:01:16', 4801323843, 6);
✓
select * from events


created_at
account_id
strategy_id


2022-10-07 12:53:53
4801323843
7

2022-10-07 08:10:07
4801323843
5

2022-10-07 15:00:45
4801323843
8

2022-10-10 13:01:16
4801323843
6


WITH RECURSIVE
  daterange(x) AS (select date(min(created_at)) from events UNION ALL SELECT date(x,'1 day') FROM daterange WHERE x<(select max(created_at) from events))
SELECT x FROM daterange;


x


2022-10-07

2022-10-08

2022-10-09

2022-10-10

2022-10-11


WITH RECURSIVE
  daterange(d) AS (select date(min(created_at)) from events UNION ALL SELECT date(d,'1 day') FROM daterange WHERE d<(select max(created_at) from events))
SELECT d, account_id, strategy_id
FROM daterange JOIN events
WHERE created_at = (select max(e.created_at) from events e where e.account_id=events.account_id and date(e.created_at) <= d)
GROUP BY account_id, d
ORDER BY account_id, d


d
account_id
strategy_id


2022-10-07
4801323843
8

2022-10-08
4801323843
8

2022-10-09
4801323843
8

2022-10-10
4801323843
6

2022-10-11
4801323843
6

created_at	account_id	strategy_id
2022-10-07 12:53:53	4801323843	7
2022-10-07 08:10:07	4801323843	5
2022-10-07 15:00:45	4801323843	8
2022-10-10 13:01:16	4801323843	6

d	account_id	strategy_id
2022-10-07	4801323843	8
2022-10-08	4801323843	8
2022-10-09	4801323843	8
2022-10-10	4801323843	6
2022-10-11	4801323843	6