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
33
34
35
36
›
⌄
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 amount < 0.01 THEN 'Less than 0.01 FLOW'
WHEN amount BETWEEN 0.01 and 0.1 THEN 'Between 0.01 and 1 FLOW'
WHEN amount BETWEEN 0.1 and 1 THEN 'Between 0.1 and 1 FLOW'
WHEN amount BETWEEN 1 and 10 THEN 'Between 1 and 10 FLOW'
WHEN amount BETWEEN 10 and 100 THEN 'Between 10 and 100 FLOW'
WHEN amount BETWEEN 100 and 500 THEN 'Between 100 and 500 FLOW'
WHEN amount BETWEEN 500 and 1000 THEN 'Between 500 and 1000 FLOW'
ELSE 'More than 1000 FLOW' 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 / Nodes as "USD per Nodes"
from flow.core.ez_staking_actions LEFT JOIN price on TIME = block_timestamp::DATE
where action LIKE '%Unstaked%'
and TX_SUCCEEDED = 'TRUE' and block_timestamp >= '2022-01-01' and block_timestamp < CURRENT_DATE
Run a query to Download Data