with all_users as (
SELECT
(BLOCK_TIMESTAMP),
(TO_ADDRESS),
row_number() over (partition by TO_ADDRESS order by BLOCK_TIMESTAMP asc) as rank
FROM
flipside_prod_db.thorchain.transfers
),
just_rank_1_users as (
SELECT
BLOCK_TIMESTAMP,
(TO_ADDRESS)
FROM
all_users
where rank = 1
)
SELECT
DATE_TRUNC ('year' ,BLOCK_TIMESTAMP ) as daily_new_users,
COUNT(TO_ADDRESS)
from
just_rank_1_users
group by daily_new_users