-- forked from retdrop - arb WAU @ https://flipsidecrypto.xyz/edit/queries/9dd5c188-6f51-4a3e-baa0-02e6077b706d

with arb_n as (select

DISTINCT from_address as addy, min(block_timestamp::date) as f_date

from optimism.core.fact_transactions

where nonce = '0'

GROUP by all

),

final as

(SELECT

date_trunc('day',f_date) as date, count(DISTINCT addy) as "New addresses", case

when f_date < '2022-06-01' then 'A.Before airdrop #1'

when f_date >= '2022-06-01' and f_date < '2023-02-09' then 'B.Between airdrop #1 & #2'

when f_date >= '2023-02-09' and f_date < '2023-09-18' then 'C.Between airdrop #2 & #3'

when f_date >= '2023-09-18' then 'D.After airdrop #3'

end as period

from arb_n

GROUP by all)

SELECT DISTINCT period , sum("New addresses")/count(DISTINCT date) as avg_new

from final

GROUP by all