prices AS (

SELECT

HOUR,

avg(PRICE) as price

FROM

aptos.price.ez_hourly_token_prices

WHERE

SYMBOL IN ('APT')

AND HOUR >= '2024-01-01'

GROUP BY

1

),

staking AS (

SELECT

-- Truncate the block timestamp to get the day

TRUNC(block_timestamp, 'day') as day,

-- Determine the provider based on the payload function

CASE

WHEN payload_function = '0x17f1e926a81639e9557f4e4934df93452945ec30bc962e11351db59eb0d78c33::thala_lsd::stake' THEN 'Thala'

ELSE 'Amnis'

END AS provider,

-- Count the number of distinct staking transactions

COUNT(DISTINCT tx_hash) as deposits,

-- Calculate the total number of deposits over time

SUM(COUNT(DISTINCT tx_hash)) OVER (

ORDER BY

day

) as total_deposits,

-- Calculate the total amount of APT staked

SUM(payload:arguments [0] / POW(10, 8)) as apt_amount,

-- Calculate the total APT volume over time

SUM(SUM(payload:arguments [0] / POW(10, 8))) OVER (

ORDER BY