WITH wallet_retention AS (

SELECT

DISTINCT date_trunc('week',block_timestamp) AS week

,1 AS cohort

,t.from_address AS address

FROM optimism.core.fact_transactions t

WHERE block_timestamp > CURRENT_DATE() - INTERVAL '52 WEEKS'

AND t.from_address NOT IN (SELECT address FROM optimism.core.dim_contracts)

),

tb AS (

SELECT

CASE WHEN r.week + INTERVAL '7 days' > CURRENT_DATE() THEN NULL ELSE r.week END AS week

,CASE WHEN r.week + INTERVAL '7 days' > CURRENT_DATE() THEN NULL ELSE SUM(r.cohort) OVER(ORDER BY r.week) END AS cohort

,CASE WHEN r.week + INTERVAL '14 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT a.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_1

,CASE WHEN r.week + INTERVAL '21 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT b.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_2

,CASE WHEN r.week + INTERVAL '28 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT c.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_3

,CASE WHEN r.week + INTERVAL '35 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT d.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_4

,CASE WHEN r.week + INTERVAL '42 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT e.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_5

,CASE WHEN r.week + INTERVAL '49 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT f.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_6

,CASE WHEN r.week + INTERVAL '56 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT g.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_7

,CASE WHEN r.week + INTERVAL '63 days' > CURRENT_DATE() THEN NULL ELSE ROUND(COUNT(DISTINCT h.address) * 1.0 / COUNT(DISTINCT r.address), 3) END AS week_8

FROM wallet_retention r

LEFT JOIN wallet_retention a

ON a.week = r.week+interval '1 week'

AND r.address=a.address

LEFT JOIN wallet_retention b

ON b.week = r.week+interval '2 weeks'

AND r.address=b.address

LEFT JOIN wallet_retention c

ON c.week = r.week + interval '3 weeks'

AND r.address=c.address

LEFT JOIN wallet_retention d

ON d.week = r.week+interval '4 weeks'

AND r.address=d.address