Postgres 12

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

 id INTEGER PRIMARY KEY,
 created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
 quantity INTEGER,
 category TEXT
);
create table foo_category_properties (
id INTEGER PRIMARY KEY , 
category TEXT NOT NULL,  
some_bool BOOLEAN NOT NULL DEFAULT FALSE
);
insert into foo_category_properties values
	(1, 'a', false), (2, 'b', false)
2 rows affected

INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('1','2020-01-01 12:00:00+00',2,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('2','2020-01-02 17:00:00+00',1,'b');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('3','2020-01-01 15:00:00+00',6,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('4','2020-01-04 09:00:00+00',1,'b');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('5','2020-01-05 19:00:00+00',2,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('6','2020-01-06 23:00:00+00',8,'b');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('7','2020-01-07 20:00:00+00',1,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('8','2020-01-08 04:00:00+00',2,'b');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('9','2020-01-09 23:00:00+00',1,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('10','2020-01-10 19:00:00+00',1,'b');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('11','2020-01-11 05:00:00+00',1,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('12','2020-01-12 21:00:00+00',1,'b');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('13','2020-01-13 01:00:00+00',1,'a');
INSERT INTO foo_numbers(id,created_at,quantity,category) VALUES ('14','2020-01-14 18:00:00+00',1,'b');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select distinct on (n.category)
	n.id,
	n.created_at at time zone 'America/Los_Angeles' at time zone 'utc' created_at,
	n.quantity,
	n.category,
	sum(quantity) 
	    over (partition by n.category order by n.created_at) as snapshot_count 
from foo_numbers n
inner join foo_category_properties cp on cp.category = n.category
where n.created_at <= '2020-01-07 19:00:00'::timestamp with time zone 
                          at time zone 'utc' at time zone 'America/Los_Angeles'
order by n.category, n.created_at desc


id
created_at
quantity
category
snapshot_count


7
2020-01-07 12:00:00+00
1
a
11

6
2020-01-06 15:00:00+00
8
b
10