with deposit AS ( -- find users that deposited SELECT sender as depositor, max(block_timestamp) as max_date, sum(mint_amount) as total_aust, sum(deposit_amount) as total_ust FROM anchor.deposits WHERE block_timestamp < {{date}} GROUP BY 1 ), redeem AS ( -- find users that redeemed select sender as borrower, max(block_timestamp) as max_date_redeem, sum(amount) as ust_redeemed FROM anchor.redeem WHERE block_timestamp < {{date}} GROUP BY 1 ), net_ust AS ( -- redeem - deposit select *, round(total_ust - ifnull(ust_redeemed,0), 0) as net_ust, CASE WHEN net_ust <= 0 THEN '. Redeemed Initials' -- fully withdrawal WHEN net_ust > 0 AND net_ust <= 1000 THEN 'a. Shrimps <1k' WHEN net_ust > 1000 AND net_ust <= 10000 THEN 'b. Crab 1k-10k' WHEN net_ust > 10000 AND net_ust <= 25000 THEN 'c. Fish I 10k-25k' WHEN net_ust > 25000 AND net_ust <= 50000 THEN 'd. Fish II 25k-50k' WHEN net_ust > 50000 AND net_ust <= pow(10,5) THEN 'e. Fish III 50k-100k' WHEN net_ust > pow(10,5) AND net_ust <= pow(10,6) THEN 'f. Dolphin 100k-1M' WHEN net_ust > pow(10,6) AND net_ust <= pow(10,7) THEN 'g. Shark 1-10M' WHEN net_ust > pow(10,7) AND net_ust <= pow(10,8) THEN 'h. Whale 10-100M' WHEN net_ust > pow(10,8) AND net_ust <= pow(10,9) THEN 'i. Humpback >100M-1B' WHEN net_ust > pow(10,9) THEN 'i. Billionaire >1B' ELSE NULL END AS Tier, case when net_ust < 0 then 0 --- redeemed initial when net_ust > 0 AND net_ust > {{compensation1}}*1000 then {{compensation1}}*1000 when net_ust > 0 AND net_ust <= {{compensation1}}*1000 then net_ust end as compensation1, case when net_ust < 0 then 0 --- redeemed initial when net_ust > 0 AND net_ust > {{compensation2}}*1000 then {{compensation2}}*1000 when net_ust > 0 AND net_ust <= {{compensation2}}*1000 then net_ust end as compensation2, case when net_ust < 0 then 0 --- redeemed initial when net_ust > 0 AND net_ust > {{compensation3}}*1000 then {{compensation3}}*1000 when net_ust > 0 AND net_ust <= {{compensation3}}*1000 then net_ust end as compensation3 FROM deposit LEFT JOIN redeem on depositor = borrower -- left join by address (assumption is users won't tx aust away) ) SELECT -- group by tier tier, sum(compensation1) as "Payout 1", -- how much payout it will take per tier sum("Payout 1") over (order by tier) as "Cumulative Payout 1", -- how much pay out over all the tiers sum(compensation2) as "Payout 2", sum("Payout 2") over (order by tier) as "Cumulative Payout 2", sum(compensation3) as "Payout 3", sum("Payout 3") over (order by tier) as "Cumulative Payout 3", sum(net_ust) as "Balance", "Balance"/pow(10,6) as "Balance in Millions", avg(net_ust) as "Avg Balance", median(net_ust) as "Median Balance", count(distinct depositor) as "Unique Depositors" FROM net_ust WHERE net_ust > 0 GROUP BY 1 ORDER BY 1

Anchor Compensation