maybeyonasbico_eth_deposit
    Updated 2022-08-03
    with
    deposits as (
    select
    block_timestamp,
    tx_hash,
    '0x'|| substr(topics[1],27) as depositor,
    '0x'|| substr(topics[2],27) as token_deposited,
    case token_deposited
    when '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' then 'USDC'
    when '0xdac17f958d2ee523a2206206994597c13d831ec7' then 'USDT'
    when '0xf17e65822b568b3903685a7c9f496cf7656cc6c2' then 'BICO'
    when '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' then 'ETH'
    end as token,
    case token_deposited
    when '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' then 6
    when '0xdac17f958d2ee523a2206206994597c13d831ec7' then 6
    else 18
    end as decimals,
    regexp_substr_all(SUBSTR(data, 3, len(data)), '.{64}') AS segmented_data,
    ethereum.public.udf_hex_to_int(segmented_data[0]) as chainId,
    case chainId
    when '1' then 'ETH'
    when '10' then 'OPT'
    when '56' then 'BSC'
    when '137' then 'POL'
    when '42161' then 'ARB'
    when '43114' then 'AVA'
    else 'sus'
    end as chainName,
    ethereum.public.udf_hex_to_int(segmented_data[1])/pow(10,decimals) as amount,
    ethereum.public.udf_hex_to_int(segmented_data[2])/pow(10,decimals) as reward,
    substr(
    hex_decode_string(segmented_data[5]),
    0,
    ethereum.public.udf_hex_to_int(segmented_data[4])
    ) as tag
    Run a query to Download Data