WITH deposit AS ( SELECT sender as user, min(date(block_timestamp)) as earliest_deposit, max(date(block_timestamp)) as latest_deposit, count(distinct tx_Id) as deposit_count, median(deposit_amount) as median_deposit, avg(deposit_amount) as avg_deposit, sum(deposit_amount) as total_deposit FROM anchor.deposits GROUP BY 1 -- 38200FFD0C76F1C4B8AE087E6A85CE6A4CDB4FEE7CF97EAB4F0EFAC4C6BDF636 ), redeem AS ( SELECT sender 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(amount) as avg_ust_reedeemed, sum(amount) as ust_redeemed FROM anchor.redeem 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)) as balance, -- if negative, withdrawn all case when user is not null and redeemer is null then 'Deposited, Never Redeemed' when user is not null and redeemer is not null then 'Deposited and Redeemed' ELSE NULL end as user_type, case when user_type = 'Deposited, Never Redeemed' THEN DATEDIFF(day, latest_deposit, getdate()) else null end AS days, * FROM deposit a LEFT JOIN redeem b on a.user = redeemer ORDER BY DEPOSIT_COUNT DESC ), FINAL_FINAL AS ( SELECT *, ifnull(ust_redeemed*-1,0)/total_deposit*100 as percent_redeemed, CASE WHEN percent_redeemed >= 100 THEN 'a. => 100% redeemed' -- Total redeemed / total depsoited WHEN percent_redeemed >80 AND percent_redeemed < 100 THEN 'b. 80-99.9% redeemed' WHEN percent_redeemed >50 AND percent_redeemed <= 80 THEN 'c. 50-80% redeemed' WHEN percent_redeemed >30 AND percent_redeemed <= 50 THEN 'd. 30-50% redeemed' WHEN percent_redeemed >10 AND percent_redeemed <= 30 THEN 'e. 10-30% redeemed' WHEN percent_redeemed > 0 AND percent_redeemed <= 10 THEN 'f. <10% redeemed' WHEN percent_redeemed = 0 THEN 'g. 0% redeemed' ELSE NULL END AS "Deposit Redeemed %", 1 as count, CASE WHEN total_deposit > 0 AND total_deposit <= 1000 THEN 'a. Shrimps <1k' --- does not account for redeems WHEN total_deposit > 1000 AND total_deposit <= 10000 THEN 'b. Crab 1k-10k' WHEN total_deposit > 10000 AND total_deposit <= 50000 THEN 'c. Fish 10k-50k' WHEN total_deposit > 50000 AND total_deposit <= pow(10,5) THEN 'd. Dolphin 50k-100k' WHEN total_deposit > pow(10,5) AND total_deposit <= pow(10,6) THEN 'e. Shark 100k-1M' WHEN total_deposit > pow(10,6) AND total_deposit <= pow(10,8) THEN 'f. Whale >1M-100M' WHEN total_deposit> pow(10,8) THEN 'g. Humpback >100M' ELSE NULL END AS Tier FROM FINAL ORDER BY deposit_count DESC ) SELECT tier, "Deposit Redeemed %", -- user_type, count(distinct user) as distinct_users, median(balance) as median_balance, -- acounting for redeems sum(total_deposit) as "Total Deposits", sum(ust_redeemed) as "Total Redeems", sum(balance) as "Balance UST", -- account for redeems median(days) as "Median Days for non-Redeemers", -- from first depsoit avg(days) as "Avg Days for non-Redeemers", -- from first depsoit median(days_first_remption) as "Median Days to 1st Redemption", avg(days_first_remption) as "Avg Days to 1st Redemption", median(days_last_remption) as "Median Days to Last Redemption", avg(days_last_remption) as "Avg Days to Last Redemption" FROM FINAL_FINAL GROUP BY 1,2 ORDER BY 1,2

Anchor Deposit Duration