mamad-5XN3k3Swell L2-3
Updated 2024-04-19
999
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 pricet as (
select
date_trunc('hour', hour) as price_date,
TOKEN_ADDRESS,
avg(price) as price
from ethereum.price.ez_hourly_token_prices
where hour::date >= '2024-04-08'
group by 1,2
)
/*select
*,
sum(vol) over (partition by symbol order by dates asc) as cum_vol_sym,
sum(usd_vol) over (partition by symbol order by dates asc) as cum_vol_sym_usd,
sum(depositors) over (partition by symbol order by dates asc) as cum_sym_depositors,
sum(withdrawers) over (partition by symbol order by dates asc) as cum_sym_withdrawers,
sum(depositors) over(order by dates asc) as cum_depositors,
sum(withdrawers) over(order by dates asc) as cum_withdrawers,
sum(vol) over (order by dates asc) as cum_vol,
sum(usd_vol) over(order by dates asc) as cum_usd_vol
from(*/
select
--date_trunc('hour',date) as dates,
symbol,
count(case when type ilike 'deposit' then user else null end) as depositors,
count(case when type ilike 'withdraw' then user else null end) as withdrawers,
round(sum(case when type ilike 'deposit' then volume * price else 0 end),0) as deposit_vol,
round(sum(case when type ilike 'withdraw' then volume * price else 0 end),0) as withdraw_vol,
round(sum(volume * price),0) as tvl
/*sum(count(case when type ilike 'deposit' then user else null end)) over(order by date_trunc('hour',date) asc) as cum_depositors,
sum(count(case when type ilike 'withdraw' then user else null end)) over(order by date_trunc('hour',date) asc) as cum_withdrawers,
--sum(sum(volume * price)) over (order by date_trunc('hour',date) asc) as cum_vol,
sum(sum(volume * price)) over(order by date_trunc('hour',date) asc) as cum_usd_vol*/
from(
select