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


--https://stackoverflow.com/q/78040865/5298879
--OP's attempt
SELECT
 advertiser_id,
 usage_updated_timestamp,
 SUM(budget*budget_usage_percentage/100) AS cost
FROM budget_usages
GROUP BY 1,2
order by 2;
/*
the cost decrease during the day 
because for the same timestamp I have a record for one campaign 
and not for the other. 
I cannot use date_trunc('hour', timestamp) because they are random. 
*/


advertiser_id
usage_updated_timestamp
cost


adv123
2024-01-01 07:00:00
250.0000000000000000

adv123
2024-01-01 08:00:00
1350.0000000000000000

adv123
2024-01-01 10:00:00
300.0000000000000000

adv123
2024-01-01 12:00:00
1800.0000000000000000

adv123
2024-01-01 15:00:00
3000.0000000000000000

adv123
2024-01-01 16:00:00
3000.0000000000000000

adv123
2024-01-01 23:00:00
6000.0000000000000000

adv123
2024-01-02 07:00:00
100.0000000000000000

adv123
2024-01-02 08:00:00
600.0000000000000000



SELECT 9


select *, current_total_spending / current_total_budget * 100 as "% spent"
from budget_usages as "then",
lateral
( select sum(budget)::float   as current_total_budget,
         sum(spending)::float as current_total_spending
  from( select distinct on(advertiser_id,budget_scope_id)
               budget,
               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;


advertiser_id
marketplace_id
budget_scope_id
budget
budget_usage_percentage
usage_updated_timestamp
current_total_budget
current_total_spending
% spent


adv123
mkt123
1
1000.00
25.00
2024-01-01 07:00:00
1000
250
25

adv123
mkt123
2
3000.00
45.00
2024-01-01 08:00:00
4000
1600
40

adv123
mkt123
1
1000.00
30.00
2024-01-01 10:00:00
4000
1650
41.25

adv123
mkt123
2
3000.00
60.00
2024-01-01 12:00:00
4000
2100
52.5

adv123
mkt123
2
3000.00
100.00
2024-01-01 15:00:00
4000
3300
82.5

adv123
mkt123
2
6000.00
50.00
2024-01-01 16:00:00
7000
3300
47.14285714285714

adv123
mkt123
2
6000.00
100.00
2024-01-01 23:00:00
7000
6300
90

adv123
mkt123
1
1000.00
10.00
2024-01-02 07:00:00
1000
100
10

adv123
mkt123
2
6000.00
10.00
2024-01-02 08:00:00
7000
700
10



SELECT 9


select to_char("then".usage_updated_timestamp,'YYYY-MM-DD HH24:MI') as "Timestamp",
       current_total_spending as "Cost",
       "Notes",
       "then".budget_scope_id as scope_id, 
       "then".budget, 
       "then".budget_usage_percentage as "% spent",
       current_total_budget as "total budget",
       round((current_total_spending / current_total_budget * 100)::numeric,2) as "total % spent"
from budget_usages as "then",
lateral
( select sum(budget)::float   as current_total_budget,
         sum(spending)::float as current_total_spending,
         string_agg(concat(budget::text,'*',budget_usage_percentage),'+') as "Notes"
  from( select distinct on(advertiser_id,budget_scope_id) ctid,
               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
scope_id
budget
% spent
total budget
total % spent


2024-01-01 07:00
250
1000*0.25
1
1000.00
25.00
1000
25.00

2024-01-01 08:00
1600
1000*0.25+3000*0.45
2
3000.00
45.00
4000
40.00

2024-01-01 10:00
1650
1000*0.3+3000*0.45
1
1000.00
30.00
4000
41.25

2024-01-01 12:00
2100
1000*0.3+3000*0.6
2
3000.00
60.00
4000
52.50

2024-01-01 15:00
3300
1000*0.3+3000*1
2
3000.00
100.00
4000
82.50

2024-01-01 16:00
3300
1000*0.3+6000*0.5
2
6000.00
50.00
7000
47.14

2024-01-01 23:00
6300
1000*0.3+6000*1
2
6000.00
100.00
7000
90.00

2024-01-02 07:00
100
1000*0.1
1
1000.00
10.00
1000
10.00

2024-01-02 08:00
700
1000*0.1+6000*0.1
2
6000.00
10.00
7000
10.00



SELECT 9

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

advertiser_id	usage_updated_timestamp	cost
adv123	2024-01-01 07:00:00	250.0000000000000000
adv123	2024-01-01 08:00:00	1350.0000000000000000
adv123	2024-01-01 10:00:00	300.0000000000000000
adv123	2024-01-01 12:00:00	1800.0000000000000000
adv123	2024-01-01 15:00:00	3000.0000000000000000
adv123	2024-01-01 16:00:00	3000.0000000000000000
adv123	2024-01-01 23:00:00	6000.0000000000000000
adv123	2024-01-02 07:00:00	100.0000000000000000
adv123	2024-01-02 08:00:00	600.0000000000000000

Timestamp	Cost	Notes	scope_id	budget	% spent	total budget	total % spent
2024-01-01 07:00	250	1000*0.25	1	1000.00	25.00	1000	25.00
2024-01-01 08:00	1600	10000.25+30000.45	2	3000.00	45.00	4000	40.00
2024-01-01 10:00	1650	10000.3+30000.45	1	1000.00	30.00	4000	41.25
2024-01-01 12:00	2100	10000.3+30000.6	2	3000.00	60.00	4000	52.50
2024-01-01 15:00	3300	10000.3+30001	2	3000.00	100.00	4000	82.50
2024-01-01 16:00	3300	10000.3+60000.5	2	6000.00	50.00	7000	47.14
2024-01-01 23:00	6300	10000.3+60001	2	6000.00	100.00	7000	90.00
2024-01-02 07:00	100	1000*0.1	1	1000.00	10.00	1000	10.00
2024-01-02 08:00	700	10000.1+60000.1	2	6000.00	10.00	7000	10.00