with AVAXPricet as ( select hour::date as day, avg (price) as AVAXPrice from ethereum.price.ez_hourly_token_prices where symbol = 'WAVAX' group by 1), WBTCPricet as ( select hour::date as day, avg (price) as WBTCPrice from ethereum.price.ez_hourly_token_prices where symbol = 'WBTC' group by 1), WETHPricet as ( select hour::date as day, avg (price) as WETHPrice from ethereum.price.ez_hourly_token_prices where symbol = 'WETH' group by 1), LINKPricet as ( select hour::date as day, avg (price) as LINKPrice from ethereum.price.ez_hourly_token_prices where symbol = 'LINK' group by 1), Maintable as ( select tx_hash, origin_from_address as User, block_timestamp, case when event_name = 'Mint' then 'Deposit (Supply)' when event_name = 'Borrow' then 'Borrow' when event_name = 'RepayBorrow' then 'Repay' when event_name = 'Redeem' then 'Withdrawal (Redeem)' end as Action_Type, case when contract_address = '0x5c0401e81bc07ca70fad469b451682c0d747ef1c' then 'AVAX' when contract_address = '0x89a415b3d20098e6a6c8f7a59001c67bd3129821' then 'BTC.b' when contract_address = '0xe194c4c5ac32a3c9ffdb358d9bfd523a0b6d1568' then 'WBTC.e' when contract_address = '0x334ad834cd4481bb02d09615e7c11a00579a7909' then 'WETH.e' when contract_address = '0x4e9f683a27a6bdad3fc2764003759277e93696e6' then 'LINK.e' when contract_address = '0x35bd6aeda81a7e5fc7a7832490e71f757b0cd9ce' then 'QI' when contract_address = '0xf362fea9659cf036792c9cb02f8ff8198e21b4cb' then 'sAVAX' when contract_address = '0xb715808a78f6041e46d61cb123c9b4a27056ae9c' then 'USDC' when contract_address = '0xbeb5d47a3f720ec0a390d04b4d41ed7d9688bc7f' then 'USDC.e' when contract_address = '0xc9e5999b8e75c3feb117f6f73e664b9f3c8ca65c' then 'USDT.e' when contract_address = '0x872670ccae8c19557cc9443eff587d7086b8043a' then 'BUSD' when contract_address = '0x835866d37afb8cb8f8334dccdaf66cf01832ff5d' then 'DAI.e' end as Token_Symbol, case when event_name = 'Redeem' then decoded_log:redeemAmount when event_name = 'Borrow' then decoded_log:borrowAmount when event_name = 'RepayBorrow' then decoded_log:repayAmount when event_name = 'Mint' then decoded_log:mintAmount end as Unadj_Volume, case when Token_Symbol in ('AVAX','WETH.e','LINK.e','QI','BUSD','sAVAX','DAI.e') then Unadj_Volume/1e18 when Token_Symbol in ('BTC.b','WBTC.e') then Unadj_Volume/1e8 when Token_Symbol in ('USDC','USDC.e','USDt','USDT.e') then Unadj_Volume/1e6 end as Adj_Volume, case when Token_Symbol in ('AVAX','sAVAX') then Adj_Volume * AVAXPrice when Token_Symbol in ('BTC.b','WBTC.e') then Adj_Volume * WBTCPrice when Token_Symbol = 'WETH.e' then Adj_Volume * WETHPrice when Token_Symbol = 'LINK.e' then Adj_Volume * LINKPrice when Token_Symbol in ('USDC','USDC.e','USDt','USDT.e','BUSD','DAI.e') then Adj_Volume end as USD_Volume from avalanche.core.ez_decoded_event_logs t1 join AVAXPricet t2 on t1.block_timestamp::date = t2.day join WBTCPricet t3 on t1.block_timestamp::date = t3.day join WETHPricet t4 on t1.block_timestamp::date = t4.day join LINKPricet t5 on t1.block_timestamp::date = t5.day where contract_address = origin_to_address and event_name in ('Redeem','Borrow','RepayBorrow','Mint') and tx_status ilike 'Success' and contract_address in ( '0x5c0401e81bc07ca70fad469b451682c0d747ef1c', -- AVAX '0x89a415b3d20098e6a6c8f7a59001c67bd3129821', -- BTC.B '0xe194c4c5ac32a3c9ffdb358d9bfd523a0b6d1568', -- BTC '0x334ad834cd4481bb02d09615e7c11a00579a7909', -- ETH '0x4e9f683a27a6bdad3fc2764003759277e93696e6', -- LINK '0xb715808a78f6041e46d61cb123c9b4a27056ae9c', -- USDC '0xbeb5d47a3f720ec0a390d04b4d41ed7d9688bc7f', -- USDC '0xd8fcda6ec4bdc547c0827b8804e89acd817d56ef', -- USDT '0xc9e5999b8e75c3feb117f6f73e664b9f3c8ca65c', -- USDT '0x872670ccae8c19557cc9443eff587d7086b8043a', -- BUSD '0x835866d37afb8cb8f8334dccdaf66cf01832ff5d', -- DAI '0xf362fea9659cf036792c9cb02f8ff8198e21b4cb', -- sAVAX '0x35bd6aeda81a7e5fc7a7832490e71f757b0cd9ce' -- QI )) select Action_Type, case when usd_volume < 10 then 'Less Than $10' when usd_volume >= 10 and usd_volume < 100 then '$10 - $100' when usd_volume >= 100 and usd_volume < 1000 then '$100 - $1,000' when usd_volume >= 1000 and usd_volume < 10000 then '$1,000 - $10,000' when usd_volume >= 10000 and usd_volume < 100000 then '$10,000 - $100,000' else 'More Than $100,000' end as type, count (distinct tx_hash) as TX_Count from Maintable where Action_Type = 'Borrow' group by 1,2 union all select Action_Type, case when usd_volume < 10 then 'Less Than $10' when usd_volume >= 10 and usd_volume < 100 then '$10 - $100' when usd_volume >= 100 and usd_volume < 1000 then '$100 - $1,000' when usd_volume >= 1000 and usd_volume < 10000 then '$1,000 - $10,000' when usd_volume >= 10000 and usd_volume < 100000 then '$10,000 - $100,000' else 'More Than $100,000' end as type, count (distinct tx_hash) as TX_Count from Maintable where Action_Type = 'Repay' group by 1,2 union all select Action_Type, case when usd_volume < 10 then 'Less Than $10' when usd_volume >= 10 and usd_volume < 100 then '$10 - $100' when usd_volume >= 100 and usd_volume < 1000 then '$100 - $1,000' when usd_volume >= 1000 and usd_volume < 10000 then '$1,000 - $10,000' when usd_volume >= 10000 and usd_volume < 100000 then '$10,000 - $100,000' else 'More Than $100,000' end as type, count (distinct tx_hash) as TX_Count from Maintable where Action_Type = 'Deposit (Supply)' group by 1,2 union all select Action_Type, case when usd_volume < 10 then 'Less Than $10' when usd_volume >= 10 and usd_volume < 100 then '$10 - $100' when usd_volume >= 100 and usd_volume < 1000 then '$100 - $1,000' when usd_volume >= 1000 and usd_volume < 10000 then '$1,000 - $10,000' when usd_volume >= 10000 and usd_volume < 100000 then '$10,000 - $100,000' else 'More Than $100,000' end as type, count (distinct tx_hash) as TX_Count from Maintable where Action_Type = 'Withdrawal (Redeem)' group by 1,2 order by 3 desc

Avalanche's BenQi Dashboard