Query editor
with safes as (selectaddress,date_trunc('week', creation_time) AS creation_weekfrom safe_optimism.safeswhere creation_time >= (date_trunc('week', current_date) - INTERVAL '84' day)), safe_txs as (selectaddress,date_trunc('week', block_time) as tx_weekfrom safe_optimism.transactionswhere block_time >= (date(current_date) - INTERVAL '84' day)group by address, date_trunc('week', block_time)), combined as (selects.creation_week,s.address,t.tx_week,--EXTRACT(DAY FROM (tx_week - creation_week)) / 7 as week_diff(EXTRACT(WEEK FROM t.tx_week) - EXTRACT(WEEK FROM s.creation_week))+ (EXTRACT(YEAR FROM t.tx_week) - EXTRACT(YEAR FROM s.creation_week)) * 52 AS week_difffrom safes sleft outer join safe_txs ton s.address = t.address)select creation_week,COUNT(DISTINCT address) as safes_created,cast(SUM(CASE WHEN week_diff = 0 THEN 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) AS week_1_retention,cast(sum(case when week_diff = 1 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_2_retention,cast(sum(case when week_diff = 2 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_3_retention,cast(sum(case when week_diff = 3 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_4_retention,cast(sum(case when week_diff = 4 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_5_retention,cast(sum(case when week_diff = 5 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_6_retention,cast(sum(case when week_diff = 6 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_7_retention,cast(sum(case when week_diff = 7 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_8_retention,cast(sum(case when week_diff = 8 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_9_retention,cast(sum(case when week_diff = 9 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_10_retention,cast(sum(case when week_diff = 10 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_11_retention,cast(sum(case when week_diff = 11 then 1 ELSE 0 END) as double) / cast(COUNT(DISTINCT address) as double) as week_12_retentionfrom combinedgroup by 1order by 1 desc