0xtoshi2024-05-29 09:50 AM
Updated 2024-05-29
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 LayerzeroAddress as (
SELECT SENDER_WALLET, COUNT(SOURCE_TRANSACTION_HASH) as txCount
FROM external.layerzero.fact_transactions_snapshot
GROUP BY 1
),
ZoraBridge as (
SELECT DISTINCT FROM_ADDRESS
FROM ethereum.core.ez_native_transfers
WHERE TO_ADDRESS = LOWER('0x1a0ad011913A150f69f6A19DF447A0CfD9551054')
AND FROM_ADDRESS IN (SELECT SENDER_WALLET FROM LayerzeroAddress WHERE txCount > 30)
),
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 FROM_ADDRESS FROM ZKBOB)
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,
QueryRunArchived: QueryRun has been archived