SELECT

COALESCE(initcap(labels.address_name), COALESCE(received.Holder, sent.Holder)) AS Holder,

COALESCE(received."TOSHI Received", 0) - COALESCE(sent."TOSHI Sent", 0) AS Balance

FROM (

SELECT

TO_ADDRESS AS Holder,

SUM(AMOUNT) AS "TOSHI Received"

FROM

base.core.ez_token_transfers

WHERE

CONTRACT_ADDRESS = LOWER('0xAC1Bd2486aAf3B5C0fc3Fd868558b082a531B2B4')

GROUP BY

TO_ADDRESS

) AS received

FULL OUTER JOIN (

SELECT

FROM_ADDRESS AS Holder,

SUM(AMOUNT) AS "TOSHI Sent"

FROM

base.core.ez_token_transfers

WHERE

CONTRACT_ADDRESS = LOWER('0xAC1Bd2486aAf3B5C0fc3Fd868558b082a531B2B4')

GROUP BY

FROM_ADDRESS

) AS sent ON received.Holder = sent.Holder

LEFT JOIN

base.core.dim_labels AS labels ON COALESCE(received.Holder, sent.Holder) = labels.address

),

CategorizedHolders AS (

SELECT

CASE

WHEN Balance < 10000 THEN 'Tiny Holder: <10,000 Tokens'

WHEN Balance >= 10000 AND Balance < 100000 THEN 'Small Holder: 10,000 - 100,000 Tokens'

WHEN Balance >= 100000 AND Balance < 1000000 THEN 'Moderate Holder: 100,000 - 1M Tokens'