Tony_IntelJoe staking1(weeklies)
Updated 2023-06-30
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 p as (
select
avg(price) as weekly_price,
date_trunc('week', hour) as week
from avalanche.core.fact_hourly_token_prices
where token_address = lower('0x6e84a6216eA6dACC71eE8E6b0a5B7322EEbC0fDd')
and hour::date >= '2022-02-24'
group by 2
),
sJoe as (
select
sum(amount) over (order by date) as cumulative_amount,
amount,
volume,
date,
'sJoe' as token,
staking_tx_count as weekly_staking_tx_count,
weekly_stakers
from (
select
sum(case when s.from_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then (-s.RAW_AMOUNT/pow(10,18)*p.weekly_price)
when s.to_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then (s.RAW_AMOUNT/pow(10,18)*p.weekly_price) end) as volume,
sum(case when s.from_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then (-s.RAW_AMOUNT/pow(10,18))
when s.to_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then (s.RAW_AMOUNT/pow(10,18)) end) as amount,
--case when s.from_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then 'unstaking'
--when s.to_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then 'staking' end as action,
date_trunc('week', s.block_timestamp) as date,
count(distinct case when s.to_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then tx_hash end) as staking_tx_count,
count(distinct case when s.to_address = '0x1a731b2299e22fbac282e7094eda41046343cb51' then s.from_address end) as weekly_stakers
from avalanche.core.fact_token_transfers s
join p
on date_trunc('week', s.block_timestamp) = p.week
where s.contract_address = lower('0x6e84a6216eA6dACC71eE8E6b0a5B7322EEbC0fDd')
and s.block_timestamp::date >= '2022-02-24'
group by 3
)
Run a query to Download Data