shaunoffLockdrop Total per Hour copy
    Updated 2023-03-10
    -- forked from f9c9b110-85b8-46ab-adb6-67bec5c620bd

    with table1 as (select
    date_trunc('hour', block_timestamp) as day_hour,
    sum(attributes:wasm:"amount") / pow(10, 6) as luna
    from terra.core.ez_messages
    where date(block_timestamp) > '2023-03-01'
    and attributes:wasm:"_contract_address" = 'terra155pkerrnlufru6h9nt5pxlq6fl8jqvgwwnds8wxjr6danfphulrs9e82gq'
    and attributes:wasm:"action" = 'deposit'
    group by day_hour
    ),

    table2 as (select
    day_hour,
    sum(LUNA) over (order by day_hour) as cum_luna,
    cum_LUNA / 80000000 as capa_luna_price
    from table1
    ),

    table3 as (select
    date_trunc('hour', block_timestamp) as day_hour,
    avg(message_value:msg:swap:"belief_price") as luna_price
    from terra.core.ez_messages
    where date(block_timestamp) > '2023-03-01'
    and message_value:"contract" = 'terra1fd68ah02gr2y8ze7tm9te7m70zlmc7vjyyhs6xlhsdmqqcjud4dql4wpxr'
    and message_value:msg:swap:offer_asset:info:native_token:denom = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4'
    group by day_hour
    order by day_hour
    ),

    final as (select
    table2.day_hour, cum_luna,
    luna_price * capa_luna_price as capa_price
    from table2
    left outer join table3 on table2.day_hour = table3.day_hour
    )