SELECT

block_timestamp,

trader,

COALESCE(amount_in_usd, amount_out_usd, 0) AS trade_amount

FROM near.defi.ez_dex_swaps

WHERE COALESCE(amount_in_usd, amount_out_usd, 0) BETWEEN 10000 AND 99999

AND DATE_TRUNC('day', block_timestamp) >= '2024-01-01'

SELECT

EXTRACT(HOUR FROM block_timestamp) AS hour_of_day,

DECODE(EXTRACT(DOW FROM block_timestamp),

0, 'Sunday',

1, 'Monday',

2, 'Tuesday',

3, 'Wednesday',

4, 'Thursday',

5, 'Friday',

6, 'Saturday'

) AS day_of_week,

COUNT(*) AS trade_count,

ROUND(SUM(trade_amount),2) AS total_trade_volume,

CASE

WHEN EXTRACT(DOW FROM block_timestamp) = 1 THEN 1

WHEN EXTRACT(DOW FROM block_timestamp) = 2 THEN 2

WHEN EXTRACT(DOW FROM block_timestamp) = 3 THEN 3

WHEN EXTRACT(DOW FROM block_timestamp) = 4 THEN 4

WHEN EXTRACT(DOW FROM block_timestamp) = 5 THEN 5

WHEN EXTRACT(DOW FROM block_timestamp) = 6 THEN 6

WHEN EXTRACT(DOW FROM block_timestamp) = 0 THEN 7

END AS day_order

FROM DOLPHINS

GROUP BY hour_of_day, day_of_week, day_order

ORDER BY day_order, hour_of_day;