WITH

tx AS (

SELECT

block_timestamp,

tx_id,

tx_from,

SPLIT_PART(fee, 'u', 1) AS fee,

tx_succeeded

FROM

sei.core.fact_transactions

where block_timestamp>'2023-08-15'

DATE_TRUNC('week', block_timestamp) AS "Week",

COUNT(DISTINCT tx_id) AS "Transactions",

SUM(COUNT(DISTINCT tx_id)) OVER (ORDER BY "Week") AS "Total Transactions",

COUNT(DISTINCT TX_FROM) as "Active users",

AVG(fee) / POW(10, 6) AS "Average Transaction Fee",

SUM(fee) / POW(10, 6) AS "Total Fee",

COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN tx_id END) AS "Successful Transactions",

(COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN tx_id END) - LAG(COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN tx_id END)) OVER (ORDER BY "Week")) AS "Successful Transactions % Difference",

(COUNT(DISTINCT tx_id) - LAG(COUNT(DISTINCT tx_id)) OVER (ORDER BY "Week")) / LAG(COUNT(DISTINCT tx_id)) OVER (ORDER BY "Week")*100 AS "Transactions % Difference",

(COUNT(DISTINCT tx_from) - LAG(COUNT(DISTINCT tx_from)) OVER (ORDER BY "Week")) / LAG(COUNT(DISTINCT tx_from)) OVER (ORDER BY "Week")*100 AS "Users % Difference",

(SUM(fee) - LAG(SUM(fee)) OVER (ORDER BY "Week")) / LAG(SUM(fee)) OVER (ORDER BY "Week") * 100 AS "Fee % Difference"

tx --where "Week"

GROUP BY

"Week"

ORDER BY

"Week" desc