WITH price_data AS (

SELECT

symbol,

price,

DATE_TRUNC('day', hour) AS date

FROM

aptos.price.ez_prices_hourly

WHERE

token_address = '0x1::aptos_coin::AptosCoin'

And hour >= current_date - 30

Qualify row_number () over (partition by date order by date desc) = 1 -- to get only the last price of the day

),

Price_diff as (

Select

Date,

symbol,

Price,

Lag(price) over (order by date) as previous_day_price,

price - previous_day_price as price_diff,

(price - Lag(price) over (order by date)) / lag(price) over(order by date) * 100 as pct_diff --calculating the price change

From price_data

select

date,

symbol,

price,

previous_day_price,

price_diff,

Concat(Round(pct_diff,2), '%') as "daily price % diff"

from price_diff

order by date desc