price_api/sql/spread.pg.sql

DROP FUNCTION IF EXISTS rlarp.get_premium;
CREATE OR REPLACE FUNCTION rlarp.get_premium(
    _mold text,
    _season int,
    _chan text,
    _relevant text,
    _quoted text
)
RETURNS jsonb 
LANGUAGE plpgsql
AS $func$
DECLARE
    _factor jsonb;

BEGIN
WITH
--target spreads--
bridge AS (
    SELECT 
         _mold          AS mold
        ,_season        AS season
        ,_chan          AS chan
        ,_relevant      AS relevant
        ,_quoted        AS quoted  
),
ts AS (
   SELECT 
        t.*
        ,t.target_price / min(t.target_price) over (partition by t.mold, t.chan, t.season) AS spread 
        ,b.quoted
        ,b.relevant
        ,CASE 
            WHEN t.data_segment = b.quoted THEN 'get to here' 
            WHEN t.data_segment = b.relevant THEN 'from here' 
         END AS flag
    FROM
        pricequote.market_setavgprice t
        INNER JOIN bridge b ON
                t.mold = b.mold
            AND t.season = b.season
            AND t.chan = b.chan
            AND t.data_segment LIKE 'v1%' 
            AND t.geo = 'ALL'
            AND t.country = 'ALL'
)
SELECT 
    jsonb_build_object(
        'bridgePremium',
        ROUND(max(spread) FILTER (WHERE flag = 'get to here') / max(spread) FILTER (WHERE flag = 'from here'), 5)
    )
INTO
    _factor
FROM 
    ts 
GROUP BY 
    mold, chan, season;

RAISE NOTICE 'relevant: %' , _relevant;
RAISE NOTICE 'quoted: %' , _quoted;

RETURN _factor;
END
$func$;