SocioCryptoPolygon / Others: ez_transactions
Updated 2024-01-15
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
›
⌄
WITH socket_main as (
SELECT a.block_number, a.block_timestamp, a.tx_hash, 'socket_bridge' as contract, b.origin_from_address as sender,
to_varchar(b.contract_address) as token_address, --token sender deposited
-- to_varchar(a.decoded_log:token) as token_in, -- token sent by Socket
'Polygon Mainnet' as src_chain,
a.decoded_log:toChainId as dest_chain_id,
to_varchar(a.decoded_log:receiver) as receiver,
-- to_number(a.decoded_log:amount) as amnt,
b.decoded_log:value as raw_amnt,
a.decoded_log:bridgeName as bridge,
a.tx_status
FROM
polygon.core.ez_decoded_event_logs a , polygon.core.ez_decoded_event_logs b
WHERE
a.block_timestamp >= '2023-11-15'
AND a.tx_hash = b.tx_hash
AND a.event_name = 'SocketBridge'
AND a.contract_address = '0x3a23f943181408eac424116af7b7790c94cb97a5' --SocketBridge
AND a.origin_to_address = '0x3a0b42ce6166abb05d30ddf12e726c95a83d7a16' --metamask
AND b.event_name = 'Transfer'
AND b.decoded_log:from = b.origin_from_address
-- AND a.tx_hash = '0x27592e381fe9cea8952ab3a1994da689606592545d73f25be741bebbb91db4c2' --tx sample
)
SELECT 'Socket' as aggregator, x.*, to_number(x.raw_amnt*price/pow(10,decimals)) as amount, tx_fee
FROM socket_main x
LEFT JOIN polygon.price.ez_hourly_token_prices y
on x.token_address = y.token_address AND y.hour = date_trunc('hour',x.block_timestamp)
LEFT JOIN polygon.core.fact_transactions z
on x.tx_hash = z.tx_hash
--UNION (SQUID Should be added)
--SELECT 'Squid' as , BLOCK_NUMBER, BLOCK_TIMESTAMP, tx_hash, 'suid_bridge' , sender,
QueryRunArchived: QueryRun has been archived