-- Inflow and outflow data

SELECT

DATE(BLOCK_TIMESTAMP) AS date,

'inflow' AS Flow,

TX_TO AS wallet,

AMOUNT / pow(10, decimal) AS amount,

FROM eclipse.core.fact_transfers

WHERE MINT = 'HgD4Dc6qYCj3UanMDiuC4qANheeTsAvk6DY91B3F8gnL'

AND succeeded = 'TRUE'

UNION ALL

SELECT

DATE(BLOCK_TIMESTAMP) AS date,

'outflow' AS Flow,

TX_FROM AS wallet,

AMOUNT / pow(10, decimal) AS amount,

FROM eclipse.core.fact_transfers

WHERE MINT = 'HgD4Dc6qYCj3UanMDiuC4qANheeTsAvk6DY91B3F8gnL'

AND succeeded = 'TRUE'

SELECT

wallet,

ROUND(SUM(CASE WHEN Flow = 'inflow' THEN amount ELSE -amount END)) AS holding_amount

FROM transfers

GROUP BY wallet

ORDER BY holding_amount DESC

limit 100;