zakkisyedEthereum CCIP Revenue
    Updated 2024-01-30
    -- forked from Final CCIP Data by Blockchain @ https://flipsidecrypto.xyz/edit/queries/3e37c678-d3c1-42b6-b0ad-a143f1f606a7

    -- forked from Final Data @ https://flipsidecrypto.xyz/edit/queries/fffe0ba1-8e4a-4ae8-8f5c-789b0cc3b230

    WITH logs_eth AS (
    SELECT tx_hash, 'ethereum' AS blockchain
    FROM ethereum.core.fact_event_logs
    WHERE topics[0] = '0xaffc45517195d6499808c643bd4a7b0ffeedf95bea5852840d7bfcf63f59e821'
    AND block_timestamp >= '2023-07-01'
    ),

    CCIP_fees AS (
    SELECT
    date_trunc('day', tx.block_timestamp) AS date,
    tx.blockchain,
    COUNT(tx.tx_hash) AS tx_count,
    SUM(tx.amount) AS amount,
    CASE
    WHEN tx.blockchain = 'polygon' THEN SUM(tx.amount) * AVG(price.PRICE)
    ELSE SUM(tx.amount_usd)
    END AS amount_usd
    FROM (
    SELECT
    et.tx_hash,
    et.block_timestamp,
    et.amount,
    et.amount_usd,
    'ethereum' AS blockchain
    FROM ethereum.core.ez_token_transfers et
    JOIN logs_eth ON logs_eth.tx_hash = et.tx_hash
    WHERE contract_address = lower('0x514910771AF9Ca656af840dff83E8264EcF986CA')
    ) AS tx
    LEFT JOIN crosschain.price.ez_hourly_token_prices price
    ON
    --tx.blockchain = price.BLOCKCHAIN AND
    Last run: about 1 year ago
    DATE
    BLOCKCHAIN
    REVENUE_LINK
    REVENUE_USD
    CUMULATIVE_REVENUE_LINK
    CUMULATIVE_REVENUE_USD
    1
    2024-01-30 00:00:00.000ethereum128.880424041933.22589099245435.760807118628049.840474447
    2
    2024-01-29 00:00:00.000ethereum0.42680327126.3006556745306.880383078626116.614583455
    3
    2024-01-28 00:00:00.000ethereum0.44399951136.42915620145306.453579807626110.313927785
    4
    2024-01-27 00:00:00.000ethereum0.45182094966.45940000345306.009580296626103.884771583
    5
    2024-01-26 00:00:00.000ethereum0.46760747296.56422874745305.557759346626097.425371581
    6
    2024-01-25 00:00:00.000ethereum0.47875497836.63760128745305.090151873626090.861142834
    7
    2024-01-24 00:00:00.000ethereum0.60424033338.56756399445304.611396895626084.223541547
    8
    2024-01-23 00:00:00.000ethereum0.43787596966.22794182945304.007156561626075.655977552
    9
    2024-01-22 00:00:00.000ethereum0.692364794810.44936738345303.569280592626069.428035724
    10
    2024-01-21 00:00:00.000ethereum1995.2638003530986.4922877945302.876915797626058.978668341
    11
    2024-01-20 00:00:00.000ethereum0.43101399976.88770348743307.613115447595072.486380551
    12
    2024-01-19 00:00:00.000ethereum0.47536421667.25149820843307.182101447595065.598677063
    13
    2024-01-18 00:00:00.000ethereum0.45987598396.91427318343306.70673723595058.347178855
    14
    2024-01-17 00:00:00.000ethereum0.44624930737.05907315443306.246861246595051.432905672
    15
    2024-01-16 00:00:00.000ethereum180.4665508242743.07279957343305.800611939595044.373832518
    16
    2024-01-15 00:00:00.000ethereum0.44791635866.95880077243125.334061115592301.301032945
    17
    2024-01-14 00:00:00.000ethereum0.47420014787.01873835943124.886144757592294.342232173
    18
    2024-01-13 00:00:00.000ethereum0.52310419157.43223227443124.411944609592287.323493813
    19
    2024-01-12 00:00:00.000ethereum2.31985960134.95734284843123.888840418592279.89126154
    20
    2024-01-11 00:00:00.000ethereum0.723161578510.91899480743121.568980816592244.933918692
    ...
    213
    20KB
    22s