SELECT

DISTINCT tx_hash,

DATE_TRUNC('week', block_timestamp) as activity_week,

block_timestamp

FROM polygon.core.ez_decoded_event_logs

WHERE contract_name ILIKE '%Azuro%'

AND block_timestamp::date >= '2023-01-01'

AND TX_STATUS = 'SUCCESS'

),

bets AS (

SELECT

a.decoded_log:bettor AS bettor,

a.block_timestamp AS bet_time,

b.activity_week

FROM polygon.core.ez_decoded_event_logs a

JOIN all_txns b ON a.tx_hash = b.tx_hash

WHERE a.event_name = 'NewBet'

),

user_activity AS (

SELECT

bettor,

activity_week

FROM bets

GROUP BY bettor, activity_week

),

activity_gap AS (

SELECT

bettor,

activity_week,

LEAD(activity_week) OVER (PARTITION BY bettor ORDER BY activity_week) AS next_activity_week

FROM user_activity

),

churned_users AS (

SELECT

bettor,