with user_transaction_date as (
select BLOCK_TIMESTAMP::date as day,TX_SIGNER
FROM flipside_prod_db.mdao_near.transactions
where day >= DATEADD(DAY, -90, CURRENT_DATE())
group by day,TX_SIGNER
)
,
user_day_count as (
select TX_SIGNER as user , count(day) as day_count from user_transaction_date
group by TX_SIGNER
)
,
user_datediff as (
SELECT TX_SIGNER as user, DATEDIFF(day, (LAG(day) over (PARTITION BY TX_SIGNER order by day)),day) as date_diff
FROM user_transaction_date
)
,
user_score as (
select user,
sum(1/( IFF(ZEROIFNULL(date_diff)=0,1,ZEROIFNULL(date_diff)))) as User_Citizenship_Score
from user_datediff
group by user
)
,
user_score_day_count as (
select user_score.user , day_count,User_Citizenship_Score
from user_score join user_day_count
on user_score.user =user_day_count.user
)