DATE_TRUNC('WEEK', block_timestamp) AS Week,

COUNT(DISTINCT tx_hash) AS Transactions,

COUNT(DISTINCT origin_from_address) AS Active_Servicers,

COUNT(DISTINCT service_id) AS Active_Services,

SUM(COUNT(DISTINCT tx_hash)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Transactions,

SUM(COUNT(DISTINCT origin_from_address)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Active_Services,

SUM(COUNT(DISTINCT service_id)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Unique_Services,

AVG(COUNT(DISTINCT tx_hash)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)

RANGE BETWEEN INTERVAL '28 DAY' PRECEDING AND CURRENT ROW) AS Moving_Avg_Transactions_28d,

AVG(COUNT(DISTINCT origin_from_address)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)

RANGE BETWEEN INTERVAL '28 DAY' PRECEDING AND CURRENT ROW) AS Moving_Avg_Active_Services_28d,

AVG(COUNT(DISTINCT service_id)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)

RANGE BETWEEN INTERVAL '28 DAY' PRECEDING AND CURRENT ROW) AS Moving_Avg_Unique_Services_28d,

COUNT(tx_hash) / NULLIF(COUNT(DISTINCT origin_from_address), 0) AS Avg_Transactions_Per_Service,

COUNT(tx_hash) / NULLIF(COUNT(DISTINCT service_id), 0) AS Avg_Transactions_Per_Service_ID

FROM

crosschain.olas.fact_service_events

GROUP BY

Week

ORDER BY

Week desc