CASE

WHEN event_from_label_type = 'operator' THEN 'Operator'

WHEN event_from_label_type = 'foundation' THEN 'Foundation'

WHEN event_from_label_type = 'distributor' THEN 'Exchanges'

WHEN event_from IN(SELECT address FROM redshift.udm_address_tags WHERE blockchain = 'near') AND event_from_label_type IS NULL THEN 'Top Holder'

ELSE 'Smaller Wallets'

END AS from_segment,

event_to,

CASE

WHEN event_to_label_type = 'operator' THEN 'Operator'

WHEN event_to_label_type = 'foundation' THEN 'Foundation'

WHEN event_to_label_type = 'distributor' THEN 'Exchanges'

WHEN event_to IN(SELECT address FROM redshift.udm_address_tags WHERE blockchain = 'near') AND event_to_label_type IS NULL THEN 'Top Holder'

ELSE 'Smaller Wallets'

END AS to_segment,

sum(event_amount) AS volume,

count(distinct tx_id) AS tx_count

FROM gold.near_events

WHERE ((event_to IN(SELECT address FROM redshift.udm_address_tags WHERE blockchain = 'near') AND event_to_label_type IS NULL) OR

(event_from IN(SELECT address FROM redshift.udm_address_tags WHERE blockchain = 'near') AND event_from_label_type IS NULL))

AND date_trunc('day', block_timestamp) >= getdate() - interval '6 months'

AND from_segment != to_segment

AND event_from IS NOT NULL

AND event_to IS NOT NULL

AND event_from != 'failed'

AND event_to != 'failed'

AND (tx_type != 'failed' OR tx_type IS NULL)

AND (event_type NOT IN('deposit', 'deposit_and_stake') OR event_type IS NULL)

GROUP BY 1,

2,

3,

4

ORDER BY 5 DESC

LIMIT 1000