(

select

block_id as block_id,

date_trunc('hour',block_timestamp) as time,

tx_id as tx_hash,

tx_from as sender,

tx_to as receiver,

'USDC' as currency,

amount as usd_amount,

case

when usd_amount < 1 and usd_amount > 0 then 'shrimp (0-1)'

when usd_amount < 10 and usd_amount >= 1 then 'crab (1-10)'

when usd_amount < 50 and usd_amount >= 10 then 'Octpus (10-50)'

when usd_amount < 100 and usd_amount >= 50 then 'Fish (50-100)'

when usd_amount < 500 and usd_amount >= 100 then 'Dolphins (100-500)'

when usd_amount < 1000 and usd_amount >= 500 then ' Shark (500-1000)'

when usd_amount < 5000 and usd_amount >= 1000 then ' Whale (1000-5000)'

when usd_amount >= 5000 then 'Humpback whale (5000+)'

else 'holder'

end as tx_type

from solana.core.fact_transfers

where 1=1

and mint=('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v')

and time between '{{start_date}}' and '{{end_date}}'

),

tab as

(

select * from sol_usdc

-- limit 1

), days as

(select t.*,dd.DAY_OF_WEEK_NAME as day,date_part('hour',t.time) as hour from tab t inner join ethereum.core.dim_dates dd on t.time::date=dd.date_day)

select day,hour,avg(usd_amount) as usd_amount from days

group by 1,2