-- First CTE captures user interactions as receivers

SELECT

DATE_TRUNC('day', ft.block_timestamp) AS activity_date,

ft.tx_receiver AS participant,

ft.tx_hash

FROM near.core.fact_transactions ft

INNER JOIN near.core.dim_address_labels dal

ON ft.tx_signer = dal.address

WHERE dal.project_name = 'sweat'

AND dal.label_type = 'games'

AND ft.tx_succeeded = TRUE

AND ft.block_timestamp >= '2025-01-01'

AND ft.block_timestamp < CURRENT_DATE

UNION ALL

-- Second part captures user interactions as signers

SELECT

DATE_TRUNC('day', ft.block_timestamp) AS activity_date,

ft.tx_signer AS participant,

ft.tx_hash

FROM near.core.fact_transactions ft

INNER JOIN near.core.dim_address_labels dal

ON ft.tx_receiver = dal.address

WHERE dal.project_name = 'sweat'

AND dal.label_type = 'games'

AND ft.tx_succeeded = TRUE

AND ft.block_timestamp >= '2025-01-01'

AND ft.block_timestamp < CURRENT_DATE

),

first_interaction AS (

SELECT

participant,

MIN(activity_date) AS first_active_date