select

date_trunc('day', "BLOCK_TIMESTAMP") as time,

count(distinct "TX_HASH") txs,

count(distinct "FROM_ADDRESS") unique_addresses

from gnosis.core.fact_transactions

where "STATUS" = 'SUCCESS'

and "BLOCK_TIMESTAMP" >= DATE_TRUNC('day',CURRENT_DATE()) - interval '90 days'

group by 1

select

time,

txs AS "Transaction",

unique_addresses AS "Unique Addresses",

AVG(unique_addresses) OVER (ORDER BY time ASC ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as "MA(7) unique users",

AVG(txs) OVER (ORDER BY time ASC ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as "MA(7) transaction",

-- tx

100*(txs - lag(txs,1) over (order by time))/ (lag(txs,1) over (order by time)) as pct_tx,

txs - (lag(txs,1) over (order by time)) as tx_change,

-- users

100*(unique_addresses - lag(unique_addresses,1) over (order by time))/ (lag(unique_addresses,1) over (order by time)) as pct_user,

unique_addresses - (lag(unique_addresses,1) over (order by time)) as users_change

from log

order by 1 desc