from core.core.fact_transactions

where tx_succeeded='TRUE'

group by 1)

select date_trunc('year',first_tx) as "Date", count(distinct from_address) as "New Users", case

when (first_tx::date between '2023-01-01' and '2023-03-31') or (first_tx::date between '2024-01-01' and '2024-03-31')

then 'Q1'

when (first_tx::date between '2023-04-01' and '2023-06-30') or (first_tx::date between '2024-04-01' and '2024-06-30')

then 'Q2'

when (first_tx::date between '2023-07-01' and '2023-09-30') or (first_tx::date between '2024-07-01' and '2024-09-30')

then 'Q3'

when (first_tx::date between '2023-10-01' and '2023-12-31') or (first_tx::date between '2024-10-01' and '2024-12-31')

then 'Q4'

end as "Quarter"

from overview

WHERE first_tx::date>='2023-01-01'

group by 1,3

order by 3