with aust_mint_raw as ( -- transaction ids for aust deposits select tx_id from flipside_prod_db.anchor.deposits where mint_currency = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' --aust and block_timestamp <= {{date}} ), aust_redeem_raw as ( -- transaction ids for aust redeems select tx_id from flipside_prod_db.anchor.redeem where currency = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' --aust and block_timestamp <= {{date}} ), aust_mint as ( -- aust deposits select sender, sum(mint_amount) as aust_minted from flipside_prod_db.anchor.deposits where mint_currency = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' --aust and block_timestamp <= {{date}} group by sender ), ust_redeem_daily as ( -- calculating aust redeem select block_timestamp::date as day, sender, sum(amount) as ust_redeemed from flipside_prod_db.anchor.redeem where currency = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' and block_timestamp <= {{date}} group by day, sender ), aust_price as ( select block_timestamp::date as day, avg(deposit_amount/mint_amount) as aust_price from flipside_prod_db.anchor.deposits where block_timestamp <= {{date}} group by day ), aust_redeem as ( -- calculating aust redeem select sender as redeemer, sum(aust_redeemed) as aust_redeem from( select u.day, u.sender, ust_redeemed/aust_price as aust_redeemed from ust_redeem_daily u inner join aust_price a on u.day = a.day ) group by sender ), aust_out_non_anchor as ( select sender as sender_non_anc, sum(amounts) as aust_out_non_anchor from( select sender, -amount as amounts from flipside_prod_db.terra.transfer_events where currency = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' -- aUST and tx_id not in (select tx_id from aust_mint_raw) and tx_id not in (select tx_id from aust_redeem_raw) and current_action in ('transfer') and block_timestamp <= {{date}} union all select recipient, amount from flipside_prod_db.terra.transfer_events where currency = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' -- aUST and tx_id not in (select tx_id from aust_mint_raw) and tx_id not in (select tx_id from aust_redeem_raw) and current_action in ('transfer') and block_timestamp <= {{date}} ) group by sender_non_anc ), aust_mirror_inflow as ( -- aust received from repaying mirror loan (aust as collateral) select event_attributes:to as depositor, sum(event_attributes:withdraw_amount[0]:amount) /1e6 as aust_in from terra.msg_events where tx_id = '5BED471691D8C2FA3D1145C0208EF4B4CCFF32B1A3218D8DF14D5F24D7235D11' and event_type = 'from_contract' and event_attributes:"0_action" = 'withdraw' and event_attributes:"0_contract_address" = 'terra1wfz7h3aqf4cjmjcvc6s8lxdhh7k30nkczyf0mj' and event_attributes:withdraw_amount[0]:denom = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' --aUST and tx_status = 'SUCCEEDED' and block_timestamp <= {{date}} group by depositor), net_aust as ( select sender, aust_minted + nvl(aust_out_non_anchor,0) + nvl(aust_in, 0) - nvl(aust_redeem, 0) as net_aust_balance, row_number() over (order by net_aust_balance desc) as rank from aust_mint left outer join aust_out_non_anchor on sender = sender_non_anc left outer join aust_mirror_inflow on sender = depositor left outer join aust_redeem on sender = redeemer --qualify rank <= 1000 ), net_balance as ( select sender, net_aust_balance, CASE WHEN net_aust_balance <= 0 THEN '. Redeemed Initials' -- fully withdrawal WHEN net_aust_balance > 0 AND net_aust_balance <= 1000 THEN 'a. Shrimps <1k' WHEN net_aust_balance > 1000 AND net_aust_balance <= 10000 THEN 'b. Crab 1k-10k' WHEN net_aust_balance > 10000 AND net_aust_balance <= 25000 THEN 'c. Fish I 10k-25k' WHEN net_aust_balance > 25000 AND net_aust_balance <= 50000 THEN 'd. Fish II 25k-50k' WHEN net_aust_balance > 50000 AND net_aust_balance <= pow(10,5) THEN 'e. Fish III 50k-100k' WHEN net_aust_balance > pow(10,5) AND net_aust_balance <= pow(10,6) THEN 'f. Dolphin 100k-1M' WHEN net_aust_balance > pow(10,6) AND net_aust_balance <= pow(10,7) THEN 'g. Shark 1-10M' WHEN net_aust_balance > pow(10,7) AND net_aust_balance <= pow(10,8) THEN 'h. Whale 10-100M' WHEN net_aust_balance > pow(10,8) AND net_aust_balance <= pow(10,9) THEN 'i. Humpback >100M-1B' WHEN net_aust_balance > pow(10,9) THEN 'i. Billionaire >1B' ELSE NULL END AS Tier, case when net_aust_balance < 0 then 0 --- redeemed initial when net_aust_balance > 0 AND net_aust_balance > {{compensation1}}*1000 then {{compensation1}}*1000 when net_aust_balance > 0 AND net_aust_balance <= {{compensation1}}*1000 then net_aust_balance end as compensation1, case when net_aust_balance < 0 then 0 --- redeemed initial when net_aust_balance > 0 AND net_aust_balance > {{compensation2}}*1000 then {{compensation2}}*1000 when net_aust_balance > 0 AND net_aust_balance <= {{compensation2}}*1000 then net_aust_balance end as compensation2, case when net_aust_balance < 0 then 0 --- redeemed initial when net_aust_balance > 0 AND net_aust_balance > {{compensation3}}*1000 then {{compensation3}}*1000 when net_aust_balance > 0 AND net_aust_balance <= {{compensation3}}*1000 then net_aust_balance end as compensation3 from net_aust ) SELECT tier, sum(compensation1) as "Payout 1", sum("Payout 1") over (order by tier) as "Cumulative Payout 1", 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_aust_balance) as "Balance", "Balance"/pow(10,6) as "Balance in Millions", avg(net_aust_balance) as "Avg Balance", median(net_aust_balance) as "Median Balance", count(sender) as "Unique Depositors" FROM net_balance WHERE net_aust_balance > 0 GROUP BY 1 ORDER BY 1

aUST balance + payout required