amir007DFKChain Weekly Active Users
    Updated 2023-06-07
    -- DFKChain Weekly Active Users
    with dfk_chain AS
    (
    SELECT trn.block_timestamp::date AS date
    , tx_hash
    , event_name
    , symbol
    , amount
    , amount_usd
    , origin_from_address
    FROM avalanche.core.fact_decoded_event_logs log
    JOIN avalanche.core.ez_token_transfers trn using(tx_hash)
    WHERE trn.block_timestamp::date >= current_date - interval '9 month'
    AND contract_address = '0xc05e61d0e7a63d27546389b7ad62fdff5a91aace' -- Synapse: Bridge
    AND event_name in ('TokenDeposit', 'TokenRedeem')
    AND decoded_log:chainId = '53935' -- https://docs.pokt.network/use/public-rpc/dfk-chain/
    ), prices AS
    (
    SELECT hour::date AS date
    , symbol
    , avg(price) AS price
    FROM avalanche.core.fact_hourly_token_prices
    GROUP BY 1, 2
    ), avax_price AS
    (
    SELECT hour::date AS date
    , avg(price) AS avax_price
    FROM avalanche.core.fact_hourly_token_prices
    WHERE symbol = 'WAVAX'
    GROUP BY 1
    ), jewel_price AS
    (
    SELECT block_timestamp::date AS date
    , 'JEWEL' AS symbol
    , avg((amount_out * avax_price) / amount_in) AS jewel_price
    FROM avalanche.core.ez_dex_swaps swp
    Run a query to Download Data