with total_depositors as (select sender, sum(deposit_amount) as deposit_amount from anchor.deposits where block_timestamp <= '2022-05-07 12:00:00.000' --sender = 'terra16976x84td8jg2qkpwn8t6ykd586kdh395vn06w' group by sender), total_withdrawals as (select sender, sum(amount) as redeem_amount from anchor.redeem where block_timestamp <= '2022-05-07 12:00:00.000' group by sender), total as ( select a.sender, a.deposit_amount, nvl(b.redeem_amount, 0) as redeem , (a.deposit_amount - redeem) as total_amount, case when total_amount > 0 and total_amount <= 2000 then total_amount when total_amount > 2000 and total_amount <= 5000 then total_amount*0.9 when total_amount > 5000 and total_amount <= 10000 then total_amount*0.85 when total_amount > 10000 and total_amount <= 15000 then total_amount*0.80 when total_amount > 15000 and total_amount <= 20000 then total_amount*0.75 when total_amount > 20000 and total_amount <= 30000 then total_amount*0.70 when total_amount > 30000 and total_amount <= 50000 then total_amount*0.65 when total_amount > 50000 and total_amount <= 100000 then total_amount*0.60 when total_amount > 100000 and total_amount <= 300000 then total_amount*0.55 when total_amount > 300000 and total_amount <= 500000 then total_amount*0.50 when total_amount > 500000 and total_amount <= 800000 then total_amount*0.45 when total_amount > 800000 and total_amount <= 1000000 then total_amount*0.40 when total_amount > 1000000 and total_amount <= 1500000 then total_amount*0.35 when total_amount > 1500000 and total_amount <= 2000000 then total_amount*0.30 when total_amount > 2000000 and total_amount <= 5000000 then total_amount*0.25 when total_amount > 5000000 then 2000000 else 0 end as total_amount_rep, case when total_amount > 0 and total_amount <= 2000 then 1 when total_amount > 2000 and total_amount <= 5000 then 2 when total_amount > 5000 and total_amount <= 10000 then 3 when total_amount > 10000 and total_amount <= 15000 then 4 when total_amount > 15000 and total_amount <= 20000 then 5 when total_amount > 20000 and total_amount <= 30000 then 6 when total_amount > 30000 and total_amount <= 50000 then 7 when total_amount > 50000 and total_amount <= 100000 then 8 when total_amount > 100000 and total_amount <= 300000 then 9 when total_amount > 300000 and total_amount <= 500000 then 10 when total_amount > 500000 and total_amount <= 800000 then 11 when total_amount > 800000 and total_amount <= 1000000 then 12 when total_amount > 1000000 and total_amount <= 1500000 then 13 when total_amount > 1500000 and total_amount <= 2000000 then 14 when total_amount > 2000000 and total_amount <= 5000000 then 15 when total_amount > 500000 then 16 end as category from total_depositors a left join total_withdrawals b on a.sender = b.sender ) /* select category, sum(total_amount_rep), count(distinct sender) from total where sender not in ('terra1yl8l5dzz4jhnzzh6jxq6pdezd2z4qgmgrdt82k', 'terra1cn2fh0k8f5ywzjl2s947xrpdnnerfnkg9qg2r7', 'terra1p3mcl2e4882hztuura0jlmw8nyzaafpcqlnvve', 'terra1w8d3hxkzcl00yutv3ge4rarx8y9rfj7kz6a3v6', 'terra1gquk8c7gt55kxlxraf4h6v2le27hsq2peujy88') group by category ; */ select case when category = 1 then 'Less than 2000' when category = 2 then 'Between 2k and 5k' when category = 3 then 'Between 5k and 10k' when category = 4 then 'Between 10k and 15k' when category = 5 then 'Between 15k and 20k' when category = 6 then 'Between 20k and 30k' when category = 7 then 'Between 30k and 50k' when category = 8 then 'Between 50k and 100k' when category = 9 then 'Between 100k and 300k' when category = 10 then 'Between 300k and 500k' when category = 11 then 'Between 500k and 800k' when category = 12 then 'Between 800k and 1M' when category = 13 then 'Between 1M and 1.5M' when category = 14 then 'Between 1.5M and 2M' when category = 15 then 'Between 2M and 5M' when category = 16 then 'More than 5M' else null end as category_text, category, sum(total_amount), count(distinct sender) as num_accounts_anchor from total where sender not in ('terra1yl8l5dzz4jhnzzh6jxq6pdezd2z4qgmgrdt82k', 'terra1cn2fh0k8f5ywzjl2s947xrpdnnerfnkg9qg2r7', 'terra1p3mcl2e4882hztuura0jlmw8nyzaafpcqlnvve', 'terra1w8d3hxkzcl00yutv3ge4rarx8y9rfj7kz6a3v6', 'terra1gquk8c7gt55kxlxraf4h6v2le27hsq2peujy88') and total_amount>0 group by category_text,category

UST Holders on Terra