Query editor
WITH user_activity AS (SELECT tx_from AS "user",DATE_TRUNC('week', MIN(block_time)) AS first_week_active,DATE_TRUNC('week', MAX(block_time)) AS last_week_activeFROM dex.tradesWHERE 1=1AND blockchain = 'hyperevm'AND project = 'prjx'GROUP BY 1)SELECT first_week_active AS cohort_week,COUNT(DISTINCT "user") AS new_users,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w0_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '7' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w1_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '14' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w2_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '21' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w3_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '28' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w4_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '35' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w5_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '42' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w6_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '49' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w7_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '56' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w8_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '63' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w9_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '70' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w10_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '77' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w11_retention,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week_active + INTERVAL '84' DAY THEN "user" END) AS DOUBLE) / COUNT(DISTINCT "user") AS w12_retention,ROUND(AVG(DATE_DIFF('week', first_week_active, last_week_active)), 2) AS avg_weeks_active,SUM (COUNT(DISTINCT "user")) OVER (ORDER BY first_week_active) AS total_usersFROM user_activityGROUP BY 1ORDER BY 1 ASC