pier123-b7LIiisolanabw4
Updated 2022-08-12Copy Reference Fork
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
›
⌄
with eth_tx as (select c.symbol, token_amount_raw/pow(10,c.decimals) as token_amount,t.* from (select
regexp_substr_all(SUBSTR(input_data, len(origin_function_signature) + 1, len(input_data)), '.{64}') AS segmented_data, lower(concat('0x',ltrim((segmented_data[0]),'0'))) as token_address,
ethereum.public.udf_hex_to_int(segmented_data[1]) as token_amount_raw, ethereum.public.udf_hex_to_int(segmented_data[2]) as chain_id,
* from ethereum.core.fact_transactions where 1=1
and to_address = lower('0x3ee18B2214AFF97000D974cf647E7C347E8fa585') and origin_function_signature = '0x0f5287b0' and chain_id = 1 and block_timestamp >= CURRENT_DATE - 90
) t join ethereum.core.dim_contracts c on t.token_address = c.address
),
eth_txns as (select 'ETH' as symbol,eth_value as token_amount,regexp_substr_all(SUBSTR(input_data, len(origin_function_signature) + 1, len(input_data)), '.{64}') AS segmented_data, ethereum.public.udf_hex_to_int(segmented_data[0]) as chain_id,
* from ethereum.core.fact_transactions
where 1=1 and to_address = lower('0x3ee18B2214AFF97000D974cf647E7C347E8fa585') and origin_function_signature = '0x9981509f' and chain_id = 1
and block_timestamp >= CURRENT_DATE - 90
),
users as (select distinct from_address as wallet
from((select symbol, token_amount, token_address, block_timestamp, tx_hash, from_address from eth_tx)
union (select 'WETH' as symbol, token_amount, 'WETH' as token_address, block_timestamp, tx_hash, from_address from eth_txns))
),
swappers as (select distinct origin_from_address from ethereum.core.ez_dex_swaps where origin_from_address in (select wallet from users)
and block_timestamp >= CURRENT_DATE - 90
),
nft_traders as (
select distinct origin_from_address from ethereum.core.ez_nft_sales
where origin_from_address in (select wallet from users) and block_timestamp >= CURRENT_DATE - 90
)
select 'Ethereum User Behavior' as type, (select count(*) from users) as "Wormhole Users", (select count(*) from swappers) as "Swappers"
, (select count(*) from nft_traders) as "NFT Traders"
Run a query to Download Data