UTCを日本時間考慮したら4/2の分はこんな感じ？
SELECT event_pubkey, COUNT(*) AS event_count FROM events WHERE `event_created_at` >= "2023-04-01 15:00" and `event_created_at` < "2023-04-02 15:00" AND event_kind=1 GROUP BY event_pubkey ORDER BY event_count DESC;
Discussion
もし PostgreSQL なら event_create_at の部分は to_timestamp(event_created_at) が良さそう。
SELECT encode(event_pubkey::bytea, 'hex'), COUNT(*) AS event_count FROM events WHERE to_timestamp(event_created_at) >= TO_TIMESTAMP('2023-04-01 15:00:00', 'YYYY/MM/DD HH24:MI:SS') and to_timestamp(event_created_at) < TO_TIMESTAMP('2023-04-02 15:00:00', 'YYYY/MM/DD HH24:MI:SS') AND event_kind=1 GROUP BY event_pubkey ORDER BY event_count DESC;
で抽出できそうです。
有難うございます!