SELECT DISTINCT tx_signer

, SPLIT(tx_signer, '.') AS app

, app[1] a1

, app[2] a2

, app[3] a3

, CASE

WHEN a1 IS NOT NULL AND a2 IS NOT NULL AND a3 IS NOT NULL THEN concat(a1,'.',a2,'.',a3)

WHEN a1 IS NOT NULL AND a2 IS NOT NULL AND a3 IS NULL THEN concat(a1,'.',a2)

WHEN a1 IS NOT NULL AND a2 IS NULL AND a3 IS NULL THEN concat(a1)

END AS parent

--, *

FROM near.core.fact_actions_events

JOIN near.core.fact_transactions

USING(tx_hash)

WHERE action_name = 'DeployContract'

),

deg AS (

SELECT parent

, COUNT(tx_signer) AS degree

FROM contracts

GROUP BY 1

HAVING parent is not null

SELECT *

, row_number() OVER (ORDER BY degree DESC) AS rank

FROM deg

ORDER BY rank

limit 20

-- action_data:code es el contract creator