Untitled

select 
sd as "Settlement day",
sp as "Settlement period",
case
when coalesce(pp_mip.pxp, 0) = 0 then pd_mip.pxp
else pp_mip.pxp
end as "PXP-Market Index Price",
pp_sbp_niv.sbp as "SBP-System Buy price",
pp_sbp_niv.ssp as "SSP-System Sell price",
pp_sbp_niv.niv as "NIV-Net Imbalance Volume"
from (
select
sd,
sp,
case when sum(qxp) = 0 then avg(pxp) else sum(pxp * qxp) / sum(qxp) end as pxp 
from bo_user_uniper.pp_sys_mid_sp 
where latest = 1 
and sd between date '2024-10-12' and date '2024-10-14'
group by sd, sp
) pp_mip
full outer join (
select 
sd,
sp,
avg(sbp) as sbp,
avg(ssp) as ssp,
sum(niv) as niv 
from bo_user_uniper.pp_sys_sp 
where latest = 1 
and sd between date '2024-10-12' and date '2024-10-14'
group by sd, sp
) pp_sbp_niv using (sd, sp)
full outer join (
select
sd,
sp,
case when sum (volume) = 0 then avg(price) else sum(price * volume) / sum (volume) end pxp
from bo_user_uniper.pd_mid_sp
where sd between date '2024-10-12' and date '2024-10-14'
group by sd, sp
) pd_mip using (sd, sp)
order by 1,2