SELECT

swapper,

MIN(block_timestamp) first_date

FROM

solana.core.fact_swaps

GROUP BY

1

),

daily_swap_data AS (

SELECT

swap_program AS label,

block_timestamp::DATE AS date,

COUNT(DISTINCT s.swapper) AS users

FROM

solana.core.fact_swaps s

LEFT JOIN first_swap f ON f.swapper = s.swapper

WHERE

block_timestamp >= '2022-07-01' -- 5 months

AND block_timestamp = first_date

GROUP BY

1, 2

),

cumulative AS (

SELECT

*,

SUM(users) OVER(ORDER BY date) AS cumulative_users,

AVG(users) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS new_users_7D_ma

FROM

daily_swap_data

SELECT

*

FROM

cumulative