theericstoneCopy of Anchor Deposit Duration by Tiers
Updated 2022-01-27
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 deposit AS (
SELECT
msg_value:sender as user,
min(date(block_timestamp)) as earliest_deposit,
max(date(block_timestamp)) as latest_deposit,
count(distinct tx_Id) as deposit_count,
round(median(msg_value:coins[0]:amount/pow(10,6)),1) as median_deposit,
round(avg(msg_value:coins[0]:amount/pow(10,6)),1) as avg_deposit,
sum(msg_value:coins[0]:amount/pow(10,6)) as total_deposit
FROM terra.msgs
WHERE msg_value:contract = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
AND msg_value:execute_msg= '{"deposit_stable":{}}'
AND tx_status = 'SUCCEEDED'
GROUP BY 1
-- 38200FFD0C76F1C4B8AE087E6A85CE6A4CDB4FEE7CF97EAB4F0EFAC4C6BDF636
),
redeem AS (
SELECT
event_attributes:"0_from" as redeemer,
min(date(block_timestamp)) as earliest_redeem,
max(date(block_timestamp)) as latest_redeem,
count(distinct tx_Id) as redeem_count,
-- sum(event_attributes:burn_amount/pow(10,6) as aust_burned,
avg(event_attributes:redeem_amount/pow(10,6))*-1 as avg_ust_reedeemed,
sum(event_attributes:redeem_amount/pow(10,6))*-1 as ust_redeemed
FROM terra.msg_events
WHERE event_attributes:"0_contract_address" = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' -- aust
AND event_attributes:"1_contract_address" = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' -- anchor_market
AND event_type = 'from_contract' AND tx_status = 'SUCCEEDED' AND event_attributes:redeem_amount IS NOT NULL
GROUP BY 1
),
FINAL AS (
SELECT
DATEDIFF(day, earliest_deposit, earliest_redeem) AS days_first_remption,
DATEDIFF(day, earliest_deposit, latest_redeem) AS days_last_remption,
round(total_deposit + ifnull(ust_redeemed,0), 1) as balance, -- if negative, withdrawn all
Run a query to Download Data