MostlyData_swQoS possible consumers (PFs&BXR) copy
    Updated 2024-11-21
    -- forked from rafal0x-eth / swQoS possible consumers (PFs&BXR) @ https://flipsidecrypto.xyz/rafal0x-eth/q/7IpO8UlqyhJy/swqos-possible-consumers-pfs-bxr

    WITH cex_addresses AS (
    SELECT
    address
    FROM
    solana.core.dim_labels
    WHERE
    label_type = 'cex'
    ),

    priority_fee_payers AS (
    SELECT
    signers[0] AS priority_fee_payer,
    COUNT(DISTINCT tx_id) AS num_transactions,
    SUM((fee - 5000) / 1e9) AS total_priority_fee_paid, -- Calculate total priority fee in SOL
    (SUM(fee - 5000) / COUNT(DISTINCT tx_id)) / 1e9 AS avg_fee_per_tx -- Calculate average fee per transaction
    FROM
    solana.core.fact_transactions
    WHERE
    succeeded = true
    AND (
    '{{n_days}}' = 0
    AND CAST(block_timestamp AS DATE) >= CAST('{{start_date}}' AS DATE)
    AND CAST(block_timestamp AS DATE) < CAST('{{end_date}}' AS DATE)
    )
    OR
    (
    '{{n_days}}' != 0
    AND block_timestamp > CURRENT_DATE() - INTERVAL '{{n_days}} DAYS'
    )
    AND signers[0] NOT IN (SELECT address FROM cex_addresses) -- Exclude CEX addresses
    GROUP BY
    signers[0]
    HAVING
    total_priority_fee_paid > 0.01 -- Filter for addresses paying more than 0 SOL
    QueryRunArchived: QueryRun has been archived