-- forked from Rothschild Investigation Part 2 FRED and PNUT @ https://flipsidecrypto.xyz/studio/queries/718cde45-657d-45f9-b83d-d56c2908a515
WITH FILTERED_PNUT_traders AS(
SELECT
Owner,
MAX(BALANCE) AS top_balance,
date_trunc('day', BLOCK_TIMESTAMP)
FROM solana.core.fact_token_balances
WHERE BLOCK_TIMESTAMP BETWEEN '{{start_date}}' AND '{{end_date}}'
AND MINT='{{CA}}'
AND OWNER NOT IN('5Q544fKrFoe6tsEbD7S8EmxGTJYAKtTVhAW5Q5pge4j1')
GROUP BY 1,3
HAVING MAX(Balance)>4000000
ORDER BY top_balance DESC
),
FILTERED_FRED_traders AS (
SELECT
Owner,
MAX(BALANCE) AS top_balance,
date_trunc('day', BLOCK_TIMESTAMP)
FROM solana.core.fact_token_balances
WHERE BLOCK_TIMESTAMP BETWEEN '{{start_date}}' AND '{{end_date}}'
AND MINT='{{CA2}}'
AND OWNER NOT IN('5Q544fKrFoe6tsEbD7S8EmxGTJYAKtTVhAW5Q5pge4j1')
GROUP BY 1,3
HAVING MAX(Balance)>14500000
ORDER BY top_balance DESC
)
SELECT
COUNT(DISTINCT Owner) AS Number_of_Matches
FROM FILTERED_PNUT_traders