WITH Price as (

SELECT

hour :: date as p_date,

symbol,

avg(PRICE) as usd_price

FROM

aptos.price.ez_hourly_token_prices

WHERE

symbol = 'APT'

GROUP BY 1, 2

),

Fee AS (

SELECT

date_trunc('day', block_timestamp) AS Date,

label_type,

INITCAP(Label) as DEXs,

avg(EVENT_DATA:amount/pow(10,DECIMALS)) as amount,

FROM

aptos.core.fact_events a

JOIN aptos.core.dim_labels b on address = event_address

JOIN aptos.core.dim_tokens c on a.event_address = c.token_address

WHERE

b.label_type = 'dex'

--AND SUCCESS = true

AND tx_type = 'user_transaction'

AND date > current_date - 30

Date,

DEXs,

SUM(amount *usd_price) as USD_Volume