BlockTrackerphoenix (transfers) based on tokens
    Updated 2023-09-03
    with phoenix_tx as (
    SELECT
    tx_id as tx
    FROM solana.core.fact_events
    WHERE program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
    AND block_timestamp::date >= '2023-08-23')
    ,
    tokens_price as (
    SELECT
    date_trunc('d',RECORDED_HOUR) as date,
    a.symbol,
    token_address,
    median(close) as usd_price
    FROM solana.price.fact_token_prices_hourly a
    INNER JOIN solana.core.dim_tokens b ON a.id = b.COIN_MARKET_CAP_ID
    WHERE provider = 'coinmarketcap'
    AND date >= '2023-08-23'
    GROUP BY 1 , 2 , 3
    )

    SELECT
    date_trunc('d', block_timestamp) as date,
    symbol,
    count(DISTINCT a.tx_id) as n_transfer,
    sum(amount*usd_price) as value,
    count(DISTINCT tx_from) as sender,
    count(DISTINCT tx_to) as reciever
    FROM solana.core.fact_transfers a
    INNER JOIN phoenix_tx b ON a.tx_id = b.tx
    LEFT JOIN tokens_price c ON date_trunc('d', block_timestamp) = c.date AND a.mint = c.token_address
    WHERE block_timestamp::date >= '2023-08-23'
    GROUP BY 1 , 2
    ORDER BY 1 DESC



    Run a query to Download Data