Postgres 16

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

-- 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
  advertiser_id,
  marketplace_id,
  usage_updated_timestamp,
  budget_scope_id,
  budget*budget_usage_percentage/100 
   -LAG( budget*budget_usage_percentage/100
        , 1, 0) OVER w1 
   AS delta
FROM budget_usages
WINDOW w1 AS (PARTITION BY advertiser_id, 
                           marketplace_id, 
                           budget_scope_id, 
                           date_trunc( 'day'
                                      ,usage_updated_timestamp) 
              ORDER BY usage_updated_timestamp)


advertiser_id
marketplace_id
usage_updated_timestamp
budget_scope_id
delta


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

adv123
mkt123
2024-01-01 10:00:00
1
50.0000000000000000

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

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

adv123
mkt123
2024-01-01 12:00:00
2
450.0000000000000000

adv123
mkt123
2024-01-01 15:00:00
2
1200.0000000000000000

adv123
mkt123
2024-01-01 16:00:00
2
0.0000000000000000

adv123
mkt123
2024-01-01 23:00:00
2
3000.0000000000000000

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



SELECT 9


WITH expenses as (
  SELECT
    advertiser_id,
    marketplace_id,
    usage_updated_timestamp,
    budget * budget_usage_percentage / 100. 
    - LAG(budget * budget_usage_percentage / 100, 1, 0)OVER w1 AS delta
  FROM budget_usages
  WINDOW w1 AS (PARTITION BY advertiser_id, 
                             marketplace_id, 
                             budget_scope_id, 
                             date_trunc( 'day'
                                        ,usage_updated_timestamp) 
                ORDER BY usage_updated_timestamp)
UNION ALL
  SELECT DISTINCT
    advertiser_id,
    marketplace_id,
    date_trunc('day', usage_updated_timestamp),
    0 AS delta
  FROM budget_usages
)
SELECT
  advertiser_id,
  marketplace_id,
  usage_updated_timestamp AS timestamp,
  SUM(delta)OVER w2 AS cost
FROM expenses
WINDOW w2 AS (PARTITION BY advertiser_id, 
                           marketplace_id, 
                           date_trunc('day', usage_updated_timestamp) 
                 ORDER BY usage_updated_timestamp)
ORDER BY 1, 2, 3


advertiser_id
marketplace_id
timestamp
cost


adv123
mkt123
2024-01-01 00:00:00
0

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

adv123
mkt123
2024-01-01 08:00:00
1600.0000000000000000

adv123
mkt123
2024-01-01 10:00:00
1650.0000000000000000

adv123
mkt123
2024-01-01 12:00:00
2100.0000000000000000

adv123
mkt123
2024-01-01 15:00:00
3300.0000000000000000

adv123
mkt123
2024-01-01 16:00:00
3300.0000000000000000

adv123
mkt123
2024-01-01 23:00:00
6300.0000000000000000

adv123
mkt123
2024-01-02 00:00:00
0

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

adv123
mkt123
2024-01-02 08:00:00
700.0000000000000000



SELECT 11

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	marketplace_id	timestamp	cost
adv123	mkt123	2024-01-01 00:00:00	0
adv123	mkt123	2024-01-01 07:00:00	250.0000000000000000
adv123	mkt123	2024-01-01 08:00:00	1600.0000000000000000
adv123	mkt123	2024-01-01 10:00:00	1650.0000000000000000
adv123	mkt123	2024-01-01 12:00:00	2100.0000000000000000
adv123	mkt123	2024-01-01 15:00:00	3300.0000000000000000
adv123	mkt123	2024-01-01 16:00:00	3300.0000000000000000
adv123	mkt123	2024-01-01 23:00:00	6300.0000000000000000
adv123	mkt123	2024-01-02 00:00:00	0
adv123	mkt123	2024-01-02 07:00:00	100.0000000000000000
adv123	mkt123	2024-01-02 08:00:00	700.0000000000000000