Query editor
WITH raw_pools AS (SELECThttp_get('https://api.indexer.omnipair.fi/api/v1/pools?limit=1000&sortBy=tvl&sortOrder=desc') AS response),parsed_data AS (SELECT json_parse(response) AS parsed_jsonFROM raw_pools),pools_extracted AS (SELECTCAST(json_extract(parsed_json, '$.data.pools') AS ARRAY(JSON)) AS pools_arrayFROM parsed_data),unnested AS (SELECT poolFROM pools_extracted,UNNEST(pools_array) AS t(pool)),pool_metadata AS (SELECTjson_extract_scalar(pool, '$.pair_address') AS pool_addressFROM unnested),-- Get all unique wallets that interacted with Omnipair program per dayuser_activity AS (SELECT DISTINCTDATE(ic.block_time) AS day,ic.tx_signer AS walletFROM solana.instruction_calls icWHERE ic.executing_account = 'omnixgS8fnqHfCcTGKWj6JtKjzpJZ1Y5y9pyFkQDkYE'AND ic.tx_success = trueAND ic.block_date >= DATE '2026-02-16'AND ic.tx_signer IS NOT NULL),-- First time each wallet ever appearedfirst_seen AS (SELECTwallet,MIN(day) AS first_dayFROM user_activityGROUP BY wallet),-- New users per daynew_users_per_day AS (SELECTfirst_day AS day,COUNT(DISTINCT wallet) AS new_usersFROM first_seenGROUP BY first_day),-- Retention: check if user returns next day or day 7retention AS (SELECTd1.day AS cohort_day,COUNT(DISTINCT d1.wallet) AS total_users,