SELECT TX_FROM AS "User", MIN(BLOCK_TIMESTAMP::DATE) "First Activity"

FROM osmosis.core.fact_transactions

GROUP BY "User"

),

Activity AS (

SELECT BLOCK_TIMESTAMP::DATE AS DAYS, TX_FROM AS "User"

FROM osmosis.core.fact_transactions

WHERE BLOCK_TIMESTAMP::DATE < '2022-10-19'

GROUP BY DAYS, "User"

),

Daily_Activity AS (

SELECT DAYS, First_Activity."User", "First Activity", 1 AS "Temp"

FROM First_Activity JOIN Activity USING("User")

),

Daily_Activity_Details AS (

SELECT DAYS, "User", "First Activity", DATEDIFF(DAY, "First Activity", DAYS) +1 AS "Account Current Age",

SUM("Temp") OVER (Partition By "User" ORDER by DAYS ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) "Current Active Days",

"Current Active Days"/"Account Current Age" AS "Current Threshold"

FROM Daily_Activity

ORDER BY DAYS ASC

),

REMOVE_NEW_USERS_ACTIVITIES AS (

SELECT DAYS, "User", "First Activity", "Account Current Age", "Current Active Days", "Current Threshold"

FROM Daily_Activity_Details

WHERE "Current Active Days" > 4

),

Check_Last_Activity AS (

SELECT DAYS, "User", "First Activity", "Account Current Age", "Current Active Days", "Current Threshold",

"Account Current Age" - LAG("Account Current Age", 1, 0) OVER (PARTITION BY "User" ORDER BY DAYS) AS "Diffrence Between Current Activity and Last Activty"

FROM REMOVE_NEW_USERS_ACTIVITIES