with txs AS (
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
type,
count( DISTINCT user) as "Number of daily txns"
from txs
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
)
SELECT
BLOCK_TIMESTAMP:: date as date,
type,
count( DISTINCT tx) as "Number of daily txns",
count(DISTINCT user) as "Number of daily active users"
from txs
where type in (SELECT type from t2)
GROUP BY 1, 2
ORDER BY 1