Query editor
with transfer_detail as (select evt_block_time, "to" as address, cast(value as double) as value, evt_tx_hashfrom erc20_ethereum.evt_Transferwhere contract_address = 0x8290333ceF9e6D528dD5618Fb97a76f268f3EDD4 --ankr tokenand "to" != 0x0000000000000000000000000000000000000000 -- Null Addressand "to" != 0x9c2578bf9e37eb1d0ddf45a410723d95e554cbdd --Team Vested Contractand evt_block_time >= date('2019-02-21') -- contract deployedunion allselect evt_block_time, "from" as address, (-1)*cast(value as double) as value, evt_tx_hashfrom erc20_ethereum.evt_Transferwhere contract_address = 0x8290333ceF9e6D528dD5618Fb97a76f268f3EDD4 --ankr tokenand "from" != 0x0000000000000000000000000000000000000000 -- Null Addressand "from" != 0x9c2578bf9e37eb1d0ddf45a410723d95e554cbdd -- Team Vested Contractand evt_block_time >= date('2019-02-21') -- contract deployed),holder_balance_daily as (select date_trunc('day', evt_block_time) as time, address, sum(value/1e18) as balance_amountfrom transfer_detailgroup by 1, 2),holder_summary_daily as (select time, address, sum(balance_amount) over (partition by address order by time) as balance_amountfrom holder_balance_dailyorder by 1, 2),min_max_date as (select min(time) as start_date, max(time) as end_datefrom holder_balance_daily),date_series as (select dt.timefrom min_max_date as mmcross join unnest(sequence(date(mm.start_date), date(mm.end_date), interval '1' day)) as dt(time)),holder_balance_until_date as (select distinct d.time, w.address, first_value(balance_amount) over (partition by w.address order by w.time desc) as last_balance_amount -- get the last balance of same address on same date or before (when no date on same date)from date_series dinner join holder_summary_daily w on w.time <= d.time),holder_count_summary as (select time, count(address) as holder_count, sum(last_balance_amount) as balance_amountfrom holder_balance_until_datewhere last_balance_amount > 0.0000000001 -- dust includedgroup by time)select time, holder_count, balance_amount as total_holder_balance, (holder_count - lag(holder_count, 1) over (order by time)) as holder_count_change,(balance_amount - lag(balance_amount, 1) over (order by time)) as balance_amount_changefrom holder_count_summaryorder by time desc