Rodolfo-LimaPolygon Daily TXs & Unique Address
    Updated 2022-07-26
    /*
    Q1. Count and plot the number of daily transactions and unique addresses on Polygon, beginning July 1, 2022.
    In your estimation, discuss whether Polygon is still growing, has reached a plateau, etc.
    */
    WITH DAILY_TX AS (
    SELECT
    DATE_TRUNC('day',BLOCK_TIMESTAMP) AS DATE,
    COUNT(DISTINCT(TX_HASH)) AS TXs,
    COUNT(DISTINCT(FROM_ADDRESS)) AS UNIQUE_ADDRESS
    FROM
    polygon.core.fact_transactions
    WHERE DATE >= '2022-07-01'
    AND TX_HASH IS NOT NULL
    --AND FROM_ADDRES NOT IN ((SELECT DISTINCT TO_ADDRESS FROM polygon.core.fact_transactions WHERE DATE_TRUNC('day',BLOCK_TIMESTAMP) => CURRENT_DATE - INTERVAL '15days'))
    GROUP BY 1
    ORDER BY 1 ASC)

    SELECT
    DATE,
    TXs,
    UNIQUE_ADDRESS,
    AVG(TXs) OVER(ORDER BY DATE ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MA_2_TX ,-- 2 DAYS MOVING AVERAGE FOR DAILY TX
    AVG(UNIQUE_ADDRESS) OVER(ORDER BY DATE ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MA_2_ADDRESS -- 2 DAYS MOVING AVERAGE FOR UNIQUE ADDRESS
    FROM
    DAILY_TX
    ORDER BY 1


    Run a query to Download Data