kasadeghThe average number of days in which each user type had a transaction
    Updated 2022-07-06
    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
    )
    Run a query to Download Data