MLDZMNTotal actions Compound V3 on Polygon
    Updated 2024-10-30
    WITH compound_v3_polygon_pools AS (
    SELECT lower('0xf25212e676d1f7f89cd72ffee66158f541246445') AS pool, lower('0x3c499c542cef5e3811e1192ce70d8cc03d5c3359') AS token --usdc
    union all
    select lower('0xaeB318360f27748Acb200CE616E389A6C9409a07') as pool, lower('0xc2132D05D31c914a87C6611C10748AEb04B58e8F') as token --USDT
    ),
    ------------------------------------------------------------------------------------------------------------------------------------------
    price_tab as (
    select
    HOUR::date as day,
    TOKEN_ADDRESS,
    SYMBOL,
    DECIMALS,
    avg(PRICE) as token_price

    from polygon.price.ez_prices_hourly
    group by 1,2,3,4
    ),

    ------------------------------------------------------------------------------------------------------------------------------------------
    deposit_compound_v3_polygon as (
    select
    'Polygon' as chain,
    BLOCK_NUMBER,
    BLOCK_TIMESTAMP,
    TX_HASH,
    EVENT_INDEX,
    'Deposit' as action,
    'Compound V3' as PLATFORM,
    CONTRACT_ADDRESS as PROTOCOL_MARKET,
    concat('0x',substring(topics[3], 26+1, 40)) as TOKEN_ADDRESS,
    p.symbol as TOKEN_SYMBOL,
    concat('0x',substring(topics[1], 26+1, 40)) AS DEPOSITOR,
    ethereum.public.udf_hex_to_int(data)::float as AMOUNT_UNADJ,
    AMOUNT_UNADJ / pow (10,p.DECIMALS) as amount,
    amount * token_price as AMOUNT_USD
    from polygon.core.fact_event_logs logs
    QueryRunArchived: QueryRun has been archived