TX_SIGNER,

min(date_trunc('hour',BLOCK_TIMESTAMP)) as mindate

FROM near.core.fact_transactions

GROUP BY 1 ORDER by 2

case

when dayname(mindate) = 'Mon' then '1. Mon'

when dayname(mindate) = 'Tue' then '2. Tue'

when dayname(mindate) = 'Wed' then '3. Wed'

when dayname(mindate) = 'Thu' then '4. Thu'

when dayname(mindate) = 'Fri' then '5. Fri'

when dayname(mindate) = 'Sat' then '6. Sat'

when dayname(mindate) = 'Sun' then '7. Sun' end as weekday,

to_varchar(mindate::datetime,'hh:mi') as hour,

COUNT(TX_SIGNER) as wallets_count

joindate

WHERE mindate >= '2023-01-01' and mindate <= '2023-01-14'

GROUP BY 1,2 ORDER BY 1 desc, 2 asc