Query editor
-- Query for 7 specific xStocks daily and cumulative volumes over last 40 daysWITH token_contract_addresses AS (SELECT DISTINCTCAST(address AS VARCHAR) AS address,CAST(pyth_id AS VARCHAR) AS pyth_idFROM query_5381782),-- Get all xStocks with their actual names from the fungible tokens tablexstocks_with_names AS (SELECTtc.address,tc.pyth_id,COALESCE(l.symbol, l.name) AS token_name,l.name AS full_nameFROM token_contract_addresses tcLEFT JOIN tokens_solana.fungible l ON l.token_mint_address = tc.addressWHERE l.name IS NOT NULL),-- Map the 7 specific xStocks we want based on token namesselected_xstocks AS (SELECTaddress,pyth_id,CASEWHEN UPPER(token_name) LIKE '%TESLA%' OR UPPER(full_name) LIKE '%TESLA%' THEN 'TSLAx'WHEN UPPER(token_name) LIKE '%SPY%' OR UPPER(full_name) LIKE '%S&P%' OR UPPER(full_name) LIKE '%SP500%' THEN 'SPYx'WHEN UPPER(token_name) LIKE '%NVIDIA%' OR UPPER(full_name) LIKE '%NVIDIA%' THEN 'NVDAx'WHEN UPPER(token_name) LIKE '%MICROSTRATEGY%' OR UPPER(full_name) LIKE '%MICROSTRATEGY%' THEN 'MSTRx'WHEN UPPER(token_name) LIKE '%CIRCLE%' OR UPPER(full_name) LIKE '%CIRCLE%' THEN 'CRCLx'WHEN UPPER(token_name) LIKE '%GOOGLE%' OR UPPER(full_name) LIKE '%ALPHABET%' OR UPPER(token_name) LIKE '%GOOGL%' THEN 'GOOGLx'WHEN UPPER(token_name) LIKE '%APPLE%' OR UPPER(full_name) LIKE '%APPLE%' THEN 'AAPLx'END AS symbol,token_name,full_nameFROM xstocks_with_namesWHERE (UPPER(token_name) LIKE '%TESLA%' OR UPPER(full_name) LIKE '%TESLA%' ORUPPER(token_name) LIKE '%SPY%' OR UPPER(full_name) LIKE '%S&P%' OR UPPER(full_name) LIKE '%SP500%' ORUPPER(token_name) LIKE '%NVIDIA%' OR UPPER(full_name) LIKE '%NVIDIA%' ORUPPER(token_name) LIKE '%MICROSTRATEGY%' OR UPPER(full_name) LIKE '%MICROSTRATEGY%' ORUPPER(token_name) LIKE '%CIRCLE%' OR UPPER(full_name) LIKE '%CIRCLE%' ORUPPER(token_name) LIKE '%GOOGLE%' OR UPPER(full_name) LIKE '%ALPHABET%' OR UPPER(token_name) LIKE '%GOOGL%' ORUPPER(token_name) LIKE '%APPLE%' OR UPPER(full_name) LIKE '%APPLE%')),relevant_trades AS (SELECTblock_time,CASEWHEN token_bought_mint_address IN (SELECT address FROM selected_xstocks)THEN token_bought_mint_addressELSE token_sold_mint_addressEND AS address,amount_usd AS volume_usdFROM dex_solana.tradesWHERE (token_bought_mint_address IN (SELECT address FROM selected_xstocks)OR token_sold_mint_address IN (SELECT address FROM selected_xstocks))AND block_time >= CURRENT_DATE - INTERVAL '40' DAY -- Filter for last 40 daysAND amount_usd > 0 -- Exclude zero-value trades),