WITH token_map AS ( SELECT COLUMN1 AS TOKEN_CONTRACT, COLUMN2 AS asset, COLUMN3 AS borrow_pow, COLUMN4 AS tvl_pow FROM (VALUES ('0xdd89c0e695df0692205912fb69fc290418bed0dbe6e4573d744a6d5e6bab6c13::coin::T', 'SOL', 26, 8), ('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WBTC', 'zWBTC', 24, 6), ('0xcfea864b32833f157f042618bd845145256b1bf4c0da34a7013b76e42daa53cc::usdy::USDY', 'USDY', 24, 6), ('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC', 'zUSDC', 24, 6), ('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT', 'zUSDT', 24, 6), ('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH', 'zWETH', 24, 6), ('0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3::fa_to_coin_wrapper::WrappedUSDT', 'USDt', 24, 6), ('0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T', 'whUSDC', 24, 6), ('0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin', 'tAPT', 26, 8), ('0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T', 'whWETH', 26, 8), ('0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt', 'amAPT', 26, 8), ('0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT', 'CAKE', 26, 8), ('0x1::aptos_coin::AptosCoin', 'APT', 26, 8), ('0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt', 'stAPT', 26, 8), ('0xa2eda21a58856fda86451436513b867c97eecb4ba099da5775520e0f7492e852::coin::t','whUSDT', 24, 6), ('0x6684d15d691e3e5edbf3474eba252e0dd29b7dfbefa6cc7c2e00811fd454c75a::tether::usdt','USDt', 24, 6), ('0x357b0b74bc833e95a115ad22604854d6b0fca151cecd94111770e5d6ffc9dc2b','USDt', 24, 6), ('0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC','aBTC', 28, 10), ('0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD','MOD', 26, 8), ('0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL','THL', 26, 8), ('0xfaf4e633ae9eb31366c9ca24214231760926576c7b625313b3688b5e900731f6::staking::StakedThalaAPT','sthAPT', 26, 8), ('0xaef6a8c3182e076db72d64324617114cacf9a52f28325edc10b483f7f05da0e7','TruAPT', 26, 8) ) ) , depsitEvent AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address as vault_add, ACCOUNT_ADDRESS, 'Deposit' as EVENT_TYPE, iff(EVENT_RESOURCE ILIKE 'DepositEvent<%',REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', ''), NULL) as coll_token_address, iff(EVENT_RESOURCE ILIKE 'DepositEvent<%', EVENT_DATA:amount, 0) as deposit_amt_raw FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::open_vault' AND EVENT_RESOURCE ILIKE 'DepositEvent<%' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' UNION ALL SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Deposit' as EVENT_TYPE, REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', '') AS coll_token_address, EVENT_DATA:amount AS deposit_amt_raw FROM aptos.core.fact_events WHERE ((PAYLOAD_FUNCTION ILIKE '%::deposit_collateral') OR (EVENT_TYPE ILIKE '%::vault::DepositEvent<%' AND ACCOUNT_ADDRESS = '0x46071dacabc07979c25c709ce902d9d86c54cc6dfcd8892ab01aa4858177cbf8') ) AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' ), deposit_event_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, vault_add, ACCOUNT_ADDRESS, EVENT_TYPE, coll_token_address, asset AS collat_asset, deposit_amt_raw * POW(10, -(tvl_pow)) AS deposit_amt FROM depsitEvent JOIN token_map on TOKEN_CONTRACT = coll_token_address ), borrowEvent AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Borrow' as EVENT_TYPE, iff(EVENT_RESOURCE ILIKE 'BorrowEvent<%','0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD', NULL) as borrow_token_add, asset as borrow_asset, iff(EVENT_RESOURCE ILIKE 'BorrowEvent<%', EVENT_DATA:amount, 0) as borrow_amt_raw FROM aptos.core.fact_events JOIN token_map on TOKEN_CONTRACT = borrow_token_add WHERE PAYLOAD_FUNCTION ILIKE '%::open_vault' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' AND EVENT_RESOURCE NOT ILIKE 'DepositEvent<%' UNION ALL SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Borrow' as EVENT_TYPE, '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' AS borrow_token_add, 'MOD' AS borrow_asset, EVENT_DATA:amount AS borrow_amt_raw, FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::borrow' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' ), borrow_event_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, vault_add, ACCOUNT_ADDRESS, EVENT_TYPE, borrow_token_add, asset as borrow_asset, borrow_amt_raw * POW(10, -(tvl_pow)) AS borrow_amt FROM borrowEvent JOIN token_map on TOKEN_CONTRACT = borrow_token_add ), witdraw_collat_event AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Withdraw' as EVENT_TYPE, iff(EVENT_RESOURCE ILIKE 'WithdrawEvent<%',REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', ''), NULL) AS withdraw_token_add, iff(EVENT_RESOURCE ILIKE 'WithdrawEvent<%', EVENT_DATA:amount, 0) AS withdraw_amt_raw FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::close_vault' AND EVENT_RESOURCE ILIKE 'WithdrawEvent<%' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' UNION ALL SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Withdraw' AS EVENT_TYPE, REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', '') AS withdraw_token_add, EVENT_DATA:amount AS withdraw_amt_raw FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::withdraw_collateral' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' UNION ALL SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Withdraw' AS EVENT_TYPE, iff(EVENT_RESOURCE ILIKE 'WithdrawEvent<%',REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', ''), NULL) AS withdraw_token_add, iff(EVENT_RESOURCE ILIKE 'WithdrawEvent<%', EVENT_DATA:amount, 0) AS withdraw_amt_raw FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::liquidate' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' AND EVENT_RESOURCE NOT LIKE 'LiquidationEvent<%' AND EVENT_RESOURCE ILIKE 'WithdrawEvent<%' ), withdraw_event_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, vault_add, ACCOUNT_ADDRESS, EVENT_TYPE, withdraw_token_add, asset AS withdraw_asset, withdraw_amt_raw * POW(10, -(tvl_pow)) AS withdraw_amt FROM witdraw_collat_event JOIN token_map on TOKEN_CONTRACT = withdraw_token_add ), repayEvent AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Repay' AS EVENT_TYPE, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%','0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD', NULL) AS repay_token_add, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%','MOD', NULL) AS repay_asset, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%', EVENT_DATA:amount, 0) AS repay_amt_raw, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%', EVENT_DATA:interest_paid, 0) AS repay_interest_paid FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::liquidate' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' AND EVENT_RESOURCE NOT LIKE 'LiquidationEvent<%' AND EVENT_RESOURCE NOT ILIKE 'WithdrawEvent<%' UNION ALL SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Repay' AS EVENT_TYPE, '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' AS repay_token_add, 'MOD' AS repay_asset, EVENT_DATA:amount AS repay_amt_raw, EVENT_DATA:interest_paid AS repay_interest_paid, FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::repay' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' UNION ALL SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:vault_address AS vault_add, ACCOUNT_ADDRESS, 'Repay' EVENT_TYPE, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%','0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD', NULL) AS repay_token_add, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%','MOD', NULL) AS repay_asset, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%', EVENT_DATA:amount, 0) AS repay_amt_raw, iff(EVENT_RESOURCE ILIKE 'RepayEvent<%', EVENT_DATA:interest_paid, 0) AS repay_interest_paid FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::liquidate' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'vault' AND EVENT_RESOURCE NOT LIKE 'LiquidationEvent<%' AND EVENT_RESOURCE NOT ILIKE 'WithdrawEvent<%' ), repay_event_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, vault_add, ACCOUNT_ADDRESS, EVENT_TYPE, repay_token_add, repay_asset, repay_amt_raw * POW(10, -(tvl_pow)) AS repay_amt, repay_interest_paid * POW(10, -(tvl_pow)) AS repay_interest_paid FROM repayEvent JOIN token_map on TOKEN_CONTRACT = repay_token_add ), mod_mint_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, 'PSM Mint' AS EVENT_TYPE, REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', '') AS mint_token_add, EVENT_DATA:coin_mint_amt AS amt_in_raw, EVENT_DATA:mod_minted AS mint_mod_raw, '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' AS mod_token_contract, 'MOD' AS mint_asset, FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::mint' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'psm' ), mintEvent AS ( SELECT psm.*, event.ACCOUNT_ADDRESS AS ACCOUNT_ADDRESS FROM mod_mint_Tb psm JOIN aptos.core.fact_events event ON (psm.TX_HASH = event.TX_HASH) WHERE event.EVENT_RESOURCE = 'DepositEvent' AND event.EVENT_MODULE = 'coin' AND event.ACCOUNT_ADDRESS != '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' ), mint_event_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, mint_token_add, mint_asset AS mint_symbol, amt_in_raw * POW(10, -(tvl_pow)) AS mint_amt, mint_mod_raw * POW(10, -(tvl_pow)) AS mod_mint_amt FROM mintEvent JOIN token_map on TOKEN_CONTRACT = mint_token_add ), mod_redeem_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, 'PSM Redeem' AS EVENT_TYPE, REPLACE(SPLIT(EVENT_TYPE, '<')[1], '>', '') AS redeem_token_add, EVENT_DATA:coin_amount_out AS redeem_amt_raw, EVENT_DATA:mod_burned AS redeem_mod_raw, '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' AS mod_token_contract, 'MOD' AS redeem_asset, FROM aptos.core.fact_events WHERE PAYLOAD_FUNCTION ILIKE '%::redeem' AND SUCCESS = TRUE AND EVENT_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' AND EVENT_MODULE = 'psm' ), redeenEvent AS ( SELECT psm.*, event.ACCOUNT_ADDRESS AS ACCOUNT_ADDRESS FROM mod_redeem_Tb psm JOIN aptos.core.fact_events event ON (PSM.TX_HASH = event.TX_HASH) WHERE event.EVENT_RESOURCE = 'DepositEvent' AND event.EVENT_MODULE = 'coin' AND event.ACCOUNT_ADDRESS != '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01' ), redeem_event_Tb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, redeem_token_add, asset, redeem_amt_raw * POW(10, -(tvl_pow)) AS redeem_amt, redeem_mod_raw * POW(10, -(tvl_pow)) AS mod_redeem_amt FROM redeenEvent JOIN token_map on TOKEN_CONTRACT = redeem_token_add ), sthAPT_price AS ( SELECT TO_TIMESTAMP(VALUE[0]::STRING) AS DATE, '0xfaf4e633ae9eb31366c9ca24214231760926576c7b625313b3688b5e900731f6::staking::StakedThalaAPT' AS TOKEN_ADDRESS, 'sthAPT' AS symbol, VALUE[1] AS PRICE_USD FROM (SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/staked-thala-apt/market_chart?vs_currency=usd&days=180&interval=daily&precision=9') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ), stAPT_price AS ( SELECT TO_TIMESTAMP(VALUE[0]::STRING) AS DATE, '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' AS TOKEN_ADDRESS, 'stAPT' AS symbol, VALUE[1] AS PRICE_USD FROM (SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/amnis-staked-aptos-coin/market_chart?vs_currency=usd&days=180&interval=daily&precision=9') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ), sthAPT_swap_price AS ( SELECT BLOCK_TIMESTAMP::DATE AS DATE, TOKEN_IN AS TOKEN_ADDRESS, SYMBOL_IN AS symbol, AVG((AMOUNT_OUT_USD/AMOUNT_IN)) AS PRICE_USD FROM aptos.defi.ez_dex_swaps WHERE TOKEN_IN = '0xfaf4e633ae9eb31366c9ca24214231760926576c7b625313b3688b5e900731f6::staking::StakedThalaAPT' AND TOKEN_OUT = '0x1::aptos_coin::AptosCoin' AND AMOUNT_OUT_USD IS NOT NULL AND AMOUNT_IN IS NOT NULL AND BLOCK_TIMESTAMP::DATE >= '2024-01-01' AND BLOCK_TIMESTAMP::DATE <= '2024-03-18' GROUP BY 1, 2, 3 ORDER BY 1 ), stAPT_swap_price AS ( SELECT BLOCK_TIMESTAMP::DATE AS DATE, TOKEN_IN AS TOKEN_ADDRESS, SYMBOL_IN AS symbol, AVG((AMOUNT_OUT_USD/AMOUNT_IN)) AS PRICE_USD FROM aptos.defi.ez_dex_swaps WHERE TOKEN_IN = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' AND TOKEN_OUT = '0x1::aptos_coin::AptosCoin' AND AMOUNT_OUT_USD IS NOT NULL AND AMOUNT_IN IS NOT NULL AND BLOCK_TIMESTAMP::DATE >= '2024-01-01' AND BLOCK_TIMESTAMP::DATE <= '2024-03-18' GROUP BY 1, 2, 3 ORDER BY 1 ), sthAPT_price_agg AS ( SELECT * FROM sthAPT_price UNION ALL SELECT * FROM sthAPT_swap_price UNION ALL SELECT * FROM stAPT_price UNION ALL SELECT * FROM stAPT_swap_price ), usdy_price AS ( SELECT TO_TIMESTAMP(VALUE[0]::STRING) AS DATE, '0xcfea864b32833f157f042618bd845145256b1bf4c0da34a7013b76e42daa53cc::usdy::USDY' AS TOKEN_ADDRESS, 'USDY' AS symbol, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/ondo-us-dollar-yield/market_chart?vs_currency=usd&days=360&interval=daily&precision=9') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ), wtbt_swap_price AS ( SELECT BLOCK_TIMESTAMP::DATE AS DATE, TOKEN_IN AS TOKEN_ADDRESS, SYMBOL_IN AS symbol, AVG((AMOUNT_OUT_USD/AMOUNT_IN)) AS PRICE_USD FROM aptos.defi.ez_dex_swaps WHERE TOKEN_IN = '0xd916a950d4c1279df4aa0d6f32011842dc5c633a45c11ac5019232c159d115bb::coin::T' AND AMOUNT_OUT_USD IS NOT NULL AND AMOUNT_IN IS NOT NULL AND BLOCK_TIMESTAMP::DATE >= '2023-05-05' GROUP BY 1, 2, 3 ORDER BY 1 ), prices AS ( SELECT HOUR::DATE AS DATE, TOKEN_ADDRESS, SYMBOL AS symbol, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN ( '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH', '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT', '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T', '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt', '0x1::aptos_coin::AptosCoin' ) AND HOUR::DATE >= '2023-01-01' GROUP BY 1, 2, 3 ORDER BY 1 ), priceTb AS ( SELECT * FROM prices UNION ALL SELECT * FROM wtbt_swap_price UNION ALL SELECT * FROM sthAPT_price_agg UNION ALL SELECT * FROM usdy_price ), deposit_event_lst1 AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, coll_token_address as token_contract, collat_asset as asset, deposit_amt AS amount FROM deposit_event_Tb ), withdraw_event_lst1 AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, withdraw_token_add as token_contract, withdraw_asset as asset, withdraw_amt AS AMOUNT FROM withdraw_event_Tb ), borrow_event_lst1 AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, borrow_token_add as token_contract, borrow_asset as asset, borrow_amt AS AMOUNT FROM borrow_event_Tb ), repay_event_lst1 AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, repay_token_add as token_contract, repay_asset as asset, repay_amt AS AMOUNT FROM repay_event_Tb ), psm_mint_event_lst1 AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, mint_token_add as token_contract, mint_symbol AS asset, mint_amt AS AMOUNT FROM mint_event_Tb ), psm_redeem_event_lst1 AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, redeem_token_add as token_contract, asset, redeem_amt AS AMOUNT FROM redeem_event_Tb ), aggregatedTb AS ( SELECT * FROM deposit_event_lst1 UNION ALL SELECT * FROM withdraw_event_lst1 UNION ALL SELECT * FROM borrow_event_lst1 UNION ALL SELECT * FROM repay_event_lst1 UNION ALL SELECT * FROM psm_mint_event_lst1 UNION ALL SELECT * FROM psm_redeem_event_lst1 ), current_date_price AS ( SELECT DATE, TOKEN_ADDRESS, PRICE_USD FROM priceTb QUALIFY ROW_NUMBER() OVER (PARTITION BY TOKEN_ADDRESS ORDER BY DATE DESC) = 1 ), amounts_in_usd AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, ACCOUNT_ADDRESS, EVENT_TYPE, asset, token_contract, AMOUNT, CASE WHEN asset IN ('whUSDC', 'zUSDC', 'zUSDT', 'MOD') then amount ELSE AMOUNT * PRICE_USD END AS AMOUNT_USD FROM aggregatedTb LEFT JOIN current_date_price p ON (token_contract = p.TOKEN_ADDRESS) ), finalTb AS ( SELECT asset, SUM(CASE WHEN EVENT_TYPE = 'Deposit' THEN AMOUNT ELSE 0 END) AS deposited_amt, SUM(CASE WHEN EVENT_TYPE = 'Withdraw' THEN AMOUNT ELSE 0 END) AS withdrawn_amt, SUM(CASE WHEN EVENT_TYPE = 'Deposit' THEN AMOUNT_USD ELSE 0 END) AS deposited_amt_usd, SUM(CASE WHEN EVENT_TYPE = 'Withdraw' THEN AMOUNT_USD ELSE 0 END) AS withdrawn_amt_usd, deposited_amt - withdrawn_amt AS "Total Collateral", deposited_amt_usd - withdrawn_amt_usd AS "Total Collateral[$]" FROM amounts_in_usd WHERE EVENT_TYPE IN ('Deposit', 'Withdraw') GROUP BY 1 ) , thala_Tb as ( SELECT asset as "Asset", ROUND("Total Collateral", 2) AS "Total Supply", ROUND("Total Collateral[$]", 2) AS "Total Supply[$]" FROM finalTb WHERE asset IN('APT','sthAPT','tAPT') ORDER BY 2 DESC ) ---------------------------- -----------Aptin------------ ---------------------------- , aptin_Tb AS ( SELECT CASE when EVENT_DATA:coin_name ilike '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' then 'tAPT' when EVENT_DATA:coin_name ilike '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' then 'amAPT' when EVENT_DATA:coin_name ilike '0x1::aptos_coin::AptosCoin' then 'APT' when EVENT_DATA:coin_name ilike '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' then 'stAPT' end as "Asset", round(EVENT_DATA:supply_total_value/1e8, 2) as "Market Size", round(EVENT_DATA:supply_total_value/1e8, 2)* PRICE_USD as "Market Size[$]", round(EVENT_DATA:borrow_total_value/1e8, 2) as "Total Borrowed", round((("Market Size") - ("Total Borrowed")), 2) AS "Total Value Locked" FROM aptos.core.fact_events LEFT JOIN current_date_price p ON (EVENT_DATA:coin_name ilike p.TOKEN_ADDRESS) WHERE EVENT_TYPE = '0x3c1d4a86594d681ff7e5d5a233965daeabdc6a15fe5672ceeda5260038857183::pool::OperEvent' AND EVENT_DATA:coin_name IN ( '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' ,'0x1::aptos_coin::AptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' ) AND SUCCESS = TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY EVENT_DATA:coin_name ORDER BY BLOCK_TIMESTAMP DESC)=1 ) ---------------------------- -----------Aries------------ ---------------------------- , eventTb AS ( SELECT TX_HASH, BLOCK_TIMESTAMP, split(split(EVENT_TYPE, '<')[1],'>')[0] as TOKEN_CONTRACT, EVENT_DATA:total_cash_available AS total_cash_available, EVENT_DATA:total_borrowed_decimal AS total_borrowed_decimal, ROW_NUMBER() OVER(PARTITION BY TX_HASH ORDER BY EVENT_INDEX DESC) AS RANK FROM aptos.core.fact_events WHERE EVENT_TYPE ILIKE '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3::reserve::SyncReserveDetailEvent%' AND EVENT_MODULE = 'reserve' ) , aries_Tb AS ( SELECT TOKEN_CONTRACT, total_cash_available, total_borrowed_decimal FROM eventTb WHERE RANK = 1 QUALIFY ROW_NUMBER() OVER (PARTITION BY TOKEN_CONTRACT ORDER BY BLOCK_TIMESTAMP DESC) = 1 ) , aries_lst as ( SELECT case when TOKEN_CONTRACT ilike '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' then 'tAPT' when TOKEN_CONTRACT ilike '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' then 'amAPT' when TOKEN_CONTRACT ilike '0x1::aptos_coin::AptosCoin' then 'APT' when TOKEN_CONTRACT ilike '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' then 'stAPT' end as "Asset", round((total_borrowed_decimal / POW(10,26)),2) AS TOTAL_BORROWED, round((total_cash_available / POW(10,8)),2) AS TVL, TOTAL_BORROWED + TVL AS MARKET_SIZE, (TOTAL_BORROWED + TVL)* PRICE_USD AS MARKET_SIZE_USD FROM aries_Tb LEFT JOIN aptos.price.ez_asset_metadata ON (TOKEN_ADDRESS = LOWER(TOKEN_CONTRACT)) LEFT JOIN current_date_price p ON (TOKEN_CONTRACT ilike p.TOKEN_ADDRESS) WHERE TOKEN_CONTRACT IN ( '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' ,'0x1::aptos_coin::AptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' ) ORDER by 2 DESC ) ---------------------------- -----------Meso------------- ---------------------------- , mesoEventTb AS ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA:user AS USER_ADDRESS, EVENT_RESOURCE, EVENT_DATA:token::string as TOKEN_ADDRESS, CASE WHEN EVENT_DATA:token = '0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T' THEN 'whUSDC' WHEN EVENT_DATA:token = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC' THEN 'zUSDC' WHEN EVENT_DATA:token = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT' THEN 'zUSDT' WHEN EVENT_DATA:token ilike '0x357b0b74bc833e95a115ad22604854d6b0fca151cecd94111770e5d6ffc9dc2b%' THEN 'USDt' ELSE SYMBOL END AS "Asset", EVENT_DATA:amount/POW(10,8) AS AMOUNT FROM aptos.core.fact_events LEFT JOIN aptos.core.dim_tokens ON (EVENT_DATA:token = TOKEN_ADDRESS) WHERE EVENT_ADDRESS = '0x68476f9d437e3f32fd262ba898b5e3ee0a23a1d586a6cf29a28add35f253f6f7' AND EVENT_MODULE = 'lending_pool' AND EVENT_DATA:token::string IN ( '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' ,'0x1::aptos_coin::AptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' ) AND EVENT_MODULE = 'lending_pool' AND EVENT_RESOURCE IN ('Deposit','Withdraw') AND SUCCESS = TRUE ), mesoBorrowTb AS ( SELECT "Asset", SUM(AMOUNT) AS SUPPLIED_AMOUNT, SUM(AMOUNT*PRICE_USD) AS SUPPLIED_AMOUNT_USD FROM mesoEventTb t LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) WHERE EVENT_RESOURCE IN ('Deposit') GROUP BY 1 ), mesoRepayTb AS ( SELECT "Asset", SUM(AMOUNT) AS WITHDRAWN_AMOUNT, SUM(AMOUNT*PRICE_USD) AS WITHDRAWN_AMOUNT_USD FROM mesoEventTb t LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) WHERE EVENT_RESOURCE IN ('Withdraw') GROUP BY 1 ) , meso_final AS ( SELECT BR."Asset", SUPPLIED_AMOUNT - WITHDRAWN_AMOUNT AS "Total supplied", SUPPLIED_AMOUNT_USD - WITHDRAWN_AMOUNT_USD AS "Total supplied[$]" FROM mesoBorrowTb BR LEFT JOIN mesoRepayTb USING ("Asset") ), meso_lst AS ( SELECT "Asset", IFF("Total supplied"<0, 0, "Total supplied") AS "Total Supplied", IFF("Total supplied[$]"<0, 0, "Total supplied[$]") AS "Total Supplied[$]" FROM meso_final ) ---------------------------- ----------Echelon----------- ---------------------------- , -- echelonEventTb as ( -- WITH echelon_token_map AS ( SELECT COLUMN1 AS POOL_CONTRACT, COLUMN2 AS TOKEN_CONTRACT, COLUMN3 AS asset, COLUMN4 AS borrow_pow, COLUMN5 AS tvl_pow FROM (VALUES ('0xa9c51ca3bcd93978d0c4aada7c4cf47c0791caced3cdc4e15f2c8e0797d1f93c', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC', 'zUSDC', 24, 6), ('0x447b3b516546f28e8c4f6825a6287b09161659e7c500c599c29c28a8492844b8', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT', 'zUSDT', 24, 6), ('0x548cf587bd918a0005b3372a1d23e64b18ace3c61962f087a21eac52cf228504', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH', 'zWETH', 24, 6), ('0xa76ebfb432982c864783fdb33cdf5a9edb18ac1b950deb8037f5cf8c984da3d2', '0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T', 'whUSDC', 24, 6), ('0x114ee519ffa1e8697784bd1a350822cb37601a1476289051b8295165597a4538', '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T', 'whWETH', 26, 8), ('0x41b039e5518b1a3786ee798d895e0ddf3141fbbc20009284323fa51ea279c819', '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT', 'CAKE', 26, 8), ('0x761a97787fa8b3ae0cef91ebc2d96e56cc539df5bc88dadabee98ae00363a831', '0x1::aptos_coin::AptosCoin', 'APT', 26, 8), ('0xc3c9cc885a52004e0490f6a8291b3bc5854f0e42d931fb124736bd189c9f0f45', '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt', 'stAPT', 26, 8), ('0xac00e90cdadec06d81e0d5ce7a3e93d63d563e982dea0ca15bad2b067f42d2be', '0x357b0b74bc833e95a115ad22604854d6b0fca151cecd94111770e5d6ffc9dc2b','USDt', 24, 6), ('0xacb7262b33a147b47b950a3a26d7699e1a58bcaa475ef636cb0fea53fa0eb872', '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC','aBTC', 28, 10), ('0xef2ae89796725d0eb363326ecb7df159feb949f6d1f400f76deeeebccbac00f1', '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD','MOD', 26, 8), ('0x127ea5b4c450be695e488da1c3bc013e2e93d8cf00270ef90385189844bc9755', '0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL','THL', 26, 8), ('0xed6bf9fe7e3f42c6831ffac91824a545c4b8bfcb40a59b3f4ccfe203cafb7f42', '0xfaf4e633ae9eb31366c9ca24214231760926576c7b625313b3688b5e900731f6::staking::StakedThalaAPT','sthAPT', 26, 8), ('0x7bd5be03df0fb3841fed337b8e7a353818b917cce6db28b807b691002a122d73', '0xaef6a8c3182e076db72d64324617114cacf9a52f28325edc10b483f7f05da0e7','TruAPT', 26, 8), ('0x754114c656e26498a8c0b20fa24af663fed5e4b24acb471c103daea0f7006b40', '0x5dee1d4b13fae338a1e1780f9ad2709a010e824388efd169171a26e3ea9029bb::stakestone_bitcoin::StakeStoneBitcoin','SBTC', 26, 8), ('0x75c39f18c7bc00ac0f81a0f9ab7e319e23790d7d8c3df968d84a23699584bd9', '0x543c5660aa4d496687e2068c11765f04607c4f4b639a83233a9333604fb8ce59::stakestone_ether::StakeStoneEther','STONE', 26, 8), ('0x778362f04f7904ba0b76913ec7c0c5cc04e469b0b96929c6998b34910690a740', '0xb30a694a344edee467d9f82330bbe7c3b89f440a1ecd2da1f3bca266560fce69','sUSDe', 24, 6), ('0x2c4e0bb55272f9c120ffd5a414c10244005caf9c1b14527cea3df7074c5bf623', '0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b','USDC', 24, 6) ) ) , echelonEventTb as ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_INDEX, EVENT_ADDRESS, EVENT_MODULE, EVENT_RESOURCE, EVENT_DATA:account_addr AS USER_ADDRESS, EVENT_DATA:amount AS amount, EVENT_DATA:market_obj:inner::string as POOL_ADDRESS, EVENT_DATA:total_cash AS total_cash, EVENT_DATA:total_liability AS total_liability, FROM aptos.core.fact_events WHERE EVENT_TYPE IN ( '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::BorrowEvent' ) AND SUCCESS = TRUE UNION All SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_INDEX, EVENT_ADDRESS, EVENT_MODULE, EVENT_RESOURCE, EVENT_DATA:repayer_addr AS USER_ADDRESS, EVENT_DATA:amount AS amount, EVENT_DATA:market_obj:inner as POOL_ADDRESS, EVENT_DATA:total_cash AS total_cash, EVENT_DATA:total_liability AS total_liability, FROM aptos.core.fact_events WHERE EVENT_TYPE IN ( '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::RepayEvent' ) AND SUCCESS = TRUE UNION All SELECT a.BLOCK_TIMESTAMP, a.TX_HASH, a.EVENT_INDEX, a.EVENT_ADDRESS, a.EVENT_MODULE, a.EVENT_RESOURCE, a.EVENT_DATA:account_addr AS USER_ADDRESS, a.EVENT_DATA:amount AS amount, a.EVENT_DATA:market_obj:inner as POOL_ADDRESS, a.EVENT_DATA:total_cash AS total_cash, a.EVENT_DATA:total_liability AS total_liability, FROM aptos.core.fact_events a JOIN aptos.core.fact_events b ON (A.TX_HASH = B.TX_HASH AND A.EVENT_DATA:market_obj:inner = B.EVENT_DATA:market_obj:inner AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP ) WHERE a.EVENT_TYPE = '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::WithdrawEvent' AND a.EVENT_TYPE = '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::AccrueMarketInterestEvent' AND a.SUCCESS = TRUE UNION All SELECT a.BLOCK_TIMESTAMP, a.TX_HASH, a.EVENT_INDEX, a.EVENT_ADDRESS, a.EVENT_MODULE, a.EVENT_RESOURCE, a.EVENT_DATA:account_addr AS USER_ADDRESS, a.EVENT_DATA:amount AS amount, a.EVENT_DATA:market_obj:inner as POOL_ADDRESS, a.EVENT_DATA:total_cash AS total_cash, a.EVENT_DATA:total_liability AS total_liability, FROM aptos.core.fact_events a JOIN aptos.core.fact_events b ON (A.TX_HASH = B.TX_HASH AND A.EVENT_DATA:market_obj:inner = B.EVENT_DATA:market_obj:inner AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP ) WHERE a.EVENT_TYPE = '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::SupplyEvent' AND a.EVENT_TYPE = '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::AccrueMarketInterestEvent' AND a.SUCCESS = TRUE ) , echelon_eventTb AS ( SELECT TX_HASH, BLOCK_TIMESTAMP, POOL_ADDRESS, total_cash as TVL, total_liability as TOTAL_BORROWED, ROW_NUMBER() OVER(PARTITION BY TX_HASH ORDER BY EVENT_INDEX DESC) AS RANK FROM echelonEventTb ) , echelon_amount_tb1 AS ( SELECT BLOCK_TIMESTAMP::DATE AS date, POOL_ADDRESS, LAST_VALUE(TVL) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), POOL_ADDRESS ORDER BY TO_DATE(BLOCK_TIMESTAMP)) AS TVL_AMT, LAST_VALUE(TOTAL_BORROWED) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), POOL_ADDRESS ORDER BY TO_DATE(BLOCK_TIMESTAMP)) AS TOTAL_BORROWED_AMT FROM echelon_eventTb WHERE RANK = 1 ) , echelon_total_amt AS ( SELECT date, POOL_ADDRESS, TVL_AMT, TOTAL_BORROWED_AMT FROM echelon_amount_tb1 GROUP BY all ) , amount_tb AS ( SELECT date, TOKEN_CONTRACT, asset, TOTAL_BORROWED_AMT/POW(10,tvl_pow) as TOTAL_BORROWED_AMOUNT, TVL_AMT / POW(10,tvl_pow) as TVL_AMOUNT, TOTAL_BORROWED_AMOUNT + TVL_AMOUNT AS MARKET_SIZE_AMOUNT FROM echelon_total_amt LEFT JOIN echelon_token_map on POOL_ADDRESS = POOL_CONTRACT ) , date_ls as ( select date_day as date from ethereum.core.dim_dates where date_day>='2024-01-01' and date_day<=current_date() ), address_ls as ( select asset, TOKEN_CONTRACT from token_map ) , DATETB as ( select date, asset, TOKEN_CONTRACT from date_ls cross join address_ls ), aggTb1 AS ( SELECT dt.date, dt.asset, dt.TOKEN_CONTRACT, TOTAL_BORROWED_AMOUNT, TVL_AMOUNT, MARKET_SIZE_AMOUNT, LAG(TOTAL_BORROWED_AMOUNT) IGNORE NULLS OVER (PARTITION BY dt.asset ORDER BY dt.date) AS LAG_TOTAL_BORROWED_AMOUNT, LAG(TVL_AMOUNT) IGNORE NULLS OVER (PARTITION BY dt.asset ORDER BY dt.date) AS LAG_TVL_AMOUNT, LAG(MARKET_SIZE_AMOUNT) IGNORE NULLS OVER (PARTITION BY dt.asset ORDER BY dt.date) AS LAG_MARKET_SIZE_AMOUNT, FROM DATETB dt LEFT JOIN amount_tb amt using(date,asset,TOKEN_CONTRACT) ), aggTb2 AS ( SELECT date, asset, TOKEN_CONTRACT, TOTAL_BORROWED_AMOUNT, TVL_AMOUNT, MARKET_SIZE_AMOUNT, LAG_TOTAL_BORROWED_AMOUNT, LAG_TVL_AMOUNT, LAG_MARKET_SIZE_AMOUNT, LEAD(LAG_TOTAL_BORROWED_AMOUNT) IGNORE NULLS OVER (PARTITION BY asset ORDER BY date) AS LEAD_TOTAL_BORROWED_AMOUNT, LEAD(LAG_TVL_AMOUNT) IGNORE NULLS OVER (PARTITION BY asset ORDER BY date) AS LEAD_TVL_AMOUNT, LEAD(LAG_MARKET_SIZE_AMOUNT) IGNORE NULLS OVER (PARTITION BY asset ORDER BY date) AS LEAD_MARKET_SIZE_AMOUNT, FROM aggTb1 ), OVER_TIME AS ( SELECT date, asset, TOKEN_CONTRACT, TOTAL_BORROWED_AMOUNT, TVL_AMOUNT, MARKET_SIZE_AMOUNT, LAG_TVL_AMOUNT, LAG_TOTAL_BORROWED_AMOUNT, LAG_MARKET_SIZE_AMOUNT, LEAD_TOTAL_BORROWED_AMOUNT, LEAD_TVL_AMOUNT, LEAD_MARKET_SIZE_AMOUNT, FROM aggTb2 WHERE date < CURRENT_DATE ), CURRENT_TOTAL_BORROWED_AMOUNT AS ( SELECT TOKEN_CONTRACT, asset, TOTAL_BORROWED_AMOUNT AS CURRENT_LEAD_TOTAL_BORROWED_AMOUNT, FROM aggTb2 WHERE TOTAL_BORROWED_AMOUNT IS NOT NULL QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY date DESC) = 1 ), CURRENT_TVL AS ( SELECT TOKEN_CONTRACT, asset, TVL_AMOUNT AS CURRENT_LEAD_TVL_AMOUNT, FROM aggTb2 WHERE TVL_AMOUNT IS NOT NULL QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY date DESC) = 1 ), CURRENT_MARKET_SIZE AS ( SELECT TOKEN_CONTRACT, asset, MARKET_SIZE_AMOUNT AS CURRENT_LEAD_MARKET_SIZE_AMOUNT, FROM aggTb2 WHERE MARKET_SIZE_AMOUNT IS NOT NULL QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY date DESC) = 1 ), CURRENT_AMT AS( SELECT CURRENT_DATE AS date, TOKEN_CONTRACT, asset, CURRENT_LEAD_TOTAL_BORROWED_AMOUNT, CURRENT_LEAD_TVL_AMOUNT, CURRENT_LEAD_MARKET_SIZE_AMOUNT FROM CURRENT_TOTAL_BORROWED_AMOUNT FULL OUTER JOIN CURRENT_TVL USING(asset) FULL OUTER JOIN CURRENT_MARKET_SIZE USING(asset) ), TOTAL_AMOUNT_FINAL AS ( SELECT date, TOKEN_CONTRACT, asset, LEAD_TOTAL_BORROWED_AMOUNT, LEAD_TVL_AMOUNT, LEAD_MARKET_SIZE_AMOUNT, FROM OVER_TIME UNION ALL SELECT * FROM CURRENT_AMT ) , echelon_priceTb AS ( SELECT HOUR::DATE AS date, CASE WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' THEN 'zWETH' WHEN TOKEN_ADDRESS = '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT' THEN 'CAKE' WHEN TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin' THEN 'APT' WHEN TOKEN_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' THEN 'stAPT' WHEN TOKEN_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' THEN 'MOD' WHEN TOKEN_ADDRESS = '0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL' THEN 'THL' WHEN TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' THEN 'aBTC' ELSE TOKEN_ADDRESS END AS asset, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN (SELECT TOKEN_CONTRACT FROM echelon_token_map WHERE asset NOT IN ('sUSDe', 'USDC', 'whUSDC', 'zUSDC', 'zUSDT', 'sthAPT', 'TruAPT', 'STONE','whWETH')) AND date < CURRENT_DATE AND date > '2024-03-01' GROUP BY date, asset UNION ALL (SELECT HOUR::DATE AS date, CASE WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' THEN 'zWETH' WHEN TOKEN_ADDRESS = '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT' THEN 'CAKE' WHEN TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin' THEN 'APT' WHEN TOKEN_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' THEN 'stAPT' WHEN TOKEN_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' THEN 'MOD' WHEN TOKEN_ADDRESS = '0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL' THEN 'THL' WHEN TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' THEN 'aBTC' ELSE TOKEN_ADDRESS END AS asset, PRICE AS PRICE_USD, FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN (SELECT TOKEN_CONTRACT FROM echelon_token_map WHERE asset NOT IN ('whUSDC', 'zUSDC', 'zUSDT', 'sthAPT', 'TruAPT', 'STONE','whWETH')) QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY HOUR DESC) = 1 ) UNION ALL (SELECT HOUR::DATE AS date, 'STONE' AS asset, AVG(PRICE) AS PRICE_USD FROM crosschain.price.ez_prices_hourly WHERE date < CURRENT_DATE AND date > '2024-10-20' AND BLOCKCHAIN = 'bsc' AND TOKEN_ADDRESS = '0x80137510979822322193fc997d400d5a6c747bf7' GROUP BY date, asset) UNION ALL ( SELECT HOUR::DATE AS date, 'STONE' AS asset, PRICE AS PRICE_USD FROM crosschain.price.ez_prices_hourly WHERE BLOCKCHAIN = 'bsc' AND TOKEN_ADDRESS = '0x80137510979822322193fc997d400d5a6c747bf7' QUALIFY ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY HOUR DESC) = 1 ) UNION ALL (SELECT date AS date, 'zUSDC' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'zUSDC' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'USDC' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'USDC' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'sUSDe' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'sUSDe' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'zUSDT' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'zUSDT' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'USDt' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'USDt' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'whUSDC' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'whUSDC' AND date > '2024-03-01' ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'sthAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/staked-thala-apt/market_chart?vs_currency=usd&days=360&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) WHERE date < CURRENT_DATE ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'sthAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/staked-thala-apt/market_chart?vs_currency=usd&days=1&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ORDER BY TO_TIMESTAMP(VALUE[0]::STRING) DESC LIMIT 1 ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'TruAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/trufin-staked-apt/market_chart?vs_currency=usd&days=40&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) WHERE date < CURRENT_DATE ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'TruAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/trufin-staked-apt/market_chart?vs_currency=usd&days=1&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ORDER BY TO_TIMESTAMP(VALUE[0]::STRING) DESC LIMIT 1 ) UNION ALL (SELECT HOUR::DATE AS date, 'whWETH' AS asset, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' AND date < CURRENT_DATE AND date > '2024-03-01' GROUP BY date, asset ) UNION ALL (SELECT HOUR::DATE AS date, 'whWETH' AS asset , PRICE AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY HOUR DESC) = 1 ) UNION ALL (SELECT HOUR::DATE AS date, 'SBTC' AS asset, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' AND date < CURRENT_DATE AND date > '2024-03-01' GROUP BY date, asset ) UNION ALL (SELECT HOUR::DATE AS date, 'SBTC' AS asset , PRICE AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY HOUR DESC) = 1 -- AND date = CURRENT_DATE -- group by all ) ) , echelon_priceTb_borrow AS ( SELECT HOUR::DATE AS date, CASE WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' THEN 'zWETH' WHEN TOKEN_ADDRESS = '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT' THEN 'CAKE' WHEN TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin' THEN 'APT' WHEN TOKEN_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' THEN 'stAPT' WHEN TOKEN_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' THEN 'MOD' WHEN TOKEN_ADDRESS = '0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL' THEN 'THL' WHEN TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' THEN 'aBTC' ELSE TOKEN_ADDRESS END AS asset, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN (SELECT TOKEN_CONTRACT FROM echelon_token_map WHERE asset NOT IN ('sUSDe', 'USDC', 'whUSDC', 'zUSDC', 'zUSDT', 'sthAPT', 'TruAPT', 'STONE','whWETH')) AND date < CURRENT_DATE AND date > '2024-03-01' GROUP BY date, asset UNION ALL (SELECT HOUR::DATE AS date, CASE WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' THEN 'zWETH' WHEN TOKEN_ADDRESS = '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT' THEN 'CAKE' WHEN TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin' THEN 'APT' WHEN TOKEN_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' THEN 'stAPT' WHEN TOKEN_ADDRESS = '0x6f986d146e4a90b828d8c12c14b6f4e003fdff11a8eecceceb63744363eaac01::mod_coin::MOD' THEN 'MOD' WHEN TOKEN_ADDRESS = '0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL' THEN 'THL' WHEN TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' THEN 'aBTC' ELSE TOKEN_ADDRESS END AS asset, PRICE AS PRICE_USD, FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN (SELECT TOKEN_CONTRACT FROM echelon_token_map WHERE asset NOT IN ('whUSDC', 'zUSDC', 'zUSDT', 'sthAPT', 'TruAPT', 'STONE','whWETH')) QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY HOUR DESC) = 1) UNION ALL (SELECT HOUR::DATE AS date, 'STONE' AS asset, AVG(PRICE) AS PRICE_USD FROM crosschain.price.ez_prices_hourly WHERE date < CURRENT_DATE AND date > '2024-10-20' AND BLOCKCHAIN = 'bsc' AND TOKEN_ADDRESS = '0x80137510979822322193fc997d400d5a6c747bf7' GROUP BY date, asset) UNION ALL ( SELECT HOUR::DATE AS date, 'STONE' AS asset, PRICE AS PRICE_USD FROM crosschain.price.ez_prices_hourly WHERE BLOCKCHAIN = 'bsc' AND TOKEN_ADDRESS = '0x80137510979822322193fc997d400d5a6c747bf7' QUALIFY ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY HOUR DESC) = 1 ) UNION ALL (SELECT date AS date, 'zUSDC' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'zUSDC' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'USDC' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'USDC' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'sUSDe' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'sUSDe' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'zUSDT' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'zUSDT' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'USDt' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'USDt' AND date > '2024-03-01' ) UNION ALL (SELECT date AS date, 'whUSDC' AS asset, 1.00::int AS PRICE_USD FROM DATETB WHERE asset = 'whUSDC' AND date > '2024-03-01' ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'sthAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/staked-thala-apt/market_chart?vs_currency=usd&days=360&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) WHERE date < CURRENT_DATE ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'sthAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/staked-thala-apt/market_chart?vs_currency=usd&days=1&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ORDER BY TO_TIMESTAMP(VALUE[0]::STRING) DESC LIMIT 1 ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'TruAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/trufin-staked-apt/market_chart?vs_currency=usd&days=40&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) WHERE date < CURRENT_DATE ) UNION ALL (SELECT (VALUE[0]::STRING)::DATE AS date, 'TruAPT' AS asset, VALUE[1] AS PRICE_USD FROM ( SELECT LIVEQUERY.LIVE.UDF_API('https://api.coingecko.com/api/v3/coins/trufin-staked-apt/market_chart?vs_currency=usd&days=1&interval=daily&precision=3') AS RESP), LATERAL FLATTEN (INPUT => RESP:data:prices) ORDER BY TO_TIMESTAMP(VALUE[0]::STRING) DESC LIMIT 1 ) UNION ALL (SELECT HOUR::DATE AS date, 'whWETH' AS asset, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' AND date < CURRENT_DATE AND date > '2024-03-01' GROUP BY date, asset ) UNION ALL (SELECT HOUR::DATE AS date, 'whWETH' AS asset , PRICE AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY HOUR DESC) = 1 ) UNION ALL (SELECT HOUR::DATE AS date, 'SBTC' AS asset, AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' AND date < CURRENT_DATE AND date > '2024-03-01' GROUP BY date, asset ) UNION ALL (SELECT HOUR::DATE AS date, 'SBTC' AS asset , PRICE AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0x4e1854f6d332c9525e258fb6e66f84b6af8aba687bbcb832a24768c4e175feec::abtc::ABTC' QUALIFY ROW_NUMBER() OVER (PARTITION BY asset ORDER BY HOUR DESC) = 1 -- AND date = CURRENT_DATE -- group by all ) ) , ecchelon_Tb as ( SELECT date as "Date", asset, round((LEAD_TVL_AMOUNT),2) as "Total Value Locked Amount", round((LEAD_MARKET_SIZE_AMOUNT),2) AS "Total Supply Amount", round((LEAD_MARKET_SIZE_AMOUNT * echelon_priceTb.PRICE_USD),2) AS "Total Supply Amount[$]", round((LEAD_TOTAL_BORROWED_AMOUNT * echelon_priceTb_borrow.PRICE_USD),2) AS TOTAL_BORROWED, round((LEAD_TVL_AMOUNT * echelon_priceTb_borrow.PRICE_USD),2) AS TVL, round((LEAD_MARKET_SIZE_AMOUNT * echelon_priceTb.PRICE_USD),2) AS MARKET_SIZE, TOTAL_BORROWED - LAG(TOTAL_BORROWED, 1, 0) OVER (PARTITION BY asset ORDER BY date) AS daily_borrowed_changes, MARKET_SIZE - LAG(MARKET_SIZE, 1, 0) OVER (PARTITION BY asset ORDER BY date) AS daily_market_size_changes, TVL - LAG(TVL, 1, 0) OVER (PARTITION BY asset ORDER BY date) AS daily_TVL_changes, FROM TOTAL_AMOUNT_FINAL LEFT JOIN echelon_priceTb USING(date, asset) LEFT JOIN echelon_priceTb_borrow USING(date, asset) where asset ilike any ('APT', 'stAPT', 'sthAPT','TruAPT') ORDER BY 2 DESC ) , echelon_final AS ( SELECT "Date", asset, "Total Supply Amount", "Total Supply Amount[$]" FROM ecchelon_Tb where "Date" = current_date() ) -- , -- ecchelon_Tb as ( -- SELECT -- "Date", -- SYMBOL as asset, -- "Total Value Locked Amount", -- "Total Supply Amount", -- "Total Supply Amount"*PRICE_USD as "Total Supply Amount[$]", -- "Total Borrowed Amount" -- FROM echelonFinalTb t -- LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) -- QUALIFY ROW_NUMBER() OVER (PARTITION BY SYMBOL ORDER BY "Date" DESC) = 1 -- ) ---------------------------- -------Echo Protocol-------- ---------------------------- , echo_protocol as ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_RESOURCE, EVENT_DATA:user as SENDER, CASE when EVENT_DATA:reserve ilike '0xc2315bdb8f7789e1817ac423e80748075fa68b7e33949bde8c2769a46fdb212' then 'USDC' when EVENT_DATA:reserve ilike '0x66cca033c185547a88494a9c71baa0dd2185212cfe5e79ec2ec0c4040b5c35c0' then 'USDT' when EVENT_DATA:reserve ilike '0xf8d3506b42a6879680974fc745526c9cbd48b0b0816079aa59f65fc865bdfbf6' then 'APT' when EVENT_DATA:reserve ilike '0x2760ca0d7ed74fec37f1c539fd752b5c684fa011c539a79def7a6c67717ebace' then 'aBTC' end as asset, EVENT_DATA:amount/pow(10, 8) as amount, FROM aptos.core.fact_events WHERE EVENT_TYPE ilike ANY ( '0xeab7ea4d635b6b6add79d5045c4a45d8148d88287b1cfa1c3b6a4b56f46839ed::supply_logic::supply' ,'0xeab7ea4d635b6b6add79d5045c4a45d8148d88287b1cfa1c3b6a4b56f46839ed::supply_logic::withdraw' ) AND EVENT_DATA:reserve ilike ('0xf8d3506b42a6879680974fc745526c9cbd48b0b0816079aa59f65fc865bdfbf6') AND SUCCESS = TRUE AND EVENT_MODULE ilike ('supply_logic') AND EVENT_RESOURCE ilike ANY ('Supply','Withdraw') ) , echoBorrowTb as ( SELECT asset, sum(amount) as amount_supplied, from echo_protocol WHERE event_resource in ('Supply') group by 1 ) , echoRepayTb as ( SELECT asset, sum(amount) as amount_withdrawn, FROM echo_protocol WHERE event_resource in ('Withdraw') group by 1 ) , echo_final1 AS ( SELECT BR.asset, amount_supplied - amount_withdrawn AS "Total supplied", (amount_supplied - amount_withdrawn)*PRICE_USD AS "Total supplied[$]" FROM echoBorrowTb BR LEFT JOIN echoRepayTb USING (asset) LEFT JOIN current_date_price p WHERE p.TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin' ), echo_lst AS ( SELECT asset, IFF("Total supplied"<0, 0, "Total supplied") AS "Total Supplied", IFF("Total supplied[$]"<0, 0, "Total supplied[$]") AS "Total Supplied[$]" FROM echo_final1 ) ---------------------------- -------Superposition-------- ---------------------------- , superpositionEventTb AS ( SELECT block_timestamp, tx_hash, event_resource, event_data:broker_type::string as TOKEN_ADDRESS, CASE WHEN TOKEN_ADDRESS = '0xa2eda21a58856fda86451436513b867c97eecb4ba099da5775520e0f7492e852::coin::T' THEN 'whUSDT' WHEN TOKEN_ADDRESS = '0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T' THEN 'whUSDC' WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC' THEN 'zUSDC' WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT' THEN 'zUSDT' ELSE split(TOKEN_ADDRESS, '::')[2] END AS asset, event_data:actor AS user, event_data:amount/1e6 AS amount FROM aptos.core.fact_events WHERE payload_function IN ( '0xccd1a84ccea93531d7f165b90134aa0415feb30e8757ab1632dac68c0055f5c2::entry_public::lend_v2' ,'0xccd1a84ccea93531d7f165b90134aa0415feb30e8757ab1632dac68c0055f5c2::entry_public::redeem_v2' ) and event_resource in ('LendEvent','RedeemEvent') AND event_data:broker_type IN ( '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' ,'0x1::aptos_coin::AptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' ) ), superpoBorrowTb as ( SELECT asset, sum(amount) as amount_supplied, sum(amount*price_usd) as amount_supplied_usd, from superpositionEventTb t LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) WHERE event_resource in ('LendEvent') group by 1 ), superpoRepayTb as ( SELECT asset, sum(amount) as amount_withdrawn, sum(amount*price_usd) as amount_withdrawn_usd, FROM superpositionEventTb t LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) WHERE event_resource in ('RedeemEvent') group by 1 ) , superpo_final AS ( SELECT BR.asset, amount_supplied - amount_withdrawn AS "Total supplied", amount_supplied_usd - amount_withdrawn_usd AS "Total supplied[$]" FROM superpoBorrowTb BR LEFT JOIN superpoRepayTb USING (asset) ) , superpositionFinal AS ( SELECT asset, IFF("Total supplied"<0, 0, "Total supplied") AS "Total Supplied", IFF("Total supplied[$]"<0, 0, "Total supplied[$]") AS "Total Supplied[$]" FROM superpo_final ) ---------------------------- -------Joule Finance-------- ---------------------------- , jouleBorrowEvent as ( select block_timestamp, tx_hash, event_resource, event_data:coin_name::string as TOKEN_ADDRESS, case when event_data:coin_name::string ilike '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' then 'tAPT' when event_data:coin_name::string ilike '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' then 'amAPT' when event_data:coin_name::string ilike '0x1::aptos_coin::AptosCoin' then 'APT' when event_data:coin_name::string ilike '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' then 'stAPT' end as asset, event_data:signer_address as user, event_data:amount/1e8 as amt from aptos.core.fact_events where payload_function ilike ANY ( '0x2fe576faa841347a9b1b32c869685deb75a15e3f62dfe37cbd6d52cc403a16f6::pool::lend' ,'0x2fe576faa841347a9b1b32c869685deb75a15e3f62dfe37cbd6d52cc403a16f6::pool::withdraw' ) AND EVENT_DATA:coin_name IN ( '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' ,'0x1::aptos_coin::AptosCoin' ,'0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' ) and event_resource ilike ANY ('LendEvent','WithdrawEvent') ) , joule_final_borrow as ( SELECT ASSET, sum(amt) as amount_supplied, sum(amt*price_usd) as amount_supplied_usd from jouleBorrowEvent t LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) WHERE event_resource ilike 'LendEvent' group by 1 ), joule_final_repay as ( SELECT ASSET, sum(amt) as amount_withdrawn, sum(amt*price_usd) as amount_withdrawn_usd, from jouleBorrowEvent t LEFT JOIN current_date_price p ON (t.TOKEN_ADDRESS ilike p.TOKEN_ADDRESS) WHERE event_resource ilike 'WithdrawEvent' group by 1 ) , joule_lst as ( SELECT BR.asset, amount_supplied - amount_withdrawn AS "Total supplied", amount_supplied_usd - amount_withdrawn_usd AS "Total supplied[$]" FROM joule_final_borrow BR LEFT JOIN joule_final_repay USING (asset) ), jouleFinal AS ( SELECT asset, IFF("Total supplied"<0, 0, "Total supplied") AS "Total Supplied", IFF("Total supplied[$]"<0, 0, "Total supplied[$]") AS "Total Supplied[$]" FROM joule_lst ) ---------------------------- -------Amnis Finance-------- ---------------------------- , amnis_finance AS ( with priceTb11 as ( select hour, avg(price) as price from aptos.price.ez_prices_hourly where token_address = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' group by 1 qualify row_number() over (ORDER by hour DESC) = 1 ) , main as ( select block_timestamp, coalesce(case when EVENT_RESOURCE = 'MintEvent' then EVENT_DATA:amapt :: float / pow(10, 8) end, 0) as stake_amt, coalesce(case when EVENT_RESOURCE = 'WithdrawEvent' then EVENT_DATA:amount :: float / pow(10, 8) end, 0) as unstake_amt, coalesce(case when EVENT_RESOURCE = 'UpdateRewardEvent' then EVENT_DATA:extra_amapt :: float / pow(10, 8) end, 0) as reward_amt, FROM aptos.core.fact_events WHERE EVENT_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a' AND EVENT_RESOURCE IN ('MintEvent','WithdrawEvent','UpdateRewardEvent') and SUCCESS = 'TRUE' ) , total_amount as ( SELECT sum(stake_amt) as "Stake Amount", sum(reward_amt) as "Reward Amount", sum(unstake_amt)*-1 as "UnStake Amount", "Stake Amount" + "UnStake Amount"+"Reward Amount" as Net_Flow, FROM main ) SELECT "Stake Amount", "Reward Amount", "UnStake Amount", Net_Flow, "Stake Amount"*price as "Stake Amount USD", "Reward Amount"*price as "Reward Amount USD", "UnStake Amount"*price as "UnStake Amount USD", Net_Flow*price as Net_Flow_usd FROM total_amount LEFT JOIN priceTb11 --on trunc(block_timestamp, 'hour') = hour ) , final as ( SELECT 'Amnis Finance' as protocol, 'APT' as ASSET, round(Net_Flow, 2) as "Total Supplied", round(Net_Flow_usd, 2) as "Total Supplied[$]" FROM amnis_finance UNION ALL SELECT 'Joule Finance' as protocol, asset::string as "Asset", round("Total Supplied", 2) as "Total Supplied", round("Total Supplied[$]", 2) as "Total Supplied[$]" FROM jouleFinal -- UNION All -- SELECT -- 'Superposition' as protocol, -- asset::string as "Asset", -- round("Total Supplied", 2) as "Total Supplied", -- round("Total Supplied[$]", 2) as "Total Supplied[$]" -- FROM superpositionFinal UNION All SELECT 'Echo Protocol' as protocol, asset as "Asset", round("Total Supplied", 2) as "Total Supplied", round("Total Supplied[$]", 2) as "Total Supplied[$]" FROM echo_lst UNION All SELECT 'Echelon' as protocol, asset as "Asset", round("Total Supply Amount", 2) as "Total Supplied", round("Total Supply Amount[$]", 2) as "Total Supplied[$]" FROM echelon_final UNION All SELECT 'Meso' as protocol, "Asset", round("Total Supplied", 2) as "Total Supplied", round("Total Supplied[$]", 2) as "Total Supplied[$]" FROM meso_lst UNION All SELECT 'Aries' as protocol, "Asset", round(MARKET_SIZE, 2) as "Total Supplied", round(MARKET_SIZE_USD, 2) as "Total Supplied[$]" FROM aries_lst UNION All SELECT 'Aptin' as protocol, "Asset", round("Market Size", 2) as "Total Supplied", round("Market Size[$]", 2) as "Total Supplied[$]" FROM aptin_Tb UNION All SELECT 'Thala' as protocol, "Asset", round("Total Supply", 2) as "Total Supplied", round("Total Supply[$]", 2) as "Total Supplied[$]" FROM thala_Tb ORDER BY 3 DESC ) SELECT protocol, -- "Asset", round(sum("Total Supplied"),2) as "Total Supplied", round(sum("Total Supplied[$]"),2) as "Total Supplied[$]" FROM final group by 1 ORDER by 2 DESC --*/