SELECT DISTINCT SIGNERS[1] AS tapper

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'

),

event_data AS (

SELECT

TX_ID,

block_timestamp,

signers[0] AS signer

FROM eclipse.core.fact_events

JOIN valid_signers ON signer = tapper

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

AND PROGRAM_ID = 'turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN'

AND SUCCEEDED = 'TRUE'

),

aggregated_data AS (

SELECT

COUNT(TX_ID) AS taps,

COUNT(DISTINCT signer) AS pls,

COUNT(DISTINCT date_trunc('second', block_timestamp)) AS distinct_seconds,

COUNT(DISTINCT date_trunc('minute', block_timestamp)) AS distinct_minutes,

COUNT(DISTINCT date_trunc('hour', block_timestamp)) AS distinct_hours

FROM event_data

taps,

CASE WHEN distinct_seconds = 0 THEN 0 ELSE taps / distinct_seconds END AS avg_sec,

CASE WHEN distinct_minutes = 0 THEN 0 ELSE taps / distinct_minutes END AS avg_min,

CASE WHEN distinct_hours = 0 THEN 0 ELSE taps / distinct_hours END AS avg_hour,

pls,

CASE WHEN pls = 0 THEN 0 ELSE taps / pls END AS avg_plr_tp,

CASE WHEN distinct_minutes = 0 THEN 0 ELSE pls / distinct_minutes END AS avg_min_pl,