SELECT

x.BLOCK_TIMESTAMP as BLOCK_TIMESTAMP,

x.TX_ID as tx,

x.PROPOSER as user,

y.EVENT_TYPE as type,

y.EVENT_CONTRACT

FROM flow.core.fact_transactions x JOIN flow.core.fact_events y ON x.TX_ID = y.TX_ID

WHERE x.TX_SUCCEEDED = 'true'

and x.BLOCK_TIMESTAMP >= '2023-01-01'

),

t2 as (SELECT

EVENT_CONTRACT,

count( DISTINCT user) as "Number of daily users"

from txs

GROUP BY 1

ORDER BY 2 DESC

LIMIT 10

BLOCK_TIMESTAMP:: date as date,

REVERSE(SUBSTRING(REVERSE(EVENT_CONTRACT), 1, CHARINDEX('.', REVERSE(EVENT_CONTRACT))-1)) as contract,

count( DISTINCT tx) as "Number of daily txns",

count(DISTINCT user) as "Number of daily active users"

from txs

where EVENT_CONTRACT in (SELECT EVENT_CONTRACT from t2)

GROUP BY 1, 2

ORDER BY 1