acc_mapping as ( SELECT

SIGNERS[0] as main_acc, SIGNERS[1] as playing_acc

from eclipse.core.fact_transactions

where

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

SUCCEEDED = 'TRUE'

and LOG_MESSAGES[0] like '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

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

),

combine as (SELECT

count(DISTINCT tx_id) as "Total tapsx",

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

count(DISTINCT tx_id) / count(DISTINCT date_trunc('second', block_timestamp))

as avg_secx,

count(DISTINCT tx_id) / count(DISTINCT date_trunc('minute', block_timestamp))

as avg_minx,

count(DISTINCT tx_id) / count(DISTINCT date_trunc('hour', block_timestamp))

as avg_hourx,

COUNT(DISTINCT block_timestamp::date) AS actvx,

max(block_timestamp::date) as "last playing datex"

from

eclipse.core.fact_events

-- join acc_mapping on "In-game address" = playing_acc

where

block_timestamp::date >= '2025-03-15'