Postgres 16

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

--https://stackoverflow.com/q/78040865/5298879
-- Creazione della tabella budget_usages
CREATE TABLE budget_usages (
    advertiser_id VARCHAR NOT NULL,
    marketplace_id VARCHAR NOT NULL,
    budget_scope_id BIGINT NOT NULL,
    budget DECIMAL(10, 2) NOT NULL,
    budget_usage_percentage DECIMAL(5, 2) NOT NULL,
    usage_updated_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    CONSTRAINT pk_budget_usages PRIMARY KEY (advertiser_id, marketplace_id, budget_scope_id, usage_updated_timestamp)
);

-- Inserimento dati fittizi
INSERT INTO budget_usages (advertiser_id, marketplace_id, budget_scope_id, budget, budget_usage_percentage, usage_updated_timestamp)
VALUES /*
--https://stackoverflow.com/revisions/78040865/2
--https://dbfiddle.uk/EJxfSGM5
--old samples
('adv123', 'mkt123', 1, 10000, 25.00, '2024-01-01 7:00'),
('adv123', 'mkt123', 1, 10000, 30.00, '2024-01-01 10:00'),
('adv123', 'mkt123', 2, 3000, 45.00, '2024-01-01 8:00'),
('adv123', 'mkt123', 2, 3000, 60.00, '2024-01-01 12:00'),
('adv123', 'mkt123', 2, 3000, 100.00, '2024-01-01 15:00'),
('adv123', 'mkt123', 2, 6000, 50.00, '2024-01-01 16:00'),
('adv123', 'mkt123', 2, 6000, 100.00, '2024-01-01 23:00'),
('adv123', 'mkt123', 1, 10000, 10.00, '2024-01-02 07:00'),
('adv123', 'mkt123', 2, 6000, 10.00, '2024-01-02 08:00')
*/
--https://stackoverflow.com/revisions/78040865/4
--https://dbfiddle.uk/sTxfucZ1
--new samples
('adv123', 'mkt123', 1, 1000, 25.00, '2024-01-01 7:00'),
('adv123', 'mkt123', 1, 1000, 30.00, '2024-01-01 10:00'),
('adv123', 'mkt123', 2, 3000, 45.00, '2024-01-01 8:00'),
('adv123', 'mkt123', 2, 3000, 60.00, '2024-01-01 12:00'),
('adv123', 'mkt123', 2, 3000, 100.00, '2024-01-01 15:00'),
('adv123', 'mkt123', 2, 6000, 50.00, '2024-01-01 16:00'),
('adv123', 'mkt123', 2, 6000, 100.00, '2024-01-01 23:00'),
('adv123', 'mkt123', 1, 1000, 10.00, '2024-01-02 07:00'),
('adv123', 'mkt123', 2, 6000, 10.00, '2024-01-02 08:00')
RETURNING *;

CREATE TABLE

advertiser_id	marketplace_id	budget_scope_id	budget	budget_usage_percentage	usage_updated_timestamp
adv123	mkt123	1	1000.00	25.00	2024-01-01 07:00:00
adv123	mkt123	1	1000.00	30.00	2024-01-01 10:00:00
adv123	mkt123	2	3000.00	45.00	2024-01-01 08:00:00
adv123	mkt123	2	3000.00	60.00	2024-01-01 12:00:00
adv123	mkt123	2	3000.00	100.00	2024-01-01 15:00:00
adv123	mkt123	2	6000.00	50.00	2024-01-01 16:00:00
adv123	mkt123	2	6000.00	100.00	2024-01-01 23:00:00
adv123	mkt123	1	1000.00	10.00	2024-01-02 07:00:00
adv123	mkt123	2	6000.00	10.00	2024-01-02 08:00:00

INSERT 0 9

select to_char("then".usage_updated_timestamp,'YYYY-MM-DD HH24:MI') as "Timestamp",
       coalesce(current_total_spending,0) as "Cost",
       coalesce("Notes",'0') as "Notes",
       coalesce(round((current_total_spending / current_total_budget * 100)::numeric,2),0) as "total % spent",
       coalesce(current_total_budget,0) as current_total_budget
from   (select distinct advertiser_id,
                generate_series('2024-01-01 7:00'::timestamp,
                                '2024-01-02 8:00',
                                '1 hour') usage_updated_timestamp
         from budget_usages order by 2,1) as "then",
lateral(select nullif(sum(budget),0)::float   as current_total_budget,
               sum(coalesce(spending,0))::float as current_total_spending,
               string_agg(concat(budget::text,'*',budget_usage_percentage),'+') as "Notes"
        from(select distinct on(advertiser_id,budget_scope_id)
                    budget::float, budget_usage_percentage::float/100 as budget_usage_percentage,
                    budget_usage_percentage*budget/100. spending
             from budget_usages as "by_then"
             where "by_then".usage_updated_timestamp
                    <="then".usage_updated_timestamp
               and ("by_then".usage_updated_timestamp::date,
                    "by_then".advertiser_id)    
                    = ("then".usage_updated_timestamp::date,
                       "then".advertiser_id)
             order by advertiser_id,
                      budget_scope_id, 
                      usage_updated_timestamp desc)_)_
order by usage_updated_timestamp;

Timestamp	Cost	Notes	total % spent	current_total_budget
2024-01-01 07:00	250	1000*0.25	25.00	1000
2024-01-01 08:00	1600	10000.25+30000.45	40.00	4000
2024-01-01 09:00	1600	10000.25+30000.45	40.00	4000
2024-01-01 10:00	1650	10000.3+30000.45	41.25	4000
2024-01-01 11:00	1650	10000.3+30000.45	41.25	4000
2024-01-01 12:00	2100	10000.3+30000.6	52.50	4000
2024-01-01 13:00	2100	10000.3+30000.6	52.50	4000
2024-01-01 14:00	2100	10000.3+30000.6	52.50	4000
2024-01-01 15:00	3300	10000.3+30001	82.50	4000
2024-01-01 16:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 17:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 18:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 19:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 20:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 21:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 22:00	3300	10000.3+60000.5	47.14	7000
2024-01-01 23:00	6300	10000.3+60001	90.00	7000
2024-01-02 00:00	0	0	0	0
2024-01-02 01:00	0	0	0	0
2024-01-02 02:00	0	0	0	0
2024-01-02 03:00	0	0	0	0
2024-01-02 04:00	0	0	0	0
2024-01-02 05:00	0	0	0	0
2024-01-02 06:00	0	0	0	0
2024-01-02 07:00	100	1000*0.1	10.00	1000
2024-01-02 08:00	700	10000.1+60000.1	10.00	7000

SELECT 26