NavidCopy of Copy of Untitled Query
Updated 2022-10-26
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
›
⌄
with daily_prices as (
select
date(RECORDED_AT) as day,
address,
avg(PRICE) as price_usd
from
osmosis.core.dim_prices a join osmosis.core.dim_labels b on project_name = symbol
group by
day, address
), daily_banalces as (
select
DATE as day,
a.ADDRESS,
BALANCE/pow(10, DECIMAL)*price_usd as balance_usd,
case
when balance_usd<=1000 then 'Shrimp'
when balance_usd>1000 and balance_usd<=5000 then 'Fish'
when balance_usd>5000 and balance_usd<=10000 then 'Dolphin'
when balance_usd>10000 and balance_usd<=100000 then 'Shark'
else 'Whale'
end as address_type
from
osmosis.core.fact_daily_balances a join daily_prices b on a.date=b.day and a.currency=b.address
--where BALANCE_TYPE in ('locked liquidity', 'superfluid staked', 'staked', 'liquid')
)
select
date_trunc('month', day) as mnth,
address_type,
count(*) as cnt
from
daily_banalces
where day < '2022-10-01' --'2022-10-24'
group by
mnth,
address_type
Run a query to Download Data