John_GaltNeb Staking
    Updated 2022-05-06
    with table1 as (select
    date(block_timestamp) as date,
    event_attributes:"from" as address,
    event_attributes:"0_amount" / pow(10, 6) as amount_in,
    null as amount_out
    from terra.msg_events
    where date > '2022-04-01'
    and event_attributes:"1_contract_address" = 'terra1gsq7p9a8uu6wdr78rk9cthz57tzkfzrejhdknf'
    and event_attributes:"1_action" = 'staking'
    and event_index = 3

    union all

    select
    date(block_timestamp) as date,
    event_attributes:recipient as address,
    null as amount_in,
    event_attributes:"0_amount" / pow(10, 6) as amount_out
    from terra.msg_events
    where date > '2022-04-01'
    and event_attributes:"0_contract_address" = 'terra1gsq7p9a8uu6wdr78rk9cthz57tzkfzrejhdknf'
    and event_attributes:"0_action" = 'withdraw'
    and event_index = 3
    ),

    table2 as (select
    date, sum(amount_in) as deposit, sum(amount_out) as withdraw, coalesce(deposit, 0) - coalesce(withdraw, 0) as net_deposit,
    sum(net_deposit) over (order by date) as cumulative_net_in
    from table1
    group by date
    )

    select * from table2