with maintable as ( select origin_from_address as User1, tx_hash, symbol, case when contract_address = '0x398ec7346dcd622edc5ae82352f02be94c62d119' then 'AAVE Version 1' when contract_address ='0x7d2768de32b0b80b7a3454c06bdac94a69ddc7a9' then 'AAVE Version 2' end as AVersion, case when AVersion = 'AAVE Version 1' then event_inputs:_reserve::string when AVersion ='AAVE Version 2' then event_inputs:asset::string end as Token, case when AVersion = 'AAVE Version 1' then event_inputs:_amount::int / pow(10,decimals) when AVersion ='AAVE Version 2' then event_inputs:amount::int / pow(10,decimals) end as Price1, Price1 * ifnull (price,0) as USD_Price from ethereum.core.fact_event_logs t1 join ethereum.core.fact_hourly_token_prices t2 on t2.hour = date_trunc('hour',t1.block_timestamp) and case when contract_address = '0x398ec7346dcd622edc5ae82352f02be94c62d119' then t1.event_inputs:_reserve::string = t2.token_address else t1.event_inputs:asset::string = t2.token_address end where contract_address in ('0x7d2768de32b0b80b7a3454c06bdac94a69ddc7a9','0x398ec7346dcd622edc5ae82352f02be94c62d119') and event_name = 'FlashLoan' and tx_status = 'SUCCESS' and (event_inputs:amount is not null or event_inputs:_amount is not null) and block_timestamp >= '2022-01-01'), table1 as ( select User1, count (distinct tx_hash) as TX_Count, sum (USD_Price) as USD_Volume from maintable group by 1) select case when usd_volume < 10 then 'Less Than $10' when usd_volume >= 10 and usd_volume < 50 then 'Between $10 to $50' when usd_volume >= 50 and usd_volume < 100 then 'Between $50 to $100' when usd_volume >= 100 and usd_volume < 200 then 'Between $100 to $200' when usd_volume >= 200 and usd_volume < 500 then 'Between $200 to $500' when usd_volume >= 500 and usd_volume < 1000 then 'Between $500 to $1000' when usd_volume >= 1000 and usd_volume < 2000 then 'Between $1000 to $2000' when usd_volume >= 2000 and usd_volume < 5000 then 'Between $2000 to $5000' when usd_volume >= 5000 and usd_volume < 10000 then 'Between $5000 to $10000' when usd_volume >= 10000 and usd_volume < 50000 then 'Between $10000 to $50000' when usd_volume >= 50000 and usd_volume < 100000 then 'Between $50000 to $100000' else 'More Than $100000' end as type, count (distinct user1) from table1 group by 1 order by 2 desc

Aave - 1. AAVE Flash Loan Users