keshanCopy of Luna whales in ANC and MIR
Updated 2022-03-04
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
›
⌄
with whales as (select address, address_label from
(SELECT address,
(case when address_label is null then 'other' else address_label end) as address_label,
sum(balance) as total,
sum(balance_usd) as total_usd,
total_usd * 100.0 / sum(total_usd) over () as percentage,
sum(case when lower(balance_type) = 'staked' then balance else 0 end) as staked,
sum(case when lower(balance_type) = 'staked' then balance_usd else 0 end) as staked_usd,
staked_usd * 100.0 / sum(staked_usd) over () as staked_percentage
from terra.daily_balances
where date::date = CURRENT_DATE - 1
and currency = 'LUNA'
group by address, address_label
order by total DESC)
where percentage >= 1.0 or staked_percentage >= 1.0)
select address, address_label, (case when msg_value:contract = 'terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5' then 'ANC'
when msg_value:contract = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x' then 'MIR' end) as protocol,
count(msg_value:execute_msg:cast_vote:poll_id)::string as voted
from whales w left join terra.msgs m on m.msg_value:sender = w.address
where (
msg_value:contract = 'terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5' -- ANC
or msg_value:contract = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x' -- MIR
)
and msg_value:execute_msg:cast_vote:poll_id is not null
and block_timestamp >= CURRENT_DATE - 90
group by address, address_label, protocol
Run a query to Download Data