(SELECT

TX_FROM as main_acc,

TX_TO as playing_acc

from eclipse.core.fact_transfers a

join eclipse.core.fact_transactions b

on a.tx_id = b.tx_id

where a.block_timestamp::date >= '2024-12-01' and

MINT like '%111111111111111111111111111111111111%'

and a.SUCCEEDED = 'TRUE'

and LOG_MESSAGES[0] like '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

and LOG_MESSAGES[1] = 'Program log: Instruction: GrantClicker')

SELECT

TO_VARCHAR(count(DISTINCT TX_ID), '999,999,999,999') as "Total taps",

main_acc as "Main wallet address",

signers[0] as "In-game address",

round(count(DISTINCT TX_ID)/count(DISTINCT date_trunc('second',block_timestamp))) as avg_sec,

round(count(DISTINCT TX_ID)/count(DISTINCT date_trunc('minute',block_timestamp))) as avg_min,

round(count(DISTINCT TX_ID)/count(DISTINCT date_trunc('hour',block_timestamp))) as avg_hour,

count(DISTINCT block_timestamp::date) as "# days being active"

from eclipse.core.fact_transactions

join chrg on

signers[0] = playing_acc

where BLOCK_TIMESTAMP <= '{{snapshot_time}}'

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%'