with y2020 as (with tb1 as (select BLOCK_TIMESTAMP, value as from_address, ROW_NUMBER() OVER (partition by FROM_ADDRESS order by BLOCK_TIMESTAMP) as t_n from flow.core.fact_transactions, table (flatten (input => authorizers)) where BLOCK_TIMESTAMP >= '2020-01-01' and BLOCK_TIMESTAMP < '2021-01-01' and value not in (SELECT DISTINCT ACCOUNT_ADDRESS from flow.core.dim_contract_labels)) , tb2 as (select BLOCK_TIMESTAMP as first_transaction, FROM_ADDRESS from tb1 where t_n=1), tb3 as (select BLOCK_TIMESTAMP as second_transaction, FROM_ADDRESS from tb1 where FROM_ADDRESS in (select FROM_ADDRESS from tb2) and t_n=2) , tb4 as (select tb2.FROM_ADDRESS, avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between from tb2 join tb3 on tb2.FROM_ADDRESS=tb3.FROM_ADDRESS group by 1) select case when time_between<1 then 'Less than a day' when time_between>=1 and time_between<3 then 'Between 1~3 days' when time_between>=3 and time_between<5 then 'Between 3~5 days' when time_between>=5 and time_between<7 then 'Between 3~7 days' when time_between>=7 and time_between<10 then 'Between 7~10 days' when time_between>=10 and time_between<15 then 'Between 10~15 days' when time_between>=15 then 'More than 15 days' end as "Transaction frequency", count(distinct FROM_ADDRESS) as "Users count" from tb4 group by 1), y2021 as (with tb1 as (select BLOCK_TIMESTAMP, value as from_address, ROW_NUMBER() OVER (partition by FROM_ADDRESS order by BLOCK_TIMESTAMP) as t_n from flow.core.fact_transactions, table (flatten (input => authorizers)) where BLOCK_TIMESTAMP >= '2020-01-01' and BLOCK_TIMESTAMP < '2021-01-01' and value not in (SELECT DISTINCT ACCOUNT_ADDRESS from flow.core.dim_contract_labels)) , tb2 as (select BLOCK_TIMESTAMP as first_transaction, FROM_ADDRESS from tb1 where t_n=1), tb3 as (select BLOCK_TIMESTAMP as second_transaction, FROM_ADDRESS from tb1 where FROM_ADDRESS in (select FROM_ADDRESS from tb2) and t_n=2) , tb4 as (select tb2.FROM_ADDRESS, avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between from tb2 join tb3 on tb2.FROM_ADDRESS=tb3.FROM_ADDRESS group by 1) select case when time_between<1 then 'Less than a day' when time_between>=1 and time_between<3 then 'Between 1~3 days' when time_between>=3 and time_between<5 then 'Between 3~5 days' when time_between>=5 and time_between<7 then 'Between 3~7 days' when time_between>=7 and time_between<10 then 'Between 7~10 days' when time_between>=10 and time_between<15 then 'Between 10~15 days' when time_between>=15 then 'More than 15 days' end as "Transaction frequency", count(distinct FROM_ADDRESS) as "Users count" from tb4 group by 1), y2022 as (with tb1 as (select BLOCK_TIMESTAMP, value as from_address, ROW_NUMBER() OVER (partition by FROM_ADDRESS order by BLOCK_TIMESTAMP) as t_n from flow.core.fact_transactions, table (flatten (input => authorizers)) where BLOCK_TIMESTAMP >= '2022-01-01' and BLOCK_TIMESTAMP < '2023-01-01' and value not in (SELECT DISTINCT ACCOUNT_ADDRESS from flow.core.dim_contract_labels)) , tb2 as (select BLOCK_TIMESTAMP as first_transaction, FROM_ADDRESS from tb1 where t_n=1), tb3 as (select BLOCK_TIMESTAMP as second_transaction, FROM_ADDRESS from tb1 where FROM_ADDRESS in (select FROM_ADDRESS from tb2) and t_n=2) , tb4 as (select tb2.FROM_ADDRESS, avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between from tb2 join tb3 on tb2.FROM_ADDRESS=tb3.FROM_ADDRESS group by 1) select case when time_between<1 then 'Less than a day' when time_between>=1 and time_between<3 then 'Between 1~3 days' when time_between>=3 and time_between<5 then 'Between 3~5 days' when time_between>=5 and time_between<7 then 'Between 3~7 days' when time_between>=7 and time_between<10 then 'Between 7~10 days' when time_between>=10 and time_between<15 then 'Between 10~15 days' when time_between>=15 then 'More than 15 days' end as "Transaction frequency", count(distinct FROM_ADDRESS) as "Users count" from tb4 group by 1), y2023 as (with tb1 as (select BLOCK_TIMESTAMP, value as from_address, ROW_NUMBER() OVER (partition by FROM_ADDRESS order by BLOCK_TIMESTAMP) as t_n from flow.core.fact_transactions, table (flatten (input => authorizers)) where BLOCK_TIMESTAMP >= '2023-01-01' and BLOCK_TIMESTAMP < '2024-01-01' and value not in (SELECT DISTINCT ACCOUNT_ADDRESS from flow.core.dim_contract_labels)) , tb2 as (select BLOCK_TIMESTAMP as first_transaction, FROM_ADDRESS from tb1 where t_n=1), tb3 as (select BLOCK_TIMESTAMP as second_transaction, FROM_ADDRESS from tb1 where FROM_ADDRESS in (select FROM_ADDRESS from tb2) and t_n=2) , tb4 as (select tb2.FROM_ADDRESS, avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between from tb2 join tb3 on tb2.FROM_ADDRESS=tb3.FROM_ADDRESS group by 1) select case when time_between<1 then 'Less than a day' when time_between>=1 and time_between<3 then 'Between 1~3 days' when time_between>=3 and time_between<5 then 'Between 3~5 days' when time_between>=5 and time_between<7 then 'Between 3~7 days' when time_between>=7 and time_between<10 then 'Between 7~10 days' when time_between>=10 and time_between<15 then 'Between 10~15 days' when time_between>=15 then 'More than 15 days' end as "Transaction frequency", count(distinct FROM_ADDRESS) as "Users count" from tb4 group by 1) select *, 'A.2020' as year from y2020 union select *, 'B.2021' as year from y2021 union select *, 'C.2022' as year from y2022 union select *, 'D.2023' as year from y2023

Daily Active Users