gokcinaxl tokens
Updated 2022-12-03Copy Reference Fork
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
›
⌄
with buying as (select min(block_timestamp) as first_transaction,trader
from osmosis.core.fact_swaps
where to_currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E' and tx_status = 'SUCCEEDED'
group by 2),
buyers as (select date_trunc('day', first_transaction) as date,
count(distinct(trader)) as wallet_count, sum(wallet_count) over (order by date) as cum_wallet_count from buying group by 1),
holders as (select max(date) as date,
address
from osmosis.core.fact_daily_balances
where currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E'
and balance > 0
group by 2),
rendered as (select date_trunc('day', block_timestamp) as date,count(distinct(tx_id)) as tx_count,sum(tx_count) over (order by date) as cum_tx_count,
sum(to_amount/pow(10, to_decimal)) as axl_volume,
sum(axl_volume) over (order by date) as cum_axl_volume
from osmosis.core.fact_swaps where to_currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E'and tx_status = 'SUCCEEDED'
group by 1)
select a.date,case when count(distinct(address)) < 1 then '0' else count(distinct(address)) end as holders,
wallet_count, count(distinct(address)) / cum_wallet_count * 100 as percentage_holders,
cum_wallet_count,
tx_count,
cum_tx_count,
axl_volume,
cum_axl_volume
from buyers a
join rendered b on a.date = b.date join holders
group by 1,3,5,6,7,8,9
order by 1 desc
Run a query to Download Data