drone-mostafatttt copy
Updated 2023-05-27
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
PRICES AS (SELECT RECORDED_HOUR::DATE AS TIME,AVG (CLOSE) AS PRICE
FROM flow.core.fact_hourly_prices WHERE TOKEN = 'Flow'
GROUP BY 1)
SELECT
date_trunc ('week',BLOCK_TIMESTAMP) as Date,
'Redeem' as Type,
count (DISTINCT TX_ID) as Txn,
count (DISTINCT EVENT_DATA:redeemer) as Users,
sum ((EVENT_DATA:scaledRedeemedUnderlyingAmount /pow(10,18)) * PRICE) as USD,
avg ((EVENT_DATA:scaledRedeemedUnderlyingAmount /pow(10,18)) * PRICE) as AVG_USD,
USD / Users as USD_Per_User,
Txn / Users as Txn_Per_User
FROM flow.core.fact_events t1
JOIN flow.core.fact_transactions using (TX_ID) LEFT JOIN PRICES ON BLOCK_TIMESTAMP::DATE = TIME::DATE
WHERE (PAYER = '0x55ad22f01ef568a1' or PROPOSER = '0x55ad22f01ef568a1')
and EVENT_TYPE = 'Redeem'
and EVENT_CONTRACT in ('A.44fe3d9157770b2d.LendingPool',
'A.de7f0150a051a92c.TokenLendingPlace',
'A.8334275bda13b2be.LendingPool',
'A.90f55b24a556ea45.LendingPool',
'A.67539e86cbe9b261.LendingPool',
'A.7492e2f9b4acea9a.LendingPool')
and TX_SUCCEEDED = 'true' GROUP BY 1,2
UNION
SELECT
date_trunc ('week',BLOCK_TIMESTAMP) as Date,
'Repay' as Type,
count (DISTINCT TX_ID) as Txn,
count (DISTINCT EVENT_DATA:borrower) as Users,
sum ((EVENT_DATA:scaledActualRepayAmount /pow(10,18)) * PRICE) as USD,
avg ((EVENT_DATA:scaledActualRepayAmount /pow(10,18)) * PRICE) as AVG_USD,
Run a query to Download Data