Moe2p773
Updated 2022-12-13Copy 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
32
33
34
35
36
›
⌄
with prices as (
select
RECORDED_AT::date as days , SYMBOL,
avg(price) as prices
from
osmosis.core.dim_prices
where provider = 'coinmarketcap'
group by 1 ,2
)
,base as (
select
l.*, PROJECT_NAME as LABEL
from
osmosis.core.fact_liquidity_provider_actions l , osmosis.core.dim_labels b
where CURRENCY = b.ADDRESS
)
,fin as (
select
b.*,prices,AMOUNT/pow(10,decimal) as AMOUNTs ,
AMOUNTs*prices as AMOUNT_usd
from
base b,prices p
where
BLOCK_TIMESTAMP::date = days and LABEL = SYMBOL
and AMOUNT is not null
)
select
date_trunc(week,BLOCK_TIMESTAMP)::date as week,
count(case when action = 'pool_joined' then LIQUIDITY_PROVIDER_ADDRESS end ) as depositors,
Run a query to Download Data