WITH UST_TERRA AS ( SELECT DATE_TRUNC('hour', block_timestamp) as block_date, 'UST_TERRA' AS symbol, avg(price_usd) as price FROM terra.oracle_prices WHERE symbol = 'UST' AND block_timestamp > {{date}} GROUP BY 1 ORDER BY 1 DESC ), UST_THORCHAIN AS ( SELECT date_trunc('hour', block_timestamp) as block_date, 'UST_THORCHAIN' as symbol, AVG(CASE WHEN pool_name = 'TERRA.UST' THEN asset_Usd ELSE NULL END) as price FROM thorchain.prices WHERE block_timestamp > {{date}} GROUP BY 1 ), ERC_TOKEN as ( SELECT HOUR, SYMBOL, PRICE, LAG(PRICE) ignore nulls over(PARTITION BY SYMBOL ORDER BY HOUR ASC) as PRICE_PREV, CASE WHEN PRICE_PREV > 1 AND PRICE_PREV - PRICE > 0 THEN 1 ELSE NULL END AS POS_PEG_DECREASE, CASE WHEN PRICE_PREV > 1 AND PRICE_PREV - PRICE < 0 THEN 1 ELSE NULL END AS POS_PEG_INCREASE, CASE WHEN PRICE_PREV < 1 AND PRICE_PREV - PRICE < 0 THEN 1 ELSE NULL END AS NEG_PEG_INCREASE, CASE WHEN PRICE_PREV < 1 AND PRICE_PREV - PRICE > 0 THEN 1 ELSE NULL END AS NEG_PEG_DECREASE, PRICE - 1 AS DEVIATION, CASE WHEN DEVIATION > 0 THEN DEVIATION ELSE NULL END AS POS_DEV, CASE WHEN DEVIATION < 0 THEN DEVIATION ELSE NULL END AS NEG_DEV FROM ethereum.token_prices_hourly WHERE token_address IN ('0xa47c8bf37f92abed4a126bda807a7b7498661acd', -- UST '0x99d8a9c45b2eca8864373a26d1459e3dff1e17f3', -- MIM '0x674c6ad92fd080e4004b2312b45f796a192d27a0', -- USN '0x853d955acef822db058eb8505911ed77f175b99e', -- FRAX '0x6b175474e89094c44da98b954eedeac495271d0f', -- Dai Stablecoin '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC ) AND hour > {{date}} UNION SELECT block_date, SYMBOL, PRICE, LAG(PRICE) ignore nulls over(PARTITION BY SYMBOL ORDER BY block_date ASC) as PRICE_PREV, CASE WHEN PRICE_PREV > 1 AND PRICE_PREV - PRICE > 0 THEN 1 ELSE NULL END AS POS_PEG_DECREASE, CASE WHEN PRICE_PREV > 1 AND PRICE_PREV - PRICE < 0 THEN 1 ELSE NULL END AS POS_PEG_INCREASE, CASE WHEN PRICE_PREV < 1 AND PRICE_PREV - PRICE < 0 THEN 1 ELSE NULL END AS NEG_PEG_INCREASE, CASE WHEN PRICE_PREV < 1 AND PRICE_PREV - PRICE > 0 THEN 1 ELSE NULL END AS NEG_PEG_DECREASE, PRICE - 1 AS DEVIATION, CASE WHEN DEVIATION > 0 THEN DEVIATION ELSE NULL END AS POS_DEV, CASE WHEN DEVIATION < 0 THEN DEVIATION ELSE NULL END AS NEG_DEV FROM UST_TERRA UNION SELECT block_date, SYMBOL, PRICE, LAG(PRICE) ignore nulls over(PARTITION BY SYMBOL ORDER BY block_date ASC) as PRICE_PREV, CASE WHEN PRICE_PREV > 1 AND PRICE_PREV - PRICE > 0 THEN 1 ELSE NULL END AS POS_PEG_DECREASE, CASE WHEN PRICE_PREV > 1 AND PRICE_PREV - PRICE < 0 THEN 1 ELSE NULL END AS POS_PEG_INCREASE, CASE WHEN PRICE_PREV < 1 AND PRICE_PREV - PRICE < 0 THEN 1 ELSE NULL END AS NEG_PEG_INCREASE, CASE WHEN PRICE_PREV < 1 AND PRICE_PREV - PRICE > 0 THEN 1 ELSE NULL END AS NEG_PEG_DECREASE, PRICE - 1 AS DEVIATION, CASE WHEN DEVIATION > 0 THEN DEVIATION ELSE NULL END AS POS_DEV, CASE WHEN DEVIATION < 0 THEN DEVIATION ELSE NULL END AS NEG_DEV FROM UST_THORCHAIN ) SELECT SYMBOL, COUNT(POS_DEV) AS "Positive Count", COUNT(NEG_DEV) AS "Negative Count", "Positive Count"+"Negative Count" AS "Total Count", ROUND( "Positive Count"/"Total Count"*100,4) AS "Positive Dev %", ROUND( "Negative Count"/"Total Count"*100,4) AS "Negative Dev %", -- counts to restore to peg COUNT(POS_PEG_DECREASE) AS POS_PEG_DECREASE_COUNT, COUNT(POS_PEG_INCREASE) AS POS_PEG_INCREASE_COUNT, POS_PEG_DECREASE_COUNT + POS_PEG_INCREASE_COUNT AS POS_PEG_COUNT, COUNT(NEG_PEG_INCREASE) AS NEG_PEG_INCREASE_COUNT, COUNT(NEG_PEG_DECREASE) AS NEG_PEG_DECREASE_COUNT, NEG_PEG_INCREASE_COUNT + NEG_PEG_DECREASE_COUNT AS NEG_PEG_COUNT, -- positive ROUND(MAX(POS_DEV),5) AS "Max Positive Dev", ROUND(AVG(POS_DEV),5) AS "Avg Positive Dev", ROUND(MEDIAN(POS_DEV),5) AS "Median Positive Dev", -- positive peg time frame ROUND(POS_PEG_DECREASE_COUNT/nullif(POS_PEG_COUNT,0)*100,4) AS "Decrease when Pos-Peg", -- good ROUND(POS_PEG_INCREASE_COUNT/nullif(POS_PEG_COUNT,0)*100,4) AS "Increase when Pos-Peg", -- negative ROUND(MIN(NEG_DEV),5) AS "Min Negative Dev", ROUND(AVG(NEG_DEV),5) AS "Avg Negative Dev", ROUND(MEDIAN(NEG_DEV),5) AS "Median Negative Dev", -- negative peg time frame ROUND(NEG_PEG_DECREASE_COUNT/nullif(NEG_PEG_COUNT,0)*100,4) AS "Decrease when Neg-Peg", ROUND(NEG_PEG_INCREASE_COUNT/nullif(NEG_PEG_COUNT,0)*100,4) AS "Increase when Neg-Peg", -- good -- Move towards peg value (POS_PEG_DECREASE_COUNT+NEG_PEG_INCREASE_COUNT)/nullif((POS_PEG_DECREASE_COUNT+POS_PEG_INCREASE_COUNT+NEG_PEG_INCREASE_COUNT+NEG_PEG_DECREASE_COUNT),0)*100 AS "Price Move to Peg-Value", (POS_PEG_INCREASE_COUNT+NEG_PEG_DECREASE_COUNT)/nullif((POS_PEG_DECREASE_COUNT+POS_PEG_INCREASE_COUNT+NEG_PEG_INCREASE_COUNT+NEG_PEG_DECREASE_COUNT),0)*100 AS "Price Did not Move to Peg-Value" FROM ERC_TOKEN GROUP BY 1 ORDER BY 1

capitulation - stablecoin peg