WITH POOLS AS ( SELECT COLUMN1 AS "Asset", COLUMN2 AS TOKEN_ADDRESS, COLUMN3 AS DECIMALS FROM (VALUES ('stAPT', '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::stakedapt', 8), ('CAKE', '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::cakeoft', 8), ('tAPT', '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::stakedaptoscoin', 8), ('WETH(Wormhole)', '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::t', 8), ('APT', '0x1::aptos_coin::aptoscoin', 8), ('zUSDC', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::usdc', 6), ('zUSDT', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::usdt', 6), ('zWETH', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::weth', 6), ('USDC(Wormhole)', '0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::t', 6), ('USDT(Wormhole)', '0xa2eda21a58856fda86451436513b867c97eecb4ba099da5775520e0f7492e852::coin::t', 6), ('USDT', '0x6684d15d691e3e5edbf3474eba252e0dd29b7dfbefa6cc7c2e00811fd454c75a::tether::usdt', 6) ) ), ALL_TX AS ( SELECT TX_HASH, BLOCK_TIMESTAMP, LOWER(EVENT_DATA:coin_name) AS TOKEN_ADDRESS, EVENT_DATA:amount AS RAW_AMOUNT, EVENT_DATA:oper_type AS TYPE, EVENT_DATA:supply_total_value AS TOTAL_SUPPLY_RAW, EVENT_DATA:borrow_total_value AS TOTAL_BORROWED_RAW, TOTAL_SUPPLY_RAW - TOTAL_BORROWED_RAW AS TVL_RAW FROM aptos.core.fact_events WHERE EVENT_TYPE = '0x3c1d4a86594d681ff7e5d5a233965daeabdc6a15fe5672ceeda5260038857183::pool::OperEvent' -- AND EVENT_DATA:coin_name != '0x6684d15d691e3e5edbf3474eba252e0dd29b7dfbefa6cc7c2e00811fd454c75a::tether::USDt' AND SUCCESS = TRUE ), FILTER_STATUS_DAILY_AMOUNT AS ( SELECT BLOCK_TIMESTAMP::DATE AS "Date", "Asset", A.TOKEN_ADDRESS, LAST_VALUE(TOTAL_BORROWED_RAW) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), A.TOKEN_ADDRESS ORDER BY TO_DATE(BLOCK_TIMESTAMP)) / POW(10, DECIMALS) AS TOTAL_BORROWED_AMOUNT_DAILY, LAST_VALUE(TVL_RAW) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), A.TOKEN_ADDRESS ORDER BY TO_DATE(BLOCK_TIMESTAMP)) / POW (10, DECIMALS) AS TVL_AMOUNT_DAILY, LAST_VALUE(TOTAL_SUPPLY_RAW) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), A.TOKEN_ADDRESS ORDER BY TO_DATE(BLOCK_TIMESTAMP)) / POW (10, DECIMALS) AS TOTAL_SUPPLY_AMOUNT_DAILY FROM ALL_TX A LEFT JOIN POOLS USING (TOKEN_ADDRESS) ), STATUS_DAILY_AMOUNT AS ( SELECT "Date", "Asset", TOKEN_ADDRESS, TOTAL_BORROWED_AMOUNT_DAILY, TVL_AMOUNT_DAILY, TOTAL_SUPPLY_AMOUNT_DAILY FROM FILTER_STATUS_DAILY_AMOUNT GROUP BY 1,2,3,4,5,6 ), PRICES AS ( SELECT HOUR::DATE AS "Date", CASE WHEN TOKEN_ADDRESS = '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' THEN 'tAPT' WHEN TOKEN_ADDRESS = '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T' THEN 'WETH(Wormhole)' 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' END AS "Asset", AVG(PRICE) AS PRICE_USD FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN ('0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin', '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T', '0x1::aptos_coin::AptosCoin', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH', '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt') AND "Date" < CURRENT_DATE GROUP BY "Date", "Asset" UNION ALL (SELECT HOUR::DATE AS "Date", CASE WHEN TOKEN_ADDRESS = '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' THEN 'tAPT' WHEN TOKEN_ADDRESS = '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T' THEN 'WETH(Wormhole)' 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' END AS "Asset", PRICE AS PRICE_USD, FROM aptos.price.ez_prices_hourly WHERE TOKEN_ADDRESS IN ('0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin', '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T', '0x1::aptos_coin::AptosCoin', '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH', '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt') QUALIFY ROW_NUMBER() OVER (PARTITION BY "Asset" ORDER BY HOUR DESC) = 1) UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'USDC(Wormhole)' AS "Asset", 1 AS PRICE_USD FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'zUSDC' AS "Asset", 1 AS PRICE_USD FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'zUSDT' AS "Asset", 1 AS PRICE_USD FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) UNION ALL SELECT '2024-11-04'::DATE+x AS "Date", 'USDT' AS "Asset", 1 AS PRICE_USD FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 260)) ) UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'USDT(Wormhole)' AS "Asset", 1 AS PRICE_USD FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) UNION ALL SELECT HOUR::DATE AS "Date", 'CAKE' AS "Asset", ROUND(AVG(PRICE),3) AS PRICE_USD FROM bsc.price.ez_prices_hourly WHERE TOKEN_ADDRESS = '0x0e09fabb73bd3ade0a17ecc321fd13a19e81ce82' AND HOUR::DATE > '2023-10-24' GROUP BY "Date" ), STATUS_DAILY_USD AS ( SELECT A."Date", A."Asset", PRICE_USD, TOTAL_BORROWED_AMOUNT_DAILY*PRICE_USD AS TB, TVL_AMOUNT_DAILY*PRICE_USD AS TVL, TOTAL_SUPPLY_AMOUNT_DAILY*PRICE_USD AS MS FROM STATUS_DAILY_AMOUNT A LEFT JOIN PRICES USING("Date", "Asset") ), GENERATE_DATES AS ( SELECT '2023-12-22'::DATE+x AS "Date", 'stAPT' AS "Asset" -- '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'tAPT' AS "Asset" -- '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'zUSDC' AS "Asset" -- '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-12-14'::DATE+x AS "Date", 'CAKE' AS "Asset" -- '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'zWETH' AS "Asset" -- '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'APT' AS "Asset" -- '0x1::aptos_coin::AptosCoin' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'USDC(Wormhole)' AS "Asset" -- '0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-10-24'::DATE+x AS "Date", 'WETH(Wormhole)' AS "Asset" -- '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2023-12-20'::DATE+x AS "Date", 'USDT(Wormhole)' AS "Asset" -- '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WBTC' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE UNION ALL SELECT '2024-11-04'::DATE+x AS "Date", 'USDT' AS "Asset", FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 260)) ) UNION ALL SELECT '2023-12-20'::DATE+x AS "Date", 'zUSDT' AS "Asset" -- '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT' AS TOKEN_CONTRACT FROM ( SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 460)) ) WHERE "Date" <= CURRENT_DATE ), STATUS_DAILY_USD_FIX_DATE_1 AS ( SELECT GD."Date", GD."Asset", TB, LAG(TB) IGNORE NULLS OVER (PARTITION BY GD."Asset" ORDER BY GD."Date") AS LAG_TB, TVL, LAG(TVL) IGNORE NULLS OVER (PARTITION BY GD."Asset" ORDER BY GD."Date") AS LAG_TVL, MS, LAG(MS) IGNORE NULLS OVER (PARTITION BY GD."Asset" ORDER BY GD."Date") AS LAG_MS, FROM GENERATE_DATES GD LEFT JOIN STATUS_DAILY_USD USING ("Date", "Asset") ), STATUS_DAILY_USD_FIX_DATE_2 AS ( SELECT "Date", "Asset", TB, LAG_TB, LEAD(LAG_TB) IGNORE NULLS OVER (PARTITION BY "Asset" ORDER BY "Date") AS LEAD_TB, --CM TVL, LAG_TVL, LEAD(LAG_TVL) IGNORE NULLS OVER (PARTITION BY "Asset" ORDER BY "Date") AS LEAD_TVL, MS, LAG_MS, LEAD(LAG_MS) IGNORE NULLS OVER (PARTITION BY "Asset" ORDER BY "Date") AS LEAD_MS, FROM STATUS_DAILY_USD_FIX_DATE_1 ), STATUS_DAILY_USD_FIX_DATE_3 AS ( SELECT "Date", "Asset", TB, LAG_TB, LEAD_TB, TVL, LAG_TVL, LEAD_TVL, MS, LAG_MS, LEAD_MS, FROM STATUS_DAILY_USD_FIX_DATE_2 WHERE "Date" < CURRENT_DATE ), STATUS_DAILY_USD_FIX_DATE_4_TB AS ( SELECT "Asset", TB AS LEAD_TB, FROM STATUS_DAILY_USD_FIX_DATE_2 WHERE TB IS NOT NULL QUALIFY ROW_NUMBER() OVER (PARTITION BY "Asset" ORDER BY "Date" DESC) = 1 ), STATUS_DAILY_USD_FIX_DATE_4_TVL AS ( SELECT "Asset", TVL AS LEAD_TVL, FROM STATUS_DAILY_USD_FIX_DATE_2 WHERE TVL IS NOT NULL QUALIFY ROW_NUMBER() OVER (PARTITION BY "Asset" ORDER BY "Date" DESC) = 1 ), STATUS_DAILY_USD_FIX_DATE_4_MS AS ( SELECT "Asset", MS AS LEAD_MS, FROM STATUS_DAILY_USD_FIX_DATE_2 WHERE MS IS NOT NULL QUALIFY ROW_NUMBER() OVER (PARTITION BY "Asset" ORDER BY "Date" DESC) = 1 ), STATUS_DAILY_USD_FIX_DATE_4 AS( SELECT CURRENT_DATE AS "Date", "Asset", LEAD_TB, LEAD_TVL, LEAD_MS FROM STATUS_DAILY_USD_FIX_DATE_4_MS FULL OUTER JOIN STATUS_DAILY_USD_FIX_DATE_4_TVL USING("Asset") FULL OUTER JOIN STATUS_DAILY_USD_FIX_DATE_4_TB USING("Asset") ), STATUS_DAILY_USD_FIX_DATE_5 AS ( SELECT "Date", "Asset", LEAD_TB, LEAD_TVL, LEAD_MS FROM STATUS_DAILY_USD_FIX_DATE_3 UNION ALL SELECT * FROM STATUS_DAILY_USD_FIX_DATE_4 ), STATUS_OVER_TIME AS ( SELECT "Date", SUM(LEAD_TB) AS "Total Borrowed", SUM(LEAD_TVL) AS "Total Value Locked", SUM(LEAD_MS) AS "Market Size", FROM STATUS_DAILY_USD_FIX_DATE_5 GROUP BY "Date" ORDER BY "Date" DESC ), CURRENT_STATUS AS ( SELECT 1 AS ID, "Date", "Total Borrowed", "Total Value Locked", "Market Size" FROM STATUS_OVER_TIME QUALIFY ROW_NUMBER() OVER (ORDER BY "Date" DESC) = 1 ), LAST_1_DAY_STATUS AS ( SELECT 1 AS ID, "Total Borrowed", "Total Value Locked", "Market Size" FROM STATUS_OVER_TIME QUALIFY ROW_NUMBER() OVER (ORDER BY "Date" DESC) = 2 ), LAST_7_DAY_STATUS AS ( SELECT 1 AS ID, "Total Borrowed", "Total Value Locked", "Market Size" FROM STATUS_OVER_TIME QUALIFY ROW_NUMBER() OVER (ORDER BY "Date" DESC) = 8 ), LAST_30_DAY_STATUS AS ( SELECT 1 AS ID, "Total Borrowed", "Total Value Locked", "Market Size" FROM STATUS_OVER_TIME QUALIFY ROW_NUMBER() OVER (ORDER BY "Date" DESC) = 31 ) SELECT ROUND(((C."Total Borrowed" - O."Total Borrowed")/O."Total Borrowed")*100,2) AS "Total Borrowed 1d Change", ROUND(((C."Total Borrowed" - S."Total Borrowed")/S."Total Borrowed")*100,2) AS "Total Borrowed 7d Change", ROUND(((C."Total Borrowed" - T."Total Borrowed")/T."Total Borrowed")*100,2) AS "Total Borrowed 1m Change", ROUND(((C."Total Value Locked" - O."Total Value Locked")/O."Total Value Locked")*100,2) AS "Total Value Locked 1d Change", ROUND(((C."Total Value Locked" - S."Total Value Locked")/S."Total Value Locked")*100,2) AS "Total Value Locked 7d Change", ROUND(((C."Total Value Locked" - T."Total Value Locked")/T."Total Value Locked")*100,2) AS "Total Value Locked 1m Change", ROUND(((C."Market Size" - O."Market Size")/O."Market Size")*100,2) AS "Market Size 1d Change", ROUND(((C."Market Size" - S."Market Size")/S."Market Size")*100,2) AS "Market Size 7d Change", ROUND(((C."Market Size" - T."Market Size")/T."Market Size")*100,2) AS "Market Size 1m Change" FROM CURRENT_STATUS C JOIN LAST_1_DAY_STATUS O USING(ID) JOIN LAST_7_DAY_STATUS S USING(ID) JOIN LAST_30_DAY_STATUS T USING(ID)

Query	Creator	Created
Changes aptos bsc evm binance	Zanyar_98	Nov 20, 2024
Daily Borrows Breakdown by Assets aptos bsc evm binance	Zanyar_98	Nov 14, 2024

Zanyar_98