zakkisyedPolygon 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_poly AS (
    SELECT tx_hash, 'polygon' AS blockchain
    FROM polygon.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
    pt.tx_hash,
    pt.block_timestamp,
    pt.amount,
    pt.amount_usd,
    'polygon' AS blockchain
    FROM polygon.core.ez_token_transfers pt
    JOIN logs_poly ON logs_poly.tx_hash = pt.tx_hash
    WHERE contract_address = lower('0xb0897686c545045aFc77CF20eC7A532E3120E0F1')

    ) AS tx
    LEFT JOIN crosschain.price.ez_hourly_token_prices price
    Last run: over 1 year ago
    DATE
    BLOCKCHAIN
    REVENUE_LINK
    REVENUE_USD
    CUMULATIVE_REVENUE_LINK
    CUMULATIVE_REVENUE_USD
    1
    2024-01-30 00:00:00.000polygon0026201.98276026184072.188599805
    2
    2024-01-29 00:00:00.000polygon13.564457993194.64997219526201.98276026184072.188599805
    3
    2024-01-28 00:00:00.000polygon15.380099104220.42910053126188.418302268183877.53862761
    4
    2024-01-27 00:00:00.000polygon0026173.038203164183657.109527078
    5
    2024-01-26 00:00:00.000polygon0.20678542832.94669235326173.038203164183657.109527078
    6
    2024-01-25 00:00:00.000polygon0.20648499242.81439044726172.831417735183654.162834725
    7
    2024-01-24 00:00:00.000polygon9.383429621134.08920928926172.624932743183651.348444278
    8
    2024-01-23 00:00:00.000polygon0026163.241503121183517.259234989
    9
    2024-01-22 00:00:00.000polygon10.523956965158.06983361526163.241503121183517.259234989
    10
    2024-01-21 00:00:00.000polygon0.19815269423.09514508326152.717546156183359.189401374
    11
    2024-01-20 00:00:00.000polygon0026152.519393462183356.094256291
    12
    2024-01-19 00:00:00.000polygon0.71045932610.3833630526152.519393462183356.094256291
    13
    2024-01-18 00:00:00.000polygon75.7111889691102.67938790826151.808934136183345.710893241
    14
    2024-01-17 00:00:00.000polygon0026076.097745168182243.031505334
    15
    2024-01-16 00:00:00.000polygon39.636632959600.49498933426076.097745168182243.031505334
    16
    2024-01-15 00:00:00.000polygon2.63977838640.9922517626036.461112208181642.536516
    17
    2024-01-14 00:00:00.000polygon4.54185772367.80678692726033.821333823181601.54426424
    18
    2024-01-13 00:00:00.000polygon5.04777032571.85675342326029.2794761181533.737477313
    19
    2024-01-12 00:00:00.000polygon137.6034581382043.56273128726024.231705775181461.88072389
    20
    2024-01-11 00:00:00.000polygon25.41505879380.97173126425886.628247637179418.317992603
    ...
    213
    18KB
    94s