WITH dimension_TX AS

SELECT

distinct a.TX_ID,

PROPOSER,

a.BLOCK_TIMESTAMP,

CASE WHEN EVENT_CONTRACT IN ('A.e3ad6030cbaff1c2.DimensionX', 'A.e3ad6030cbaff1c2.DimensionXPromo') then 'Dimension X'

WHEN EVENT_CONTRACT = 'A.20187093790b9aef.Gamisodes' then 'Gamisodes Inspector Gadget'

WHEN EVENT_CONTRACT = 'A.12d80714e3e5b7a6.ChessCombo' then 'Flow Chess'

WHEN EVENT_CONTRACT IN ('A.8529aaf64c168952.MonoCat', 'A.8529aaf64c168952.MonoCatMysteryBox') then 'Mono Cats'

WHEN EVENT_CONTRACT = 'A.47cbd3edd044cb5d.ArleeScene' then 'Arlequin'

WHEN EVENT_CONTRACT IN ('A.85b8bbf926dcddfa.NowggNFT', 'A.85b8bbf926dcddfa.NowggPuzzle') then 'Now.gg'

WHEN EVENT_CONTRACT IN ('A.83ed64a1d4f3833f.InceptionBlackBox', 'A.83ed64a1d4f3833f.InceptionAvatar') then 'Inception Animals'

WHEN EVENT_CONTRACT IN ('A.62b3063fbe672fc8.ZeedzINO', 'A.62b3063fbe672fc8.ZeedzDrops', 'A.62b3063fbe672fc8.ZeedzMarketplace', 'A.e1c34bb70fbb5357.ZeedzItems') then 'Zeedz'

WHEN EVENT_CONTRACT = 'A.b56aa3ab87a06926.MastersOfMMA' then 'Masters of MMA'

WHEN EVENT_CONTRACT = 'A.164fc4203b9de90b.Blockanime' then 'Blockanime'

WHEN EVENT_CONTRACT IN ('A.a8d493db1bb4df56.SolarpupsMarket', 'A.a8d493db1bb4df56.SolarpupsNFT') then 'SolarPups'

WHEN EVENT_CONTRACT = 'A.de7a5daf9df48c65.BasicBeasts' then 'Basic Beasts'

ELSE 'Others' END AS FLOW_GAMES

FROM

flow.core.fact_transactions a JOIN flow.core.fact_events b ON a.TX_ID = b.TX_ID

WHERE

a.TX_SUCCEEDED = 'true'

AND

FLOW_GAMES != 'Others'

SELECT

FLOW_GAMES,

COUNT(distinct TX_ID) as txs,

COUNT(distinct PROPOSER) as users

FROM