with x as

(SELECT

BLOCK_TIMESTAMP,

signers[0] as player,

TX_ID

from eclipse.core.fact_transactions

where block_timestamp::date >= '2024-12-01'

and (

LOG_MESSAGES[0] LIKE '%Instruction: Click%'

OR LOG_MESSAGES[1] LIKE '%Instruction: Click%'

OR LOG_MESSAGES[2] LIKE '%Instruction: Click%'

OR LOG_MESSAGES[3] LIKE '%Instruction: Click%'

OR LOG_MESSAGES[4] LIKE '%Instruction: Click%'

)

and (

LOG_MESSAGES[0] LIKE '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

OR LOG_MESSAGES[1] LIKE '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

OR LOG_MESSAGES[2] LIKE '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

OR LOG_MESSAGES[3] LIKE '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

OR LOG_MESSAGES[4] LIKE '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

)

and SUCCEEDED = 'TRUE'),

stat as

(select DISTINCT player,

count(DISTINCT TX_ID) as tps

from x

group by all)

select case

when tps < 1000 then 'A. <1000 Taps'

when tps >= 1000 and tps < 5000 then 'B. 1k~5k Taps'

when tps >= 5000 and tps < 15000 then 'C. 5k~15k Taps'

when tps >= 15000 and tps < 50000 then 'D. 15k~50k Taps'