SELECT hour, price

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xfe0c30065b384f05761f15d0cc899d4f9f9cc0eb'

AND hour = TO_CHAR(DATEADD(hour, -1, current_timestamp), 'YYYY-MM-DD HH24:00:00.000')

),

datas AS (

SELECT

block_timestamp,

tx_hash,

origin_from_address AS user,

symbol,

amount,

amount*price AS amoun_usd

FROM ethereum.core.ez_token_transfers

LEFT JOIN prices

WHERE contract_address = '0x86b5780b606940eb59a062aa85a07959518c0161'

AND origin_to_address = '0xe2acf9f80a2756e51d1e53f9f41583c84279fb1f'

AND block_timestamp::date >= '2024-07-18'

SELECT

(CASE

WHEN amount < 50 THEN 'Less Than 50 ETHFI'

WHEN amount >= 50 AND amount < 100 THEN 'Between 50 and 100 ETHFI'

WHEN amount >= 100 AND amount < 500 THEN 'Between 100 and 500 ETHFI'

WHEN amount >= 500 AND amount < 1000 THEN 'Between 500 and 1000 ETHFI'

WHEN amount >= 1000 AND amount < 5000 THEN 'Between 1000 and 5000 ETHFI'

WHEN amount >= 5000 AND amount < 10000 THEN 'Between 5000 and 10000 ETHFI'

WHEN amount >= 10000 THEN 'More Than 10000 ETHFI'

END) AS category,

count(tx_hash) AS transactions,

count(DISTINCT user) AS users,

sum(amount) AS staked_amount,

sum(amoun_usd) AS staked_amoun_usd

FROM datas