WITH contract_info AS ( SELECT * FROM ( VALUES ('DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263', ' BONK', '@bonk_inu', '

BONK ') , ('ukHH6c7mMyiWCf1b9pnWe25TSpkDDt3H5pQZgZ74J82', ' BOME', '@Bomebookofmemes', '

BOME ') , ('EKpQGSJtjMFqKZ9KQanSqYXRcF8fBopzLHYxdM65zcjm', ' WIF', '@dogwifcoin', '

WIF ') , ('7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr', ' POPCAT', '@POPCATSOLANA', '

POPCAT ') , ('2qEHjDLDLbuBgRYvsxhc5D6uDWAivNFZGan56P1tpump', ' PNUT', '@pnutsolana', '

PNUT ') , ('CzLSujWBLFsSjncfkh59rUFqvafWcY5tzedWJSuypump', ' GOAT', '@truth_terminal', '

GOAT ') , ('MEW1gQWJ3nEXg2qgERiKu7FAFj79PHvQVREQUzScPP5', ' MEW', '@MewsWorld', '

MEW ') , ('GJAFwWjJ3vnTsrQVabjBVK2TYB1YtRCQXRDfDgUnpump', ' ACT', '@ACTICOMMUNITY', '

ACT ') , ('A8C3xuqscfmyLrte3VmTqrAq8kgMASius9AFNANwpump', ' FWOG', '@itsafwog', '

FWOG ') , ('79F32BvHBE49gPsvypYTGzcpWGvt66mgvenQow3mJjXu', ' CORGIAI', '@corgiaiclub', '

CORGIAI ') , ('ED5nyyWEzpPPiWimP8vYm7sD7TD3LAt3Q3gRTWHzPJBY', ' MOODENG', '@MooDengSOL', '

MOODENG ') , ('5z3EqYQo9HiCEs3R84RCDMu2n7anpDMxRhdK8PSWmrRC', ' PONKE', '@ponkesol', '

PONKE ') , ('63LfDmNb3MQ8mw9MtZ2To9bEA2M71kZUUGq5tiJxcqj9', ' GIGA', '@GIGACHAD_meme', '

GIGA ') , ('Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump', ' CHILLGUY', '@chillguycto', '

CHILLGUY ') , ('8x5VqbHA8D7NkD52uNuS5nnt3PwA8pLD34ymskeSo2Wn', ' ZEREBRO', '@0xzerebro', '

ZEREBRO ') , ('A8C3xuqscfmyLrte3VmTqrAq8kgMASius9AFNANwpump', ' FWOG', '@itsafwog', '

FWOG ') , ('A8C3xuqscfmyLrte3VmTqrAq8kgMASius9AFNANwpump', ' FWOG', '@itsafwog', '

FWOG ') ) AS t (contract_address, token_name, twitter,image) ), prices AS ( SELECT c.contract_address AS token_address, p.price AS price FROM contract_info c JOIN solana.price.ez_prices_hourly p ON c.contract_address = p.token_address WHERE p.hour = (SELECT MAX(hour) FROM solana.price.ez_prices_hourly p2 WHERE p2.token_address = p.token_address) GROUP BY c.contract_address, p.price ORDER BY c.contract_address; ), min_max as ( select distinct c.contract_address AS contract_address, c.token_name , min(p.price) as min_price , max(p.price) as max_price FROM contract_info c JOIN solana.price.ez_prices_hourly p ON c.contract_address = p.token_address group by 1 ,2 ), price_aggregates AS ( SELECT DISTINCT c.contract_address AS contract_address, DATE_TRUNC('day', p.hour) AS date, AVG(p.price) AS avg_daily_price FROM contract_info c JOIN solana.price.ez_prices_hourly p ON c.contract_address = p.token_address GROUP BY 1, 2 ), latest_prices AS ( SELECT date, contract_address, avg_daily_price, LEAD(avg_daily_price) OVER (PARTITION BY contract_address ORDER BY date DESC) AS prev_day_price, LEAD(avg_daily_price, 7) OVER (PARTITION BY contract_address ORDER BY date DESC) AS prev_7_day_price, LEAD(avg_daily_price, 30) OVER (PARTITION BY contract_address ORDER BY date DESC) AS prev_30_day_price FROM price_aggregates ), current_price_info AS ( SELECT TO_CHAR(date, 'YYYY-MM-DD') AS date, contract_address, ROUND(avg_daily_price, 9) AS current_price, ROUND((avg_daily_price - prev_day_price) / prev_day_price * 100, 2) AS change_from_previous_day, ROUND((avg_daily_price - prev_7_day_price) / prev_7_day_price * 100, 2) AS change_from_previous_7_days, ROUND((avg_daily_price - prev_30_day_price) / prev_30_day_price * 100, 2) AS change_from_previous_30_days FROM latest_prices WHERE date = (SELECT MAX(date) FROM latest_prices) ), latest_balances AS ( SELECT bl.owner, bl.mint, bl.balance, row_number() over(partition by owner, mint order by block_timestamp desc) as rank FROM solana.core.fact_token_balances bl JOIN contract_info ci ON bl.mint = ci.contract_address ), balances AS ( SELECT lb.owner AS address, lb.mint AS token_address, lb.balance AS "Token Balance" FROM latest_balances lb WHERE lb.rank = 1 AND balance > 0 ), total_supply AS ( SELECT COUNT(DISTINCT address) AS address, token_address, SUM("Token Balance") AS "Total Supply" FROM balances GROUP BY token_address ), total_burned AS ( SELECT c.contract_address AS token_address, SUM(BURN_AMOUNT/ pow(10,decimal)) AS "Total Burned" FROM contract_info c JOIN solana.defi.fact_token_burn_actions b ON c.contract_address = b.mint WHERE b.event_type = 'burn' AND b.succeeded = 'true' GROUP BY token_address ORDER BY "Total Burned" desc ), mc as ( SELECT ci.token_name as "Meme Coin" , ci.twitter as "Twitter" , ci.image as "Image" , ci.contract_address as "Contract" , address::int as "Total Holders" , round(p.price * ts."Total Supply")::int AS "Fully diluted market cap" , round(p.price,10) as "Price" , round(max_price,9) as max_price , round(min_price,9) as min_price , round(( "Price" - max_price) / max_price * 100) AS percent_from_max , round(( "Price" - min_price) / min_price * 100) AS percent_from_min , round(ts."Total Supply")::int as "Total Supply" , change_from_previous_day , change_from_previous_7_days , change_from_previous_30_days , COALESCE("Total Burned",0) as "Total Burned" , round( (COALESCE("Total Burned",0) * p.price),1) as "Burned market cap" , ("Fully diluted market cap" - "Burned market cap") as "Market cap" , round("Market cap"/"Total Holders")::int AS "Avg Holding (USD)" FROM prices p left JOIN total_supply ts ON p.token_address = ts.token_address left JOIN contract_info ci ON p.token_address = ci.contract_address left JOIN min_max ON p.token_address = min_max.contract_address left JOIN current_price_info ON p.token_address = current_price_info.contract_address left JOIN total_burned bs ON p.token_address = bs.token_address ) SELECT "Meme Coin", CASE WHEN "Total Holders" >= 1000000000000 THEN ROUND(TO_VARIANT("Total Holders" / 1000000000000),4) || ' T' WHEN "Total Holders" >= 1000000000 THEN ROUND(TO_VARIANT("Total Holders" / 1000000000),2) || ' B' WHEN "Total Holders" >= 1000000 THEN ROUND(TO_VARIANT("Total Holders" / 1000000),2) || ' M' WHEN "Total Holders" >= 1000 THEN ROUND(TO_VARIANT("Total Holders" / 1000),1) || ' K' ELSE TO_VARIANT("Total Holders") END AS "Total Holder", CASE WHEN "Fully diluted market cap" >= 1000000000000 THEN ROUND(TO_VARIANT("Fully diluted market cap" / 1000000000000),4) || ' T' WHEN "Fully diluted market cap" >= 1000000000 THEN ROUND(TO_VARIANT("Fully diluted market cap" / 1000000000),2) || ' B' WHEN "Fully diluted market cap" >= 1000000 THEN ROUND(TO_VARIANT("Fully diluted market cap" / 1000000),2) || ' M' WHEN "Fully diluted market cap" >= 1000 THEN ROUND(TO_VARIANT("Fully diluted market cap" / 1000),1) || ' K' ELSE TO_VARIANT("Fully diluted market cap") END AS "Fully diluted MarketCap", "Fully diluted market cap", CASE WHEN "Market cap" >= 1000000000000 THEN ROUND(TO_VARIANT("Market cap" / 1000000000000),4) || ' T' WHEN "Market cap" >= 1000000000 THEN ROUND(TO_VARIANT("Market cap" / 1000000000),2) || ' B' WHEN "Market cap" >= 1000000 THEN ROUND(TO_VARIANT("Market cap" / 1000000),2) || ' M' WHEN "Market cap" >= 1000 THEN ROUND(TO_VARIANT("Market cap" / 1000),1) || ' K' ELSE TO_VARIANT("Market cap") END AS "MarketCap", "Market cap", CASE WHEN "Total Burned" >= 1000000000000 THEN ROUND(TO_VARIANT("Total Burned" / 1000000000000),4) || ' T' WHEN "Total Burned" >= 1000000000 THEN ROUND(TO_VARIANT("Total Burned" / 1000000000),2) || ' B' WHEN "Total Burned" >= 1000000 THEN ROUND(TO_VARIANT("Total Burned" / 1000000),2) || ' M' WHEN "Total Burned" >= 1000 THEN ROUND(TO_VARIANT("Total Burned" / 1000),1) || ' K' ELSE TO_VARIANT("Total Burned") END AS "Burned Supply", "Total Burned", CASE WHEN "Burned market cap" >= 1000000000000 THEN ROUND(TO_VARIANT("Burned market cap" / 1000000000000),4) || ' T' WHEN "Burned market cap" >= 1000000000 THEN ROUND(TO_VARIANT("Burned market cap" / 1000000000),2) || ' B' WHEN "Burned market cap" >= 1000000 THEN ROUND(TO_VARIANT("Burned market cap" / 1000000),2) || ' M' WHEN "Burned market cap" >= 1000 THEN ROUND(TO_VARIANT("Burned market cap" / 1000),1) || ' K' ELSE TO_VARIANT("Burned market cap") END AS "Burned MarketCap", "Burned market cap", round(("Total Supply" - "Total Burned") /"Total Supply"*100 ) as "Circulating Supply (%)", ---------------------------------- "Price", min_price as "ATL", max_price AS "ATH", percent_from_max AS "percentage from ATH", percent_from_min AS "percentage from ATL", CASE WHEN "Total Supply" >= 1000000000000 THEN ROUND(TO_VARIANT("Total Supply" / 1000000000000),4) || ' T' WHEN "Total Supply" >= 1000000000 THEN ROUND(TO_VARIANT("Total Supply" / 1000000000),2) || ' B' WHEN "Total Supply" >= 1000000 THEN ROUND(TO_VARIANT("Total Supply" / 1000000),2) || ' M' WHEN "Total Supply" >= 1000 THEN ROUND(TO_VARIANT("Total Supply" / 1000),1) || ' K' ELSE TO_VARIANT("Total Supply") END AS "Total Supply.", "Avg Holding (USD)", "Contract" , change_from_previous_day as "Price (24h)" , change_from_previous_7_days as "Price (7d)" , change_from_previous_30_days as "Price (30d)" , "Twitter", "Image" ---------------------------------- FROM mc ORDER BY "Market cap" DESC

Query	Creator	Created
shrill-lime copy solana	i_dan	Nov 20, 2024
shrill-lime solana token	i_dan	Nov 20, 2024
Solana Memes Main solana token	i_dan	Nov 20, 2024

i_dan