jstrong
/
data-pipelines

SELECT hr,
       bmex / gdax AS ratio
FROM
  (SELECT hr,
          max(gdax) "gdax", -- max(..) necessary to grab the non-NULL row from two returned
          max(bmex) "bmex"  -- rows for each hr
   FROM
     (SELECT hr,
             (CASE WHEN exch=3 THEN wt_avg END) "gdax",
             (CASE WHEN exch=6 THEN wt_avg END) "bmex"
      FROM
        (SELECT hr,
                exch,
                w_sum / sum_w AS wt_avg
         FROM
           (SELECT date_trunc('hour', "time") AS hr,
                   exch,
                   sum(price * amount) AS w_sum,
                   sum(amount) AS sum_w
            FROM trades
            WHERE base=1             -- btc=1 usd=100
              AND quote=100
              AND (exch=3 OR exch=6) -- gdax=3, bmex=6
            GROUP BY 1, 2
            ORDER BY 1, 2) a) b) c
   GROUP BY hr) d;

SELECT hr,
       base,
       quote,
       bmex / gdax AS bmex_gdax,
       bmex / btfx AS bmex_btfx,
       bmex / bits AS bmex_bits,
       bmex / bnce AS bmex_bnce,
       bmex / okex AS bmex_okex,
       bmex / plnx AS bmex_plnx
FROM
  (SELECT hr,
          base,
          quote,
          max(gdax) "gdax", -- max(..) necessary to grab the non-NULL row from two returned
          max(bmex) "bmex", -- rows for each hr
          max(btfx) "btfx",
          max(bnce) "bnce",
          max(okex) "okex",
          max(plnx) "plnx",
          max(bits) "bits"
   FROM
     (SELECT hr,
             base,
             quote,
             (CASE WHEN exch=1 THEN wt_avg END) "plnx",
             (CASE WHEN exch=3 THEN wt_avg END) "gdax",
             (CASE WHEN exch=5 THEN wt_avg END) "bits",
             (CASE WHEN exch=6 THEN wt_avg END) "bmex",
             (CASE WHEN exch=7 THEN wt_avg END) "btfx",
             (CASE WHEN exch=8 THEN wt_avg END) "bnce",
             (CASE WHEN exch=9 THEN wt_avg END) "okex"
      FROM
        (SELECT hr,
                exch,
                base,
                quote,
                w_sum / sum_w AS wt_avg
         FROM
           (SELECT date_trunc('hour', "time") AS hr,
                   exch,
                   base,
                   quote,
                   sum(price * amount) AS w_sum,
                   sum(amount) AS sum_w
            FROM trades
            WHERE 
              -- base=1             -- btc=1 usd=100
              -- AND quote=100
              -- AND 
              exch IN (1,3,5,6,7,8,9)
            GROUP BY 1, 2, 3, 4
            ORDER BY 1, 2, 3, 4) a) b) c
   GROUP BY hr, base, quote) d;


SELECT tm,
       bmex / gdax AS ratio
FROM
  (SELECT tm,
          max(gdax) "gdax", -- max(..) necessary to grab the non-NULL row from two returned
          max(bmex) "bmex"  -- rows for each tm
   FROM
     (SELECT tm,
             (CASE WHEN exch=3 THEN wt_avg END) "gdax",
             (CASE WHEN exch=6 THEN wt_avg END) "bmex"
      FROM
        (SELECT tm,
                exch,
                w_sum / sum_w AS wt_avg
         FROM
           (SELECT 
                   -- date_trunc('minute', "time") AS tm,
                   date_trunc('minute', "time") - (CAST(EXTRACT(MINUTE FROM "time") AS integer) % 10) * interval '1 minute' AS tm,
                   exch,
                   sum(price * amount) AS w_sum,
                   sum(amount) AS sum_w
            FROM trades
            WHERE base=1             -- btc=1 usd=100
              AND quote=100
              AND (exch=3 OR exch=6) -- gdax=3, bmex=6
            GROUP BY 1, 2
            ORDER BY 1, 2) a) b) c
   GROUP BY tm) d;


select tm from generate_series('2019-01-01T00:00:00Z'::timestamptz, '2019-02-01T00:00:00Z'::timestamptz, interval '1 second') as tm;


select tm,
       sum(d1.price * d1.amount) / sum(d1.amount) as d1_wt_avg_price,
       sum(d1bmex.price * d1bmex.amount) / sum(d1bmex.amount) as d1bmex_wt_avg_price,
       sum(d5.price * d5.amount) / sum(d5.amount) as d5_wt_avg_price,
       sum(d5bmex.price * d5bmex.amount) / sum(d5bmex.amount) as d5bmex_wt_avg_price
from
    (select tm::timestamptz from generate_series(
        (select date_trunc('second', min("time")) from trades) + '20 minutes'::interval,
        -- (select date_trunc('second', max("time")) from trades),
        (select date_trunc('second', min("time")) from trades) + '21 minutes'::interval,
        interval '1 second'
    ) as tm
) a

left join trades d1 on (d1.time between a.tm - '1 minute'::interval and a.tm and d1.base=1 and d1.quote=100 and d1.exch=3)
left join trades d1bmex on (d1bmex.time between a.tm - '1 minute'::interval and a.tm and d1bmex.base=1 and d1bmex.quote=100 and d1bmex.exch=6)
left join trades d5 on (d5.time between a.tm - '5 minute'::interval and a.tm and d5.base=1 and d5.quote=100 and d5.exch=3)
left join trades d5bmex on (d5bmex.time between a.tm - '5 minute'::interval and a.tm and d5bmex.base=1 and d5bmex.quote=100 and d5bmex.exch=6)
group by a.tm
order by a.tm;
-- left join lateral (
--     select sum(price * amount) as wsum, sum(amount) as w
--     from trades
--     where base=1 and quote=100
--     group by exch

--inner join (select "time", price, amount from trades where base=1 and quote=100 and exch=3) d1
--    on (d1."time" between a.tm - '1 minute'::interval and a.tm)
-- on 1
-- inner join (select "time", sum(price * amount) as w_sum, sum(amount) as sum_w from trades group by exch) d5
-- inner join (select sum(price * amount) as w_sum, sum(amount) as sum_w from trades where "time" between a.tm - '5 minute'::interval and a.tm and base=1 and quote=100 and exch in (3,6) group by exch) d5
--     on 1
    -- on (d5."time" between a.tm - '5 minutes'::interval and a.tm)

-- where
-- d1.base = 1
-- and d1.quote = 100
-- and d5.base = 1
-- and d5.quote = 100
-- and d1.exch in (3,6)
-- and d5.exch in (3,6)

;

select 
    tm,
    bmex_d15_wt_avg_price / gdax_d15_wt_avg_price as ratio_15,
    bmex_d60_wt_avg_price / gdax_d60_wt_avg_price as ratio_60
from (
    select tm,
           sum(gdax_d15.price * gdax_d15.amount) / sum(gdax_d15.amount) as gdax_d15_wt_avg_price,
           sum(bmex_d15.price * bmex_d15.amount) / sum(bmex_d15.amount) as bmex_d15_wt_avg_price,
           sum(gdax_d60.price * gdax_d60.amount) / sum(gdax_d60.amount) as gdax_d60_wt_avg_price,
           sum(bmex_d60.price * bmex_d60.amount) / sum(bmex_d60.amount) as bmex_d60_wt_avg_price
    from
        (select tm::timestamptz from generate_series(
            (select date_trunc('second', min("time")) from trades) + '2 hours'::interval,
            (select date_trunc('second', min("time")) from trades) + '2 hours'::interval + '10 second'::interval,
            -- (select date_trunc('second', max("time")) from trades),
            interval '1 second'
        ) as tm
    ) a

    join trades gdax_d15 on (gdax_d15.time > a.tm - '15 minutes'::interval and gdax_d15.time <= a.tm and gdax_d15.base=1 and gdax_d15.quote=100 and gdax_d15.exch=3)
    join trades bmex_d15 on (bmex_d15.time > a.tm - '15 minutes'::interval and bmex_d15.time <= a.tm and bmex_d15.base=1 and bmex_d15.quote=100 and bmex_d15.exch=6)
    join trades gdax_d60 on (gdax_d60.time > a.tm - '60 minutes'::interval and gdax_d60.time <= a.tm and gdax_d60.base=1 and gdax_d60.quote=100 and gdax_d60.exch=3)
    join trades bmex_d60 on (bmex_d60.time > a.tm - '60 minutes'::interval and bmex_d60.time <= a.tm and bmex_d60.base=1 and bmex_d60.quote=100 and bmex_d60.exch=6)
    group by a.tm
    order by a.tm
) b;


SELECT tm,
       bmex / gdax AS ratio
FROM
  (SELECT tm,
          max(gdax) "gdax", -- max(..) necessary to grab the non-NULL row from two returned
          max(bmex) "bmex"  -- rows for each tm
   FROM
   GROUP BY tm) d;


     SELECT 
        tm,
        max(gdax_w_sum) "gdax_w_sum",
        max(gdax_sum_w) "gdax_sum_w",
        max(bmex_w_sum) "bmex_w_sum",
        max(bmex_sum_w) "bmex_sum_w"
      FROM
        (SELECT tm,
                (CASE WHEN exch=3 THEN w_sum END) "gdax_w_sum",
                (CASE WHEN exch=3 THEN sum_w END) "gdax_sum_w",
                (CASE WHEN exch=6 THEN w_sum END) "bmex_w_sum",
                (CASE WHEN exch=6 THEN sum_w END) "bmex_sum_w"
         FROM
           (SELECT date_trunc('second', "time") AS tm,
                   exch,
                   sum(price * amount) AS w_sum,
                   sum(amount) AS sum_w
            FROM trades
            WHERE base=1             -- btc=1 usd=100
              AND quote=100
              AND (exch=3 OR exch=6) -- gdax=3, bmex=6
            GROUP BY 1, 2
            ORDER BY 1, 2) a) b
    GROUP BY tm
    ORDER BY tm
    ;

SELECT a."time", avg(b.price)  
FROM trades a
JOIN trades b ON b.time >= a.time - interval '1 minute'  
  AND b.time <= a.time
GROUP BY a.time  
ORDER BY a.time
limit 10;

select 
    tm,
    bmex_d15_wt_avg_price / gdax_d15_wt_avg_price as ratio_15,
    bmex_d5_wt_avg_price / gdax_d5_wt_avg_price as ratio_5
from (
    select tm,
           sum(gdax_d15.price * gdax_d15.amount) / sum(gdax_d15.amount) as gdax_d15_wt_avg_price,
           sum(bmex_d15.price * bmex_d15.amount) / sum(bmex_d15.amount) as bmex_d15_wt_avg_price,
           sum(gdax_d5.price * gdax_d5.amount) / sum(gdax_d5.amount) as gdax_d5_wt_avg_price,
           sum(bmex_d5.price * bmex_d5.amount) / sum(bmex_d5.amount) as bmex_d5_wt_avg_price
    from
        (select tm::timestamptz from generate_series(
            (select date_trunc('second', min("time")) from trades limit 1), -- + '2 hours'::interval,
            -- (select date_trunc('second', min("time")) from trades) + '2 hours, 2 seconds'::interval,
            (select date_trunc('second', max("time")) from trades limit 1),
            interval '1 second'
            ) as tm
        ) a

    join trades gdax_d15 on (gdax_d15.time > a.tm - '15 minutes'::interval and gdax_d15.time <= a.tm and gdax_d15.base=1 and gdax_d15.quote=100 and gdax_d15.exch=3)
    join trades bmex_d15 on (bmex_d15.time > a.tm - '15 minutes'::interval and bmex_d15.time <= a.tm and bmex_d15.base=1 and bmex_d15.quote=100 and bmex_d15.exch=6)
    join trades gdax_d5 on (gdax_d5.time > a.tm - '5 minutes'::interval and gdax_d5.time <= a.tm and gdax_d5.base=1 and gdax_d5.quote=100 and gdax_d5.exch=3)
    join trades bmex_d5 on (bmex_d5.time > a.tm - '5 minutes'::interval and bmex_d5.time <= a.tm and bmex_d5.base=1 and bmex_d5.quote=100 and bmex_d5.exch=6)
    group by a.tm
    order by a.tm
) b;


select "time", avg(price) filter(where "time" > "time" - interval '10 seconds' and "time" <= "time") over( order by "time" ) from trades order by "time" limit 10; 


select 
"time",
-- (bmex_wsum_5  / bmex_w_5) as bmex_5min,
-- (gdax_wsum_5  / gdax_w_5) as gdax_5min,
(bmex_wsum_5  / bmex_w_5)  / (gdax_wsum_5  / gdax_w_5 ) as r5
-- (bmex_wsum_15 / bmex_w_15) / (gdax_wsum_15 / gdax_w_15) as r15,
-- (bmex_wsum_60 / bmex_w_60) / (gdax_wsum_60 / gdax_w_60) as r60
from
    (select
        "time",

        sum(price * amount) filter(where "time" > "time" - interval  '5 minutes' and "time" <= "time" and exch=3) over( order by "time" ) "gdax_wsum_5",
        -- sum(price * amount) filter(where "time" > "time" - interval '15 minutes' and "time" <= "time" and exch=3) over( order by "time" ) "gdax_wsum_15",
        -- sum(price * amount) filter(where "time" > "time" - interval '60 minutes' and "time" <= "time" and exch=3) over( order by "time" ) "gdax_wsum_60",

        sum(amount) filter(where "time" > "time" - interval  '5 minutes' and "time" <= "time" and exch=3) over( order by "time" ) "gdax_w_5",
        -- sum(amount) filter(where "time" > "time" - interval '15 minutes' and "time" <= "time" and exch=3) over( order by "time" ) "gdax_w_15",
        -- sum(amount) filter(where "time" > "time" - interval '60 minutes' and "time" <= "time" and exch=3) over( order by "time" ) "gdax_w_60",

        sum(price * amount) filter(where "time" > "time" - interval  '5 minutes' and "time" <= "time" and exch=6) over( order by "time" ) "bmex_wsum_5",
        -- sum(price * amount) filter(where "time" > "time" - interval '15 minutes' and "time" <= "time" and exch=6) over( order by "time" ) "bmex_wsum_15",
        -- sum(price * amount) filter(where "time" > "time" - interval '60 minutes' and "time" <= "time" and exch=6) over( order by "time" ) "bmex_wsum_60",

        sum(amount) filter(where "time" > "time" - interval  '5 minutes' and "time" <= "time" and exch=6) over( order by "time" ) "bmex_w_5"
        -- sum(amount) filter(where "time" > "time" - interval '15 minutes' and "time" <= "time" and exch=6) over( order by "time" ) "bmex_w_15",
        -- sum(amount) filter(where "time" > "time" - interval '60 minutes' and "time" <= "time" and exch=6) over( order by "time" ) "bmex_w_60"

    from trades
    order by "time") a
;


with seconds as (
    select tm::timestamptz from generate_series(
        (select date_trunc('second', min("time")) from trades limit 1), -- + '2 hours'::interval,
        -- (select date_trunc('second', min("time")) from trades) + '2 hours, 2 seconds'::interval,
        (select date_trunc('second', max("time")) from trades limit 1),
        interval '1 second'
        ) as tm
)
select 
tm,
(bmex_wsum_5  / bmex_w_5)  / (gdax_wsum_5  / gdax_w_5 ) as r5,
(bmex_wsum_15 / bmex_w_15) / (gdax_wsum_15 / gdax_w_15) as r15,
(bmex_wsum_60 / bmex_w_60) / (gdax_wsum_60 / gdax_w_60) as r60
from
    (select
        seconds.tm,

        sum(price * amount) filter(where "time" > seconds.tm - interval  '5 minutes' and "time" <= seconds.tm and exch=3) over( order by "time" ) "gdax_wsum_5",
        sum(price * amount) filter(where "time" > seconds.tm - interval '15 minutes' and "time" <= seconds.tm and exch=3) over( order by "time" ) "gdax_wsum_15",
        sum(price * amount) filter(where "time" > seconds.tm - interval '60 minutes' and "time" <= seconds.tm and exch=3) over( order by "time" ) "gdax_wsum_60",

        sum(amount) filter(where         "time" > seconds.tm - interval  '5 minutes' and "time" <= seconds.tm and exch=3) over( order by "time" ) "gdax_w_5",
        sum(amount) filter(where         "time" > seconds.tm - interval '15 minutes' and "time" <= seconds.tm and exch=3) over( order by "time" ) "gdax_w_15",
        sum(amount) filter(where         "time" > seconds.tm - interval '60 minutes' and "time" <= seconds.tm and exch=3) over( order by "time" ) "gdax_w_60",

        sum(price * amount) filter(where "time" > seconds.tm - interval  '5 minutes' and "time" <= seconds.tm and exch=6) over( order by "time" ) "bmex_wsum_5",
        sum(price * amount) filter(where "time" > seconds.tm - interval '15 minutes' and "time" <= seconds.tm and exch=6) over( order by "time" ) "bmex_wsum_15",
        sum(price * amount) filter(where "time" > seconds.tm - interval '60 minutes' and "time" <= seconds.tm and exch=6) over( order by "time" ) "bmex_wsum_60",

        sum(amount) filter(where         "time" > seconds.tm - interval  '5 minutes' and "time" <= seconds.tm and exch=6) over( order by "time" ) "bmex_w_5",
        sum(amount) filter(where         "time" > seconds.tm - interval '15 minutes' and "time" <= seconds.tm and exch=6) over( order by "time" ) "bmex_w_15",
        sum(amount) filter(where         "time" > seconds.tm - interval '60 minutes' and "time" <= seconds.tm and exch=6) over( order by "time" ) "bmex_w_60"

    from seconds, trades
    -- order by seconds.tm) a
) a
limit 1000;