maybeyonasbico_eth_deposit
Updated 2022-08-03
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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