BlockTrackerswap (main query)
    Updated 3 days ago
    with uniswap_V2 as (
    with pool_created as (
    select
    contract_address,
    REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data,
    lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0,
    lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1,
    lower('0x' || substr(segmented_data[0]::string, 25, 40 ) :: string) as pool_address
    from ink.core.fact_event_logs
    WHERE topics[0] = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9'
    and TX_SUCCEEDED
    )
    ,
    swap as (
    select
    block_timestamp,
    block_number,
    origin_from_address,
    origin_to_address,
    ORIGIN_FUNCTION_SIGNATURE,
    tx_hash,
    event_index,
    contract_address,
    lower('0x' || substr(topics[1]::string, 27, 40) :: string) as sender,
    lower('0x' || substr(topics[2]::string, 27, 40) :: string) as address_to,
    REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data,
    livequery.utils.udf_hex_to_int(segmented_data[0]::string) :: int as amount0In,
    livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int as amount1In,
    livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int as amount0Out,
    livequery.utils.udf_hex_to_int(segmented_data[3]::string) :: int as amount1Out
    from ink.core.fact_event_logs
    where topics[0]::string = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'
    and TX_SUCCEEDED
    )

    , result as (
    Last run: 3 days ago
    DATE
    ROUTER
    N_SWAPS
    N_SWAPPERS
    VOLUME_USD
    CUMU_ROUTER_VOLUME
    D_AVG_VOLUME
    AVG_VOLUME_USD
    1
    2025-01-20 00:00:00.000SquidSwap1518155567.9394306834531353.93210714130589.336504484375.459050207
    2
    2025-01-20 00:00:00.000UniswapV2Router0223610235414.2296591372741172.670356130589.336504484150.060295166
    3
    2025-01-20 00:00:00.000DYORSwap63332024620.77285577317772561.4251613130589.33650448443.043309188
    4
    2025-01-20 00:00:00.000SwapRouter0213754471228.708747048528636.474481354130589.33650448454.918048379
    5
    2025-01-20 00:00:00.000Unknown16762035846.557218976562470.395006034130589.33650448416.572610827
    6
    2025-01-20 00:00:00.000Reservoir:Swap4833572746.5770150931153922.05625967130589.3365044845.039590853
    7
    2025-01-20 00:00:00.000SuperSwap42219975.611978921133643.712570229130589.336504484216.861129977
    8
    2025-01-19 00:00:00.000Unknown26682562203.637235921526623.837787058133932.80920707417.884887072
    9
    2025-01-19 00:00:00.000SquidSwap484201212276.2052085584475785.99267645133932.809207074417.045589801
    10
    2025-01-19 00:00:00.000SuperSwap16110017202.856027178123668.100591309133932.80920707496.105340934
    11
    2025-01-19 00:00:00.000UniswapV2Router0282743356503.6391334822705758.44069686133932.80920707464.501871157
    12
    2025-01-19 00:00:00.000SwapRouter021798126120525.68303985457407.765734306133932.80920707466.405335008
    13
    2025-01-19 00:00:00.000DYORSwap170987220470.41554105217747940.6523056133932.80920707412.076941322
    14
    2025-01-19 00:00:00.000Reservoir:Swap2534182819021.8517579931151175.47924458133932.8092070746.404663892
    15
    2025-01-18 00:00:00.000SwapRouter02130115457931.047931748336882.082694456136123.2448014943.920430578
    16
    2025-01-18 00:00:00.000DYORSwap153784340872.23628776117727470.2367645136123.2448014926.099767744
    17
    2025-01-18 00:00:00.000SquidSwap33014597753.5897344514263509.7874679136123.24480149284.167412019
    18
    2025-01-18 00:00:00.000UniswapV2Router0276033132919.8775500962649254.80156338136123.2448014939.47227524
    19
    2025-01-18 00:00:00.000SuperSwap142875370.153336831106465.244564131136123.2448014933.35498967
    20
    2025-01-18 00:00:00.000Reservoir:Swap3260238240540.9175742821132153.62748659136123.2448014910.032397321
    ...
    210
    23KB
    10s