superflyCumulative USD Volume in 2022 by Action
Updated 2022-11-14
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
›
⌄
with price as ( SELECT TIMESTAMP::DATE as TIME, avg (PRICE_USD) as Price from flow.core.fact_prices WHERE TOKEN_CONTRACT = 'A.1654653399040a61.FlowToken' GROUP by 1 )
select
date_trunc('week',block_timestamp) as date,
CASE
WHEN ACTION LIKE '%Unstaked%' THEN 'Untake and Withdraw'
WHEN ACTION LIKE '%Reward%' THEN 'Claim Reward'
WHEN ACTION LIKE '%Committed%' THEN 'Stake and Deposit'
END AS type,
count(distinct tx_id) as TXN, sum (TXN)over (partition by type order by date ) as cum_TXN,
count(distinct DELEGATOR) as Users, sum (Users) over (partition by type order by date ) as cum_Users,
count(distinct node_id) as Nodes, sum (Nodes) over (partition by type order by date ) as cum_Nodes,
sum(amount) as Amounts, sum (Amounts) over (partition by type order by date ) as cum_Amounts,
sum(amount * Price) as USD, sum (USD) over (partition by type order by date ) as cum_USD,
avg(amount) as avg_Amounts, avg(amount* Price) as avg_USD,
TXN / Users as "TXN per Users", TXN / Nodes as "TXN per Nodes",
Amounts / Users as "FLOW per Users", USD / Users as "USD per Users"
from flow.core.ez_staking_actions LEFT JOIN price on TIME = block_timestamp::DATE
where TX_SUCCEEDED = 'TRUE' and block_timestamp >= '2022-01-01' and block_timestamp < CURRENT_DATE
group by 1,2
Run a query to Download Data