FlippppppaDialect Trading Total copy
    -- forked from kiryl_sol / Dialect Trading Total @ https://flipsidecrypto.xyz/kiryl_sol/q/dialect-trading-per-day-rTmdVZ

    WITH
    filtered_transactions AS (
    SELECT
    DATE_TRUNC('DAY', BLOCK_TIMESTAMP) as date,
    SIGNERS[0]::STRING AS signer,
    PRE_BALANCES[0]::NUMBER AS pre_balance,
    POST_BALANCES[0]::NUMBER AS post_balance
    FROM
    solana.core.fact_transactions,
    LATERAL FLATTEN(input => ACCOUNT_KEYS) AS ACCOUNT_KEYS1,
    LATERAL FLATTEN(input => ACCOUNT_KEYS) AS ACCOUNT_KEYS2,
    LATERAL FLATTEN(input => INSTRUCTIONS) AS INSTRUCTIONS
    WHERE
    ACCOUNT_KEYS1.value:pubkey::STRING = 'TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp'
    AND ACCOUNT_KEYS2.value:pubkey::STRING = 'BGUMAp9Gq7iTEuizy4pqaxsTyUCBK68MDfK752saRPUY'
    and INSTRUCTIONS.value:data::STRING like '%4euRRQ61%'
    AND BLOCK_TIMESTAMP >= '2023-05-17'
    AND SUCCEEDED = TRUE
    )
    SELECT
    count(DISTINCT signer) as unique_traders,
    count(*) as total_trades,
    SUM(post_balance - pre_balance) / 1000000000 as volume,
    (
    SUM(post_balance - pre_balance) / 1000000000 / NULLIF(COUNT(*), 0)
    ) as average_trade
    FROM
    filtered_transactions


    Run a query to Download Data