0xtoshizora1
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 Cluster as (
SELECT * FROM (VALUES
) t(address) ),
ProjectCount as (
SELECT
SENDER_WALLET,
LISTAGG(PROJECT || ' (' || PROJECT_COUNT || ')', ', ') WITHIN GROUP (ORDER BY project) AS project_list
FROM (
SELECT
SENDER_WALLET,
PROJECT,
COUNT(*) AS PROJECT_COUNT
FROM external.layerzero.fact_transactions_snapshot
WHERE PROJECT IS NOT NULL
AND SENDER_WALLET IN (SELECT address FROM Cluster)
GROUP BY SENDER_WALLET, PROJECT
) AS subquery
GROUP BY SENDER_WALLET;
),
TxEth as (
SELECT
FROM_ADDRESS,
LISTAGG(DecodedFunction || ' (' || FunctionCount || ')', ', ') WITHIN GROUP (ORDER BY DecodedFunction) AS eth_function_list
FROM (
SELECT
FROM_ADDRESS,
CASE
--WHEN ORIGIN_FUNCTION_SIGNATURE = '0x' THEN 'ETH_TRANSFER'
--WHEN ORIGIN_FUNCTION_SIGNATURE = '0xe9e05c42' THEN CONCAT('depositTransaction ', SUM(VALUE), 'ETH')
--WHEN ORIGIN_FUNCTION_SIGNATURE = '0xb2267a7b' THEN CONCAT('sendMessage Scroll ', SUM(VALUE), 'ETH')
WHEN ORIGIN_FUNCTION_SIGNATURE = '0xe9e05c42' THEN CONCAT('depositTransaction() into ZORA ', SUM(VALUE), 'ETH => Tx in ', BLOCK_TIMESTAMP) ELSE NULL
END AS DecodedFunction,
COUNT(ORIGIN_FUNCTION_SIGNATURE) AS FunctionCount
Run a query to Download Data