binhachon117. [Hard] Terrarbitage, Part 1 - Dailly chart
    Updated 2021-12-13
    ------------------------------------- GET SWAP PRICE --------------------------------------------------------------------------
    WITH LP_SWAP_TABLE AS(
    SELECT BLOCK_TIMESTAMP, TX_ID, --SWAP LUNA FOR bLUNA
    EVENT_ATTRIBUTES:"to"::string AS ADDRESS,
    EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS LUNA_AMOUNT,
    EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS BLUNA_AMOUNT
    FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"0_action"::string = 'swap'
    AND EVENT_ATTRIBUTES:"0_contract_address"::string = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' -- INPUT LP CONTRACT HERE
    UNION ALL
    SELECT BLOCK_TIMESTAMP, TX_ID, -- SWAP bLUNA for LUNA
    EVENT_ATTRIBUTES:"from"::string AS ADDRESS,
    EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS LUNA_AMOUNT,
    EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS BLUNA_AMOUNT
    FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"3_action"::string = 'swap'
    AND EVENT_ATTRIBUTES:"3_contract_address"::string = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' -- INPUT LP CONTRACT HERE
    )
    ------------------------------------- 30 seconds time window --------------------------------------------------------------------------
    select date_trunc('day', "START OF SLICE") as blocktime,
    count_if(ratio > 1) as frequency_1_01, sum(case when ratio > 1 then LUNA_AMOUNT else 0 end) as volume_1_01, volume_1_01/iff(frequency_1_01 = 0,1, frequency_1_01) as average_1_01,
    count_if(ratio > 2) as frequency_1_02, sum(case when ratio > 2 then LUNA_AMOUNT else 0 end) as volume_1_02, volume_1_02/iff(frequency_1_02 = 0,1, frequency_1_02) as average_1_02,
    count_if(ratio > 3) as frequency_1_03, sum(case when ratio > 3 then LUNA_AMOUNT else 0 end) as volume_1_03, volume_1_03/iff(frequency_1_03 = 0,1, frequency_1_03) as average_1_03,
    count_if(ratio > 4) as frequency_1_04, sum(case when ratio > 4 then LUNA_AMOUNT else 0 end) as volume_1_04, volume_1_04/iff(frequency_1_04 = 0,1, frequency_1_04) as average_1_04,
    count_if(ratio > 5) as frequency_1_05, sum(case when ratio > 5 then LUNA_AMOUNT else 0 end) as volume_1_05, volume_1_05/iff(frequency_1_05 = 0,1, frequency_1_05) as average_1_05,
    count_if(ratio < 0) as frequency_1_00, sum(case when ratio < 0 then LUNA_AMOUNT else 0 end) as volume_1_00, volume_1_00/iff(frequency_1_00 = 0,1, frequency_1_00) as average_1_00,
    count_if(ratio < 1) as frequency_1_01_1, sum(case when ratio < 1 then LUNA_AMOUNT else 0 end) as volume_1_01_1, volume_1_01_1/iff(frequency_1_01_1 = 0,1, frequency_1_01_1) as average_1_01_1,
    count_if(ratio < 0.5) as frequency_1_005, sum(case when ratio < 0.5 then LUNA_AMOUNT else 0 end) as volume_1_005, volume_1_005/iff(frequency_1_005 = 0,1, frequency_1_005) as average_1_005
    from (
    SELECT time_slice(BLOCK_TIMESTAMP, 30, 'SECOND', 'START') as "START OF SLICE",
    time_slice(BLOCK_TIMESTAMP, 30, 'SECOND', 'END') as "END OF SLICE",
    SUM(BLUNA_AMOUNT) AS BLUNA_AMOUNT,
    SUM(LUNA_AMOUNT) AS LUNA_AMOUNT,
    (1 - SUM(LUNA_AMOUNT)/SUM(BLUNA_AMOUNT))*100 AS RATIO
    Run a query to Download Data