SELECT

decoded_log:account::string as account_address,

DATE_TRUNC('week', block_timestamp::date) as cohort_week,

block_timestamp::date as event_date

FROM polygon.core.ez_decoded_event_logs

WHERE contract_address = '0x7043e4e1c4045424858ecbced80989feafc11b36'

AND event_name IN ('LiquidityAdded', 'LiquidityRemoved')

UNION

SELECT

origin_from_address as account_address,

DATE_TRUNC('week', block_timestamp::date) as cohort_week,

block_timestamp::date as event_date

FROM gnosis.core.ez_decoded_event_logs

WHERE

DECODED_LOG:src = '0x204e7371ade792c5c006fb52711c50a7efc843ed' OR

DECODED_LOG:dst = '0x204e7371ade792c5c006fb52711c50a7efc843ed'

AND event_name IN ('Withdrawal', 'Deposit')

),

subsequent_events AS (

SELECT

a.account_address,

a.cohort_week,

a.event_date AS first_event_date,

b.event_date AS subsequent_event_date

FROM all_events a

JOIN all_events b ON a.account_address = b.account_address AND b.event_date > a.event_date

),

retention AS (

SELECT

account_address,

cohort_week,

MAX(CASE WHEN subsequent_event_date <= first_event_date + INTERVAL '1 day' THEN 1 ELSE 0 END) AS D1,

MAX(CASE WHEN subsequent_event_date <= first_event_date + INTERVAL '7 days' THEN 1 ELSE 0 END) AS D7,

MAX(CASE WHEN subsequent_event_date <= first_event_date + INTERVAL '30 days' THEN 1 ELSE 0 END) AS D30

FROM subsequent_events