うちのDB構造だとこんな感じで最新のkind 0のcontentからnameを取り出すようにした。

SELECT

e.pubkey,

COUNT(*) AS event_count,

CASE

WHEN COALESCE((SELECT content FROM events WHERE pubkey = e.pubkey AND kind = 0 ORDER BY event_created_at DESC LIMIT 1), '') = ''

THEN ''

WHEN JSON_VALID((SELECT content FROM events WHERE pubkey = e.pubkey AND kind = 0 ORDER BY event_created_at DESC LIMIT 1))

THEN JSON_EXTRACT((SELECT content FROM events WHERE pubkey = e.pubkey AND kind = 0 ORDER BY event_created_at DESC LIMIT 1), '$.name')

ELSE ''

END AS latest_name

FROM

events AS e

WHERE

e.event_created_at >= "2023-04-01 15:00" AND e.event_created_at < "2023-04-02 15:00"

AND e.kind = 1

GROUP BY

e.pubkey

ORDER BY

event_count DESC;