Update SQL query to prorate data

I need this query updated to make sure the usage and price data only reflects the data for the month we're querying.

Our database stores data by bills in a billing period. We may have a bill overlap multiple months or not cover the entire month we're querying. So we need to average the usage data and prorate it by the days in that month. Then we create a weighted average for the price for that month according to the usage (as there may be different prices if there is one bill for the start of march and the end of march for example).

Make sure days and usage is prorated according to the number of days in the month we are querying (right now this query returns multiple rows with > 31 days).

Access to our database is not possible.

The first one to get the query back to me that is correct will be awarded and immediately paid. I won't be accepting the project from anyone until I see working SQL. Please message me your fix.


--- POSTGRESQL ---

WITH usage_unnested AS (
SELECT
sites.id AS site_id,
unnest(usage.peak) AS peak,
unnest(usage.off_peak) AS off_peak,
unnest(usage.shoulder) AS shoulder,
unnest(usage.controlled_load) AS controlled_load,
usage.subtotal,
usage.price_id,
usage.start_date,
usage.start_date + (usage.supply_charge * INTERVAL '1 day') AS end_date
FROM
usage
JOIN
sites ON usage.site_id = sites.id
),
price_unnested AS (
SELECT
prices.id AS price_id,
unnest(prices.peak) AS peak,
unnest(prices.off_peak) AS off_peak,
unnest(prices.shoulder) AS shoulder,
unnest(prices.controlled_load) AS controlled_load
FROM
prices
),
usage_prorated AS (
SELECT
site_id,
start_date,
end_date,
LEAST(GREATEST(LEAST(end_date, '2023-03-31'::date) - GREATEST(start_date, '2023-03-01'::date) + INTERVAL '1 day', INTERVAL '0 day'), '31 days'::interval) AS days_in_march_2023,
LEAST(GREATEST(LEAST(end_date, '2024-03-31'::date) - GREATEST(start_date, '2024-03-01'::date) + INTERVAL '1 day', INTERVAL '0 day'), '31 days'::interval) AS days_in_march_2024,
EXTRACT(EPOCH FROM LEAST(GREATEST(LEAST(end_date, '2023-03-31'::date) - GREATEST(start_date, '2023-03-01'::date) + INTERVAL '1 day', INTERVAL '0 day'), '31 days'::interval)) / 86400 AS usage_days_2023,
EXTRACT(EPOCH FROM LEAST(GREATEST(LEAST(end_date, '2024-03-31'::date) - GREATEST(start_date, '2024-03-01'::date) + INTERVAL '1 day', INTERVAL '0 day'), '31 days'::interval)) / 86400 AS usage_days_2024,
peak,
off_peak,
shoulder,
controlled_load,
subtotal
FROM
usage_unnested
WHERE
(start_date <= '2023-03-31' AND end_date >= '2023-03-01') OR
(start_date <= '2024-03-31' AND end_date >= '2024-03-01')
),
usage_data AS (
SELECT
site_id,
SUM(COALESCE(peak, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2023) AS mar_2023_usage,
SUM(COALESCE(off_peak, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2023) AS mar_2023_offpeak,
SUM(COALESCE(shoulder, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2023) AS mar_2023_shoulder,
SUM(COALESCE(controlled_load, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2023) AS mar_2023_controlled,
SUM(COALESCE(peak, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2024) AS mar_2024_usage,
SUM(COALESCE(off_peak, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2024) AS mar_2024_offpeak,
SUM(COALESCE(shoulder, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2024) AS mar_2024_shoulder,
SUM(COALESCE(controlled_load, 0) / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2024) AS mar_2024_controlled,
SUM(subtotal / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2023) AS mar_2023_spend,
SUM(subtotal / NULLIF(EXTRACT(EPOCH FROM (end_date - start_date + INTERVAL '1 day')) / 86400, 0) * usage_days_2024) AS mar_2024_spend,
SUM(usage_days_2023) AS total_usage_days_2023,
SUM(usage_days_2024) AS total_usage_days_2024
FROM
usage_prorated
GROUP BY
site_id
),
price_data_2023 AS (
SELECT
usage_unnested.site_id,
SUM(usage_unnested.peak * price_unnested.peak) / NULLIF(SUM(usage_unnested.peak), 0) AS weighted_peak_price_2023,
SUM(usage_unnested.off_peak * price_unnested.off_peak) / NULLIF(SUM(usage_unnested.off_peak), 0) AS weighted_offpeak_price_2023,
SUM(usage_unnested.shoulder * price_unnested.shoulder) / NULLIF(SUM(usage_unnested.shoulder), 0) AS weighted_shoulder_price_2023,
SUM(usage_unnested.controlled_load * price_unnested.controlled_load) / NULLIF(SUM(usage_unnested.controlled_load), 0) AS weighted_controlled_load_price_2023
FROM
usage_unnested
JOIN
price_unnested ON usage_unnested.price_id = price_unnested.price_id
WHERE
EXTRACT(YEAR FROM usage_unnested.start_date) = 2023
GROUP BY
usage_unnested.site_id
),
price_data_2024 AS (
SELECT
usage_unnested.site_id,
SUM(usage_unnested.peak * price_unnested.peak) / NULLIF(SUM(usage_unnested.peak), 0) AS weighted_peak_price_2024,
SUM(usage_unnested.off_peak * price_unnested.off_peak) / NULLIF(SUM(usage_unnested.off_peak), 0) AS weighted_offpeak_price_2024,
SUM(usage_unnested.shoulder * price_unnested.shoulder) / NULLIF(SUM(usage_unnested.shoulder), 0) AS weighted_shoulder_price_2024,
SUM(usage_unnested.controlled_load * price_unnested.controlled_load) / NULLIF(SUM(usage_unnested.controlled_load), 0) AS weighted_controlled_load_price_2024
FROM
usage_unnested
JOIN
price_unnested ON usage_unnested.price_id = price_unnested.price_id
WHERE
EXTRACT(YEAR FROM usage_unnested.start_date) = 2024
GROUP BY
usage_unnested.site_id
)
SELECT
sites.identity AS "nmi",
ud.mar_2023_usage + ud.mar_2023_offpeak + ud.mar_2023_shoulder + ud.mar_2023_controlled AS "Mar 2023 usage",
ud.mar_2023_spend AS "Mar 2023 spend (excl gst + supply charge)",
ud.mar_2024_usage + ud.mar_2024_offpeak + ud.mar_2024_shoulder + ud.mar_2024_controlled AS "Mar 2024 usage",
ud.mar_2024_spend AS "Mar 2024 spend",
(ud.mar_2024_usage + ud.mar_2024_offpeak + ud.mar_2024_shoulder + ud.mar_2024_controlled - ud.mar_2023_usage - ud.mar_2023_offpeak - ud.mar_2023_shoulder - ud.mar_2023_controlled) / NULLIF(ud.mar_2023_usage + ud.mar_2023_offpeak + ud.mar_2023_shoulder + ud.mar_2023_controlled, 0) * 100 AS "Increase (decrease)",
(ud.mar_2024_usage + ud.mar_2024_offpeak + ud.mar_2024_shoulder + ud.mar_2024_controlled - ud.mar_2023_usage - ud.mar_2023_offpeak - ud.mar_2023_shoulder - ud.mar_2023_controlled) / NULLIF(ud.mar_2023_usage + ud.mar_2023_offpeak + ud.mar_2023_shoulder + ud.mar_2023_controlled, 0) * 100 AS "Usage increase",
pd_2023.weighted_peak_price_2023 AS "Peak price (Mar23)",
ud.mar_2023_usage AS "Peak usage (Mar23)",
pd_2024.weighted_peak_price_2024 AS "Peak price (Mar24)",
ud.mar_2024_usage AS "Peak usage (Mar24)",
pd_2023.weighted_offpeak_price_2023 AS "OffPeak price (Mar23)",
ud.mar_2023_offpeak AS "OffPeak usage (Mar23)",
pd_2024.weighted_offpeak_price_2024 AS "OffPeak price (Mar24)",
ud.mar_2024_offpeak AS "OffPeak usage (Mar24)",
ud.total_usage_days_2023 AS "Usage days (Mar23)",
ud.total_usage_days_2024 AS "Usage days (Mar24)"
FROM
clients
JOIN
users ON clients.id = users.client_id
JOIN
sites ON users.id = sites.user_id
LEFT JOIN
usage_data ud ON sites.id = ud.site_id
LEFT JOIN
price_data_2023 pd_2023 ON pd_2023.site_id = sites.id
LEFT JOIN
price_data_2024 pd_2024 ON pd_2024.site_id = sites.id
WHERE
clients.id = 211
AND sites.type = 'elec';