contract_transactions AS (

SELECT

ev.block_timestamp::date AS utc_date

, split(event_contract, '.')[2]::string AS contract_label

, count(distinct tx_id) AS transactions

FROM flow.core.fact_events AS ev

WHERE ev.tx_succeeded = True

GROUP BY 1,2

),

regrouped_contract_transactions AS (

SELECT

utc_date

, (CASE WHEN lower(contract_label) LIKE '%topshot%' THEN contract_label

WHEN transactions < 1000 THEN 'Others'

ELSE contract_label END) AS contract_label

, sum(transactions) AS transactions

FROM contract_transactions

GROUP BY 1,2

SELECT *

FROM regrouped_contract_transactions

ORDER BY 1 DESC, 2