0xtoshi2024-05-29 09:50 AM
    Updated 2024-05-29
    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