DATE_TRUNC('WEEK', block_timestamp) AS Week,

COUNT(DISTINCT request_id) AS Total_Requests,

COUNT(DISTINCT sender_address) AS Unique_Requestors,

COUNT(DISTINCT contract_address) AS Unique_Contracts,

COUNT(DISTINCT event_name) AS Unique_Events,

SUM(COUNT(DISTINCT request_id)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Total_Requests,

SUM(COUNT(DISTINCT sender_address)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Unique_Requestors,

SUM(COUNT(DISTINCT contract_address)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Unique_Contracts,

SUM(COUNT(DISTINCT event_name)) OVER (ORDER BY DATE_TRUNC('WEEK', block_timestamp)) AS Cumulative_Unique_Events,

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

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

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

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

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

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

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

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

COUNT(tx_hash) / NULLIF(COUNT(DISTINCT sender_address), 0) AS Avg_Transactions_Per_Requestor,

COUNT(request_id) / COUNT(DISTINCT TO_DATE(block_timestamp)) AS Avg_Requests_Per_Day

FROM

crosschain.olas.fact_mech_activity

GROUP BY

Week

ORDER BY

Week desc