grahamNEAR stake actions da big project
Updated 2022-10-25Copy 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
›
⌄
with token_prices as (
select
TRUNC(TIMESTAMP,'hour') as timestamp_h,
avg(price_usd) as price_usd
from near.core.fact_prices
where timestamp >= (current_date - {{metric_days}})
AND symbol = 'wNEAR'
group by 1
)
select
tx_signer as user_address,
pool_address as protocol,
'NEAR' as token_contract,
'NEAR' as token_symbol,
sum(case when action = 'Stake' then 1 else 0 end) as n_stakes,
sum(case when action = 'Unstake' then 1 else 0 end) as n_unstakes,
sum(case when action = 'Stake' then stake_amount else 0 end) / pow(10,24) as stake_token_volume,
sum(case when action = 'Stake' then stake_amount / pow(10,24) * b.price_usd else 0 end) as stake_usd_volume,
sum(case when action = 'Unstake' then stake_amount else 0 end) / pow(10,24) as unstake_token_volume,
sum(case when action = 'Unstake' then stake_amount / pow(10,24) * b.price_usd else 0 end) as unstake_usd_volume
from near.core.dim_staking_actions a
left join token_prices b
on TRUNC(a.block_timestamp,'hour') = b.timestamp_h
where block_timestamp >= (current_date - {{metric_days}})
group by 1,2,3,4
Run a query to Download Data