--vote with ref

SELECT block_timestamp,

tx_hash,

signer_id AS User,

'xRef' AS Stake_type,

args:amount / 1e18 AS adjusted_amount,

CASE

WHEN receiver_id = 'memefarm-xref-lonk.ref-labs.near' THEN 'lonk'

WHEN receiver_id = 'memefarm-xref-usm.ref-labs.near' THEN 'usmemetg'

WHEN receiver_id = 'memefarm-xref-blackdragon.ref-labs.near' THEN 'blackdragon'

WHEN receiver_id = 'memefarm-xref-gear.ref-labs.near' THEN 'Gear'

WHEN receiver_id = 'memefarm-xref-uwon.ref-labs.near' THEN 'Uwon'

WHEN receiver_id = 'memefarm-xref-kat.ref-labs.near' THEN 'Kat'

WHEN receiver_id = 'memefarm-xref-intel.ref-labs.near' THEN 'INTEAR'

WHEN receiver_id = 'memefarm-xref-chill.ref-labs.near' THEN 'Chill'

WHEN receiver_id = 'memefarm-xref-shitzu.ref-labs.near' THEN 'shitzu'

WHEN receiver_id = 'memefarm-xref-bd.ref-labs.near' THEN 'Bendog'

END AS token_name

FROM near.core.fact_actions_events_function_call

WHERE predecessor_id = 'xtoken.ref-finance.near'

AND receiver_id LIKE '%memefarm-xref%'

AND block_timestamp >= '2024-10-01'

AND method_name = 'ft_on_transfer'

AND receipt_succeeded = 1

),

user_distribution AS (

SELECT User,

SUM(adjusted_amount) AS total_amount,

CASE

WHEN SUM(adjusted_amount) < 50 THEN '<50 xRef'

WHEN SUM(adjusted_amount) BETWEEN 50 AND 100 THEN '50-100 xRef'

WHEN SUM(adjusted_amount) BETWEEN 101 AND 200 THEN '101-200 xRef'

WHEN SUM(adjusted_amount) BETWEEN 201 AND 500 THEN '201-500 xRef'

ELSE '>500 xRef'

END AS amount_range