SELECT

DISTINCT signers[0] as users,

'aurory' as game

FROM solana.core.fact_events

WHERE program_id='STkwf3sbMapjy7KV3hgrJtcVvY4SvRxWQ8pj4Enw1i5'

), tab2 as (

SELECT

DISTINCT signers[0] AS users,

'staratlas' as game

FROM solana.core.fact_events

WHERE program_id='ATLocKpzDbTokxgvnLew3d7drZkEzLzDpzwgrgWKDbmc'

), tab3 as (

SELECT

DISTINCT signers[0] AS users,

'genopets' as game

FROM solana.core.fact_events

WHERE program_id='StaKe9nb7aUjXpjpZ45o6uJBsZxj2BWCDBtjk8LCg2v'

SELECT --*

CASE WHEN games = 3 THEN 'user of all 3 games'

WHEN games2 = 2 THEN 'aurory and staratlas users'

WHEN games3 = 2 THEN 'aurory and genopets users'

WHEN games4 = 2 THEN 'staratlas and genopets users'

WHEN games5 = 1 THEN 'aurory users'

WHEN games6 = 1 THEN 'genopets users'

WHEN games7 = 1 THEN 'staratlas users' END as users_group,

count(*) as users

FROM (

SELECT

users,

count(*) as games,

COUNT(CASE WHEN game IN ('aurory', 'staratlas') THEN 1 END) as games2,

COUNT(CASE WHEN game IN ('aurory', 'genopets') THEN 1 END) as games3,

COUNT(CASE WHEN game IN ('genopets', 'staratlas') THEN 1 END) as games4,