rokets5
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
36
›
⌄
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')
), address_type_lps as (
select
date(a.BLOCK_TIMESTAMP) as day,
liquidity_provider_address,
b.address_type,
amount/pow(10, decimal)*price_usd as amount_usd
from
osmosis.core.fact_liquidity_provider_actions a
join daily_banalces b on a.liquidity_provider_address=b.address and date(a.BLOCK_TIMESTAMP)=b.day
join daily_prices c on date(a.BLOCK_TIMESTAMP)=c.day and c.address = a.CURRENCY
where
action in ('pool_joined') --, 'pool_exited', 'lp_tokens_minted', 'lp_tokens_burned')
Run a query to Download Data