SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS "Date",

SUM(GAS_USED)/POWER(10, 8) AS "Paid Gas Fees"

FROM

aptos.core.fact_transactions

WHERE "Date" >= '2022-10-20'

GROUP BY "Date"

), average_fees AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS "Date",

AVG(GAS_USED)/POWER(10, 8) AS "Weekly Average Gas Fees"

FROM

aptos.core.fact_transactions

WHERE "Date" >= '2022-10-20'

GROUP BY "Date"

), total_weekly_transactions AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS "Date",

COUNT(*) AS "Transactions Count"

FROM

aptos.core.fact_transactions

WHERE "Date" >= '2022-10-20'

GROUP BY "Date"

), weekly_greedy_transactions AS (

SELECT

DATE_TRUNC('week', ft.BLOCK_TIMESTAMP) AS "Date",

COUNT(CASE WHEN ft.GAS_USED / POWER(10, 8) >= af."Weekly Average Gas Fees" THEN 1 END) AS "Greedy Transactions"

FROM

aptos.core.fact_transactions ft

JOIN

average_fees af ON af."Date" = DATE_TRUNC('week', ft.BLOCK_TIMESTAMP)

WHERE ft.BLOCK_TIMESTAMP >= '2022-10-20'

GROUP BY DATE_TRUNC('week', ft.BLOCK_TIMESTAMP)