binhachon86. [Easy] Currency Interactions - Percentage of top 100 on volume
    Updated 2021-10-10
    WITH TRADING_TABLE AS(
    SELECT TRADER, TOKEN_0_CURRENCY AS SYMBOL, TOKEN_0_AMOUNT AS AMOUNT, TOKEN_0_AMOUNT_USD AS AMOUNT_USD FROM terra.swaps
    WHERE BLOCK_TIMESTAMP >= GETDATE() - INTERVAL'90 days'
    AND AMOUNT_USD IS NOT NULL
    UNION ALL
    SELECT TRADER, TOKEN_1_CURRENCY AS SYMBOL, TOKEN_1_AMOUNT AS AMOUNT, TOKEN_1_AMOUNT_USD AS AMOUNT_USD FROM terra.swaps
    WHERE BLOCK_TIMESTAMP >= GETDATE() - INTERVAL'90 days'
    AND AMOUNT_USD IS NOT NULL
    ),
    VOLUME_TABLE AS(
    SELECT TRADER, SYMBOL, SUM(AMOUNT) AS AMOUNT, SUM(AMOUNT_USD) AS AMOUNT_USD FROM TRADING_TABLE
    GROUP BY TRADER, SYMBOL
    ),
    VOLUME_TABLE_WITH_INDEX AS (
    SELECT TRADER, SYMBOL, AMOUNT, AMOUNT_USD, row_number() over (partition by SYMBOL order by AMOUNT_USD desc) as INDEX FROM VOLUME_TABLE
    ),
    FREQUENCY_TABLE AS(
    SELECT TRADER, SYMBOL, COUNT(SYMBOL) AS FREQUENCY FROM TRADING_TABLE
    GROUP BY TRADER, SYMBOL
    ),
    FREQUENCY_TABLE_WITH_INDEX AS (
    SELECT TRADER, SYMBOL, FREQUENCY, row_number() over (partition by SYMBOL order by FREQUENCY desc) as INDEX FROM FREQUENCY_TABLE
    ),
    FINAL_TABLE AS(
    SELECT VOLUME_TABLE_WITH_INDEX.TRADER AS TRADER, VOLUME_TABLE_WITH_INDEX.SYMBOL AS SYMBOL,
    VOLUME_TABLE_WITH_INDEX.INDEX AS VOLUME_INDEX, AMOUNT, AMOUNT_USD,
    FREQUENCY_TABLE_WITH_INDEX.INDEX AS FREQUENCY_INDEX, FREQUENCY
    FROM VOLUME_TABLE_WITH_INDEX LEFT JOIN FREQUENCY_TABLE_WITH_INDEX
    ON VOLUME_TABLE_WITH_INDEX.TRADER = FREQUENCY_TABLE_WITH_INDEX.TRADER AND VOLUME_TABLE_WITH_INDEX.SYMBOL = FREQUENCY_TABLE_WITH_INDEX.SYMBOL
    ),
    TOTAL_TABLE AS(
    SELECT SYMBOL, SUM(AMOUNT_USD) AS TOTAL_USD, SUM(FREQUENCY) AS TOTAL_FREQUENCY FROM FINAL_TABLE
    WHERE SYMBOL IS NOT NULL
    GROUP BY SYMBOL
    ),
    TOP_100_FREQUENCY AS(
    Run a query to Download Data