pecio222USERS individual lfj aggregator using router events
    Updated 2025-06-18
    -- forked from lfj aggregator using router events @ https://flipsidecrypto.xyz/studio/queries/2c2bac39-3b0b-4ad7-9b77-a45b56999cf0
    with all_events as (
    select
    block_timestamp,
    date_trunc('hour', block_timestamp) as hour,
    tx_hash,
    case
    when DECODED_LOG ['tokenIn'] = '0x0000000000000000000000000000000000000000' then '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'
    else DECODED_LOG ['tokenIn']
    end as token_in,
    case
    when DECODED_LOG ['tokenOut'] = '0x0000000000000000000000000000000000000000' then '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'
    else DECODED_LOG ['tokenOut']
    end as token_out,
    DECODED_LOG ['amountIn'] as amount_in,
    DECODED_LOG ['amountOut'] as amount_out,
    DECODED_LOG ['sender'] as user
    from
    avalanche.core.ez_decoded_event_logs
    where
    contract_address = lower('0x45a62b090df48243f12a21897e7ed91863e2c86b')
    and ORIGIN_TO_ADDRESS = lower('0x45a62b090df48243f12a21897e7ed91863e2c86b') --only directly with router, ignore LO etc
    and block_timestamp > TO_TIMESTAMP(1742947200) -- Wednesday, 26 March 2025 00:00:00
    ),
    all_txs as (
    select
    block_timestamp,
    tx_hash,
    case
    when concat('0x', substring(INPUT_DATA, 11 + 24, 40)) = '0xb35033d71cf5e13cab5eb8618260f94363dff9cf' then 'LFJ'
    when concat('0x', substring(INPUT_DATA, 11 + 24, 40)) = '0xc04f291347d21dc663f7646056db22bff8ce8430' then (
    CASE
    WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '88de50b233052e4fb783d4f6db78cc34fea3e9fc' THEN 'ODOS'
    WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '6131b5fae19ea4f9d964eac0408e4408b66337b5' THEN 'KYBER'
    WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '38575264810371c15f0e5744fa2ab29cdef7245d' THEN 'OKX'
    WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '1dac23e41fc8ce857e86fd8c1ae5b6121c67d96d' THEN 'OKX'
    Last run: 15 days ago
    USER
    AVG_USD
    SUM_USD
    HIGHEST_USD_VALUE_SWAP
    SWAPS_AMOUNT
    1
    0x111183d9f9c161b350776ad736f25bcf9a71cafe
    44975.4156039365.67116012.371246
    2
    0x85ac420773116e916e9671cb4ac1059635606cf2
    150995.2639711752.99604406.71263
    3
    0xc638fc731ffd77b17c486f5ae4a85d40ad9db4fc
    50517.6834250986.63400135.47678
    4
    0x027f6c45f761a0ce410cb4e9f6355905db4227ff
    305381.6229622017.25878860.2297
    5
    0xcb4a7b790edb7fa3e2731efd7ed85275f92fc74a
    184055.8616380971.97595481.1789
    6
    0xa3a45a745d99b11f068e9c75930daae44ed65218
    227509.5515698158.72228921.0869
    7
    0xb4d2e36b4e05f88d211caec63c5237539f08f8da
    805.7213442656.81984.9616691
    8
    0xd77f6fdcc248a27bcdcd478f020332e3e2fde618
    227553.5512742999228790.2156
    9
    0xb17b3f999c9f6181cd2a573cdfdb119ee1158a6b
    111070.169663103.6609095.1487
    10
    0xe9bb74fe148e41ff9f0a4cae504005015ee6919d
    149660.018380960.41300041.7356
    11
    0x0f9ef41f81b763083b29460cfb788843e7dd993c
    36918.986719253.72340716.41182
    12
    0x8a3044c1be0dffd81936a65e714d210e23a383c9
    13144.16112008.3226389.3465
    13
    0x4d32a3c6827a6b2aec8ce2ef095d599701fdf8f9
    2680.935833709.8364880.092176
    14
    0xbe2a78454e797f982e03265bb32995045a4d3d33
    335943.785711044.27619968.5417
    15
    0xa925fe59719e1253751fad11ee4c73bfee1b9b73
    1102739.545513697.722001360.855
    16
    0x9390e7d8d72a5f5d280d63ad35aff943cc98b01a
    122488.665144523.91249995.8742
    17
    0x0d6a0385a012261d6dcc1742786c8ae0910861e1
    127046.694827774.27642622.4238
    18
    0x84a4af8a46f47ad6ca9f016ed33f7273255ebea3
    7268.074709708.8559594.59651
    19
    0x26f6c31dc1d352485b0023c2e4783df0b87f8abf
    33842.674636446.17107275.24137
    20
    0xa099a4f71b5a1bd40690e441447846c3ae2a2c46
    406299.844469298.261042848.2511
    ...
    37833
    2MB
    6s