mlhcompare $axl on osmosis and axelar
Updated 2022-12-02
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
29
30
31
32
33
34
35
36
›
⌄
select date_trunc('day', date) as day,
'osmosis' as network,
count(distinct ADDRESS) as wallet,
sum(BALANCE/pow(10,decimal)) as amount
from osmosis.core.fact_daily_balances
where currency='ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E'
group by 1
UNION ALL
(WITH
ins as (
SELECT date_trunc('day', block_timestamp) as day,
receiver as wallet,
sum(amount/pow(10,decimal)) as amount_in
from axelar.core.fact_transfers
where currency ='uaxl' and transfer_type in ('IBC_TRANSFER_IN','AXELAR')
group by 1,2
),
outs as (
SELECT date_trunc('day', block_timestamp) as day,
sender as wallet,
sum(amount/pow(10,decimal)) as amount_out
from axelar.core.fact_transfers
where currency ='uaxl' and transfer_type in ('IBC_TRANSFER_OUT','AXELAR')
group by 1,2
),
holders as (
SELECT
ifnull(ins.day,outs.day) as day,
ifnull(ins.wallet,outs.wallet) as address,
ifnull(amount_in,0)-ifnull(amount_out,0) as balance
from ins
full outer join outs on ins.wallet=outs.wallet and ins.day= outs.day
where amount_in is not null and amount_in>0
and amount_in<1e9 and amount_out<1e9 and amount_out>0
Run a query to Download Data