ErsvanApp protocol table with price ALGO II
    Updated 2022-07-14
    SELECT BLOCK_TIMESTAMP, ALGO_PRICE, Protocol, SUM(Transactions) AS Transactions FROM (
    SELECT BLOCK_TIMESTAMP, P.PRICE AS ALGO_PRICE, T.TX_GROUP_ID, AP.Protocol, COUNT(TX_ID) AS Transactions, SUM(FEE) AS Fee
    FROM flipside_prod_db.algorand.transactions AS T
    JOIN
    (SELECT
    CASE
    WHEN APP_ID IN (776179559,776176449,752770929,751285119,747239433,747237154,743685742,743679535,696044550,694464549,694405065,686876641,686875498,686862190,686860954,686501760,686500844,686500029,686498781)
    THEN 'Folks finance App IDs'
    WHEN APP_ID IN (649588853,350338509,552635992)
    THEN 'Tinyman App IDs'
    WHEN APP_ID IN (705663269,705657303,674527132,661247364,661207804,661204747,661199805,661193019,661192413,659678644,659677335,658337046,658336870,647785804,647785158,641500474,641499935,639747739,639747119,611869320,553866305,482625868,465814222,465814065,674526408,637795072,637793356,635866213,635864509,635863793,635860537,635813909,635812850,611867642,611804624,611801333,605753404,553869413,482608867,465818260,465814318,465814278,465814149,465814103)
    THEN 'AlgoFi App IDs'
    WHEN APP_ID IN (354073718,354073834)
    THEN 'Algo dex App IDs'
    ELSE NULL
    END AS Protocol,
    TX_GROUP_ID
    FROM flipside_prod_db.algorand.application_call_transaction
    WHERE Protocol IS NOT NULL) AS AP
    ON AP.TX_GROUP_ID = T.TX_GROUP_ID
    JOIN (SELECT BLOCK_HOUR, ((MIN_PRICE_USD_HOUR+MAX_PRICE_USD_HOUR)/2) AS PRICE FROM flipside_prod_db.algorand.prices_swap WHERE ASSET_ID = 0 AND BLOCK_HOUR >= CURRENT_DATE - 90) AS P
    ON DATE_TRUNC('HOUR', T.BLOCK_TIMESTAMP) = P.BLOCK_HOUR
    WHERE T.BLOCK_TIMESTAMP >= CURRENT_DATE - 365
    GROUP BY 1,2,3,4
    ORDER BY 1
    )
    WHERE ALGO_PRICE BETWEEN 0.27 AND 2.83
    GROUP BY 1,2,3
    ORDER BY 1
    Run a query to Download Data