alirsop-4dec-03-swap_volume
    Updated 2022-12-10
    WITH price
    AS (
    SELECT HOUR::DATE AS Price_date
    ,TOKEN_ADDRESS
    ,decimals
    ,avg(price) AS price_usd
    FROM ethereum.core.fact_hourly_token_prices
    GROUP BY 1
    ,2
    ,3
    ),
    GigaSwap as(
    SELECT date_trunc('day', BLOCK_TIMESTAMP)::DATE AS DATE
    ,FROM_Address
    ,COUNT(DISTINCT TX_HASH) AS "Number of TX"
    ,COUNT(DISTINCT FROM_ADDRESS) AS "Number of Users"
    ,sum(((RAW_AMOUNT / pow(10, decimals)) * price_usd)) AS "Volume"
    -- ,sum("Number of TX") OVER (
    -- ORDER BY DATE
    -- ) AS "cumulative Number of TX"
    -- ,sum("Volume") OVER (
    -- ORDER BY DATE
    -- ) AS "cumulative Amount"
    -- ,sum("Number of Users") OVER (
    -- ORDER BY DATE
    -- ) AS "cumulative Number of TX"
    FROM ethereum.core.fact_token_transfers GigaSwap
    INNER JOIN price ON (
    GigaSwap.contract_address = price.TOKEN_ADDRESS
    AND GigaSwap.BLOCK_TIMESTAMP::DATE = price.price_date
    )
    WHERE TO_ADDRESS = '0x83249c6794bca5a77eb8c0af9f1a86e055459cea' --GigaSwap
    AND FROM_ADDRESS != '0x83249c6794bca5a77eb8c0af9f1a86e055459cea'
    GROUP BY 1,2
    ORDER BY 1)
    Run a query to Download Data