-- forked from tokens data final (2) copy copy copy copy copy copy copy copy copy copy @ https://flipsidecrypto.xyz/edit/queries/eb07f915-f603-4421-8d57-1afa739ddd1b
WITH
user_data AS (
SELECT
a.signers[0] AS user,
--COUNT(DISTINCT a.tx_id) AS events,
--SUM(ABS(((post_balances[0] - pre_balances[0]) / POWER(10, 9)) + 0.000345)) AS volume_sol,
MIN(DATE(a.block_timestamp)) AS first_day,
ARRAY_AGG(DISTINCT CASE
WHEN NOT PRE_TOKEN_BALANCES[0]['mint'] LIKE 'So11111111111111111111111111111111111111112' THEN PRE_TOKEN_BALANCES[0]['mint']
WHEN NOT PRE_TOKEN_BALANCES[1]['mint'] LIKE 'So11111111111111111111111111111111111111112' THEN PRE_TOKEN_BALANCES[1]['mint']
WHEN NOT PRE_TOKEN_BALANCES[2]['mint'] LIKE 'So11111111111111111111111111111111111111112' THEN PRE_TOKEN_BALANCES[2]['mint']
WHEN NOT PRE_TOKEN_BALANCES[3]['mint'] LIKE 'So11111111111111111111111111111111111111112' THEN PRE_TOKEN_BALANCES[3]['mint']
END
) AS token_swapped
FROM solana.core.fact_transactions AS a
INNER JOIN solana.core.fact_events AS b
ON a.tx_id = b.tx_id
WHERE
a.block_timestamp BETWEEN '2024-05-01' AND '2024-07-01'
AND (ARRAY_TO_STRING(log_messages, ', ') LIKE '%Program log: Instruction: Sell%' OR ARRAY_TO_STRING(log_messages, ', ') LIKE '%Buy%')
AND a.SUCCEEDED
AND PROGRAM_ID LIKE '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P'
GROUP BY a.signers[0]
),
indexed_data AS (
SELECT
user,
--active_days,
--events,
--volume_sol,