WITH FirstGuildTx AS (

SELECT

DECODED_LOG:id::STRING AS guild_id,

MIN(BLOCK_TIMESTAMP) AS first_tx

FROM ronin.core.ez_decoded_event_logs

WHERE contract_address = '0x7ac8de4b2d37ac6136be285ee7060e8676c9644f'

GROUP BY guild_id

SELECT

DATE_TRUNC('day', first_tx) AS creation_date,

COUNT(*) AS new_guilds

FROM FirstGuildTx

GROUP BY DATE_TRUNC('day', first_tx)

ORDER BY creation_date DESC;