sqrr_researchTest Ord 7
    Updated 2023-12-19
    WITH total
    AS (
    SELECT date_trunc('day', BLOCK_TIMESTAMP) AS "DATE"
    ,sum(FEE) AS "Total Fees"
    ,count(TX_HASH) AS "Total Transactions"
    FROM bitcoin.core.fact_transactions
    WHERE 1 = 1
    AND BLOCK_NUMBER > 767429
    GROUP BY 1
    )
    ,ordinals
    AS (
    SELECT date_trunc('day', BLOCK_TIMESTAMP) AS "DATE"
    ,sum(FEE) AS "Ordinals Fees"
    ,count(TX_HASH) AS "Ordinals Transactions"
    FROM bitcoin.core.fact_transactions
    WHERE 1 = 1
    AND BLOCK_NUMBER > 767429
    AND HEX::STRING LIKE '%0063036f726401%'
    GROUP BY 1
    )
    ,prices
    AS (
    SELECT date_trunc('day', HOUR) AS "DATE"
    ,avg(PRICE) AS "Price"
    FROM bitcoin.price.ez_hourly_token_prices
    WHERE PROVIDER = 'coingecko'
    GROUP BY 1
    )
    SELECT t."DATE"
    ,"Ordinals Fees"
    ,"Ordinals Fees" * "Price" AS "Ordinals Fees (USD)"
    ,"Total Fees"
    ,"Total Fees" * "Price" AS "Total Fees (USD)"
    ,"Ordinals Transactions"
    ,"Total Transactions"
    Run a query to Download Data