select

date_trunc('day', hour) as date,

avg(price) as Matic_price

from ethereum.core.fact_hourly_token_prices

where symbol = 'MATIC'

group by 1

),

gas as (

select

BLOCK_TIMESTAMP::date as date,

tx_hash,

TX_FEE * Matic_price as Gas_USD,

Matic_price

from polygon.core.fact_transactions a join price b on a.BLOCK_TIMESTAMP::date = b.date

where 1=1

and STATUS = 'SUCCESS'

and BLOCK_TIMESTAMP >= CURRENT_DATE - 30

and BLOCK_TIMESTAMP < CURRENT_DATE

and TX_FEE is not null

date,

count(distinct tx_hash) as "# TXs",

sum(Gas_USD) as "Gas Amount-USD",

sum("Gas Amount-USD") over(order by date) as "Total Amount USD of Gas",

percentile_cont(0.1) within group(order by Gas_USD) as "%10",

percentile_cont(0.5) within group(order by Gas_USD) as "%50",

percentile_cont(0.9) within group(order by Gas_USD) as "%90",

avg(Gas_USD) as "AVG Gas Amount-USD",

avg(Matic_price) as Matic_price

from gas

group by 1

order by date asc