SELECT

date_trunc('{{interval}}', block_timestamp) AS date,

COUNT(DISTINCT sender) AS DAU

FROM axelar.defi.ez_bridge_squid

GROUP BY date

),

new AS (

SELECT

date_trunc('{{interval}}', first_tx) AS date,

COUNT(DISTINCT sender) AS new_user

FROM (

SELECT

sender,

MIN(block_timestamp) AS first_tx

FROM axelar.defi.ez_bridge_squid

GROUP BY sender

)

GROUP BY date

SELECT *,

ROUND(AVG(daily_change_dau) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW), 2) AS "Avg 7 Dau Change",

ROUND(AVG(daily_change_dau) OVER (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW), 2) AS "Avg 30 Dau Change"

FROM (

SELECT

a.date,

Dau,

ROUND(AVG(Dau) OVER (ORDER BY date)) AS "Avg Dau Over Time",

IFF(Dau > LAG(Dau) OVER (ORDER BY date), '🟢', IFF(Dau = LAG(Dau) OVER (ORDER BY date), '⚪', '🔴')) AS " ",

((Dau - LAG(Dau) OVER (ORDER BY date)) / LAG(Dau) OVER (ORDER BY date)) * 100 AS daily_change_dau,

COALESCE(new_user, 0) AS n_new_users,

SUM(n_new_users) OVER (ORDER BY date) AS cum_users,

ROUND(AVG(n_new_users) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)) AS avarage_7_new_user,

ROUND(AVG(n_new_users) OVER (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW)) AS avarage_30_new_user,

Dau - n_new_users AS n_recurring_users,