Query editor
with view_tokens as (select * from query_4449741),token_prices AS (SELECTdate_trunc('hour', minute) AS dt,a.vtoken_contract_address AS vtoken_contract,a.underlying_token_address AS contract_address,a.symbol,a.vtoken,a.decimals AS decimals,vtoken_decimals AS vtoken_decimals,b.price AS priceFROM view_tokens aINNER JOIN prices.usd b ON a.underlying_token_address = b.contract_addressWHERE b.blockchain = 'avalanche_c' and b.minute >= date '2021-01-01'),exchange_rates_1 AS (SELECT dt, vtoken, vtoken_contract_address, vtoken_decimals, underlying_token_address, symbol, decimals, exchange_rateFROM (SELECTcall_block_time AS dt,contract_address,CASEWHEN contract_address = 0xc9e5999b8e75c3feb117f6f73e664b9f3c8ca65c THEN output_0 / power(10,16)WHEN contract_address = 0xbeb5d47a3f720ec0a390d04b4d41ed7d9688bc7f THEN output_0 / power(10,16)WHEN contract_address = 0xd8fcda6ec4bdc547c0827b8804e89acd817d56ef THEN output_0 / power(10,16)WHEN contract_address = 0xb715808a78f6041e46d61cb123c9b4a27056ae9c THEN output_0 / power(10,16)WHEN contract_address = 0xe194c4c5ac32a3c9ffdb358d9bfd523a0b6d1568 THEN output_0 / power(10,18)ELSE output_0 / POWER(10, 28)END AS exchange_rateFROM benqi_finance_avalanche_c.QiErc20Delegate_call_exchangeRateStored aWHERE call_successUNION ALLSELECT call_block_time AS dt, contract_address, CAST(output_0 AS double) / POWER(10, 28) AS exchange_rateFROM benqi_finance_avalanche_c.QiAvax_call_exchangeRateStored bWHERE call_success) cLEFT JOIN view_tokens d ON c.contract_address = d.vtoken_contract_address),-- exchange_rates_max AS (-- SELECT MAX(dt) AS dt, vtoken_contract_address-- FROM exchange_rates_1-- GROUP BY vtoken_contract_address-- ),-- exchange_rates AS (-- SELECT b.dt, a.vtoken, a.vtoken_contract_address, a.vtoken_decimals, a.underlying_token_address, a.symbol, a.decimals, a.exchange_rate-- FROM exchange_rates_1 a-- JOIN exchange_rates_max b ON a.dt = b.dt AND a.vtoken_contract_address = b.vtoken_contract_address-- ),liquidation_info AS (SELECT *FROM (SELECTa.*,