@MM-Matt

WITH
SUM_BUYS AS (
SELECT
"to" AS address,
SUM(value/1e18) AS amount
FROM erc20."ERC20_evt_Transfer"
WHERE contract_address = '\x6b4c7a5e3f0b99fcd83e9c089bddd6c7fce5c611'
GROUP BY 1),
SUM_SELLS AS (
SELECT
"from" AS address,
SUM(value/1e18) AS amount
FROM erc20."ERC20_evt_Transfer"
WHERE contract_address = '\x6b4c7a5e3f0b99fcd83e9c089bddd6c7fce5c611'
GROUP BY 1)
SELECT
'no_diamond_hands',
COUNT(DISTINCT CASE WHEN ss.amount IS NOT NULL THEN sb.address END)
FROM SUM_BUYS sb
LEFT JOIN SUM_SELLS ss ON(sb.address = ss.address)

UNION

SELECT
'yes_diamond_hands',
COUNT(DISTINCT CASE WHEN ss.amount IS NULL THEN sb.address END)
FROM SUM_BUYS sb
LEFT JOIN SUM_SELLS ss ON(sb.address = ss.address);

@dolphin this is the query behind the diamond hands KPI