WITH Cluster AS (

SELECT * FROM (VALUES

{{param_fyyW}}

) t(address)

),

TxBase as (

SELECT

FROM_ADDRESS,

LISTAGG(ORIGIN_FUNCTION_SIGNATURE || ' (' || FunctionCount || ')', ', ') WITHIN GROUP (ORDER BY ORIGIN_FUNCTION_SIGNATURE) AS base_function_list,

FROM (

SELECT

FROM_ADDRESS,

ORIGIN_FUNCTION_SIGNATURE,

COUNT(*) AS FunctionCount

FROM polygon.core.fact_transactions

WHERE ORIGIN_FUNCTION_SIGNATURE IS NOT NULL

AND FROM_ADDRESS IN (SELECT address FROM Cluster)

GROUP BY FROM_ADDRESS, ORIGIN_FUNCTION_SIGNATURE

) AS subquery

GROUP BY FROM_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