How to calculate average stock status in day

Stock status for days is in table

create table stockstatus (
stockdate date not null,  -- date of stock status
product  character(60) not null,  -- product id
status  int not null, -- stock status in end of this day.
constraint primary key ( stockdate, product )
)

How to calculate average stock status in day for given period. Result table should be

create table AverageStockState (
product c(60) character(60) primary key,
status  int -- average stock status in day in period
)

For example for one product December stock table contains rows

stockdate   status
Dec 1       10
Dec 5       20

Calculation:

Dec 1-4 has status 10

Dec 5-31 has status 20

Average stock status in December is

( 4*10 + ( 31-5+1 ) * 20 ) /31 = 19

So result table should contain product code and value 19 for this product.

Using PostgreSQL 16.2

1. date_ranges CTE calculates the current stock_date and the next stockdate based on Lead, as an example only december month is considered, you can adjust as required.

2. Missing stock dates are then assigned a stock using generate_series in expanded_dates CTE

3. weighted_sum is calculated by group by and I have used NUMERIC to cast and CEIL to fetch the next integer value.

EDIT : Sample input when stockdate does not start from beginning of month

Fiddle

WITH date_ranges AS (
    SELECT 
        s.product,
        s.stockdate,
        LEAD(s.stockdate) OVER (PARTITION BY s.product ORDER BY s.stockdate) AS next_stockdate,
        s.status
    FROM 
        stockstatus s
),
periods AS (
    SELECT 
        product,
        stockdate AS start_date,
        COALESCE(next_stockdate, '9999-12-31'::date) AS end_date,
        status,
        next_stockdate
    FROM date_ranges
),
daily_status AS (
    SELECT 
        p.product,
        p.start_date,
        p.status,
        p.next_stockdate,
        CASE 
            WHEN p.next_stockdate IS NULL THEN LEAST(p.end_date, '2023-12-31'::date) - GREATEST(p.start_date, '2023-12-01'::date) + 1
            ELSE LEAST(p.end_date, '2023-12-31'::date) - GREATEST(p.start_date, '2023-12-01'::date)
        END AS days_in_period
    FROM periods p
    WHERE p.end_date >= '2023-12-01' AND p.start_date <= '2023-12-31'
),
monthly_status AS (
    SELECT 
        ds.product,
        SUM(ds.days_in_period * ds.status) AS weighted_status_sum,
        COUNT(*) AS total_days_in_period,
        EXTRACT(DAY FROM (DATE_TRUNC('MONTH', MIN(ds.start_date)) + INTERVAL '1 MONTH' - INTERVAL '1 day')) AS days_in_month
    FROM daily_status ds
    GROUP BY ds.product
)
SELECT 
    product,
    weighted_status_sum,
    days_in_month,
    CEIL(weighted_status_sum::NUMERIC / days_in_month::NUMERIC) AS status  
FROM monthly_status
ORDER BY product;

Input data

Output