SELECT

origin_from_address AS "Address",

LISTAGG(VALUE::STRING, '') WITHIN GROUP (ORDER BY SEQ4()) AS "Beraname"

FROM

berachain.testnet.ez_decoded_event_logs,

LATERAL FLATTEN(INPUT => DECODED_LOG:chars)

WHERE

contract_address = '0xbb57539243c4d35265fbf4b2f17d67219200450f'

AND contract_name = 'Beranames'

AND event_name = 'Mint'

AND ORIGIN_FUNCTION_SIGNATURE = '0x3d30c7f6'

GROUP BY

origin_from_address, tx_hash

),

letter_counts AS (

SELECT

"Address",

LENGTH("Beraname") AS "Letters Count"

FROM

datatbl

SELECT

CASE

WHEN "Letters Count" = 1 THEN 'tier1: 1 letter'

WHEN "Letters Count" > 1 AND "Letters Count" <= 5 THEN 'tier2: 2-5 letters'

WHEN "Letters Count" > 5 AND "Letters Count" <= 10 THEN 'tier2: 6-10 letters'

WHEN "Letters Count" > 10 AND "Letters Count" <= 20 THEN 'tier3: 11-20 letters'

WHEN "Letters Count" > 20 AND "Letters Count" <= 50 THEN 'tier4: 21-50 letters'

ELSE 'tier5: more than 50 letters'

END AS "Tier",

COUNT(DISTINCT "Address") AS "Total Addresses"

FROM

letter_counts