WITH contract_info AS ( SELECT * FROM ( VALUES -- HIGH MC ('0x532f27101965dd16442e59d40670faf5ebb142e4', ' BRETT', 'BasedBrett' ,'

BRETT ' ), ('0x4ed4e862860bed51a9570b96d89af5e1b0efefed', ' DEGEN', 'degentokenbase','

DEGEN ' ), ('0x4da78059d97f155e18b37765e2e042270f4e0fc4', ' WUF' , 'WUFFI_Inu','

WUF '), ('0xac1bd2486aaf3b5c0fc3fd868558b082a531b2b4', ' TOSHI' , 'Toshi_base','

TOSHI '), ('0x5d559ea7bb2dae4b694a079cb8328a2145fd32f6', ' OWO' , 'SoMon_OwO','

OWO '), ('0xb1a03eda10342529bbf8eb700a06c60441fef25d', ' MIGGLES', 'MrMigglesOnBase','

MIGGLES ' ), ('0xBC45647eA894030a4E9801Ec03479739FA2485F0', ' BENJI' , 'Basenjiofficial','

BENJI '), ('0x689644b86075ed61c647596862c7403e1c474dbf', ' BAMBOO', 'bamboobase','

BAMBOO ' ), ('0x2598c30330D5771AE9F983979209486aE26dE875', ' AI', 'AnyInuCoin','

AI ' ), ('0x6b9bb36519538e0C073894E964E90172E1c0B41F', ' WEWE', 'weweonbase','

WEWE ' ), ('0xE3086852A4B125803C815a158249ae468A3254Ca', ' MFER', 'basedmfercoin' ,'

MFER '), ('0x9a26F5433671751C3276a065f57e5a02D2817973', ' KEYCAT', 'KeyboardCatBase' ,'

KEYCAT '), ('0xF6e932Ca12afa26665dC4dDE7e27be02A7c02e50', ' MOCHI', 'mochi_token','

MOCHI ' ), ('0xebfF2db643Cf955247339c8c6bCD8406308ca437', ' CHOMP', 'Chompcoin','

CHOMP ' ), ('0x18a8bd1fe17a1bb9ffb39ecd83e9489cfd17a022', ' ANDY' , '_AndyOnBase','

ANDY '), ('0x2da56acb9ea78330f947bd57c54119debda7af71', ' Mog', 'MogCoinEth' ,'

Mog '), ('0x6921B130D297cc43754afba22e5EAc0FBf8Db75b', ' DOGINME' , 'doginmeonbase','

DOGINME '), ('0x47b464eDB8Dc9BC67b5CD4C9310BB87b773845bD', ' NORMIE', 'NormieBase','

NORMIE ' ), ('0xb8D98a102b0079B69FFbc760C8d857A31653e56e', ' TOBY' , 'toadgod1017','

TOBY '), ('0x6d3B8C76c5396642960243Febf736C6BE8b60562', ' SKOP', 'TrystanNFT','

SKOP ' ), ('0xb166e8b140d35d9d8226e40c09f757bac5a4d87d', ' NPC', 'NonPlayableCoin' ,'

NPC ') , -- MEDIUM MC ('0x8853F0c059C27527d33D02378E5E4F6d5afB574a', ' AIINU' ,'Aiinuclub','

AIINU '), ('0x15aC90165f8B45A80534228BdCB124A011F62Fee', ' MEOW','BaseMeow','

MEOW ' ), ('0x0d97F261b1e88845184f678e2d1e7a98D9FD38dE', ' TYBG' ,'tybasegod','

TYBG '), ('0x4dd9077269dd08899f2a9e73507125962b5bc87f', ' CRASH','BasedCrashX','

CRASH ' ), ('0x6Bc40d4099f9057b23aF309C08d935b890d7Adc0', ' SNAIL','SnailBrookAI','

SNAIL ' ), ('0xcdE172dc5ffC46D228838446c57C1227e0B82049', ' BOOMER','BoomerOnBase','

BOOMER ' ), ('0x0578d8a44db98b23bf096a382e016e29a5ce0ffe', ' HIGHER','higheronchain' ,'

HIGHER '), ('0x70737489DFDf1A29b7584d40500d3561bD4Fe196', ' BORED' ,'BoredNBased','

BORED '), ('0x698DC45e4F10966f6D1D98e3bFd7071d8144C233', ' PEPE','PepeOnBase0x69','

PEPE ' ), ('0x55027a5b06f4340cC4C82DCC74C90cA93dcb173E', ' TADPOLE' ,'tadpolebase','

TADPOLE '), ('0x1b6A569DD61EdCe3C383f6D565e2f79Ec3a12980', ' PEEZY','YoungPeezy_Pepe','

PEEZY ' ), ('0x9A86980D3625b4A6E69D8a4606D51cbc019e2002', ' FOMO' ,'FOMO_BASE','

FOMO '), ('0xeD899bfDB28c8ad65307Fa40f4acAB113AE2E14c', ' ROOST','RoostCoin' ,'

ROOST '), ('0x3c281a39944a2319aa653d81cfd93ca10983d234', ' BUILD','' ,'

BUILD '), ('0x57f5fbd3de65dfc0bd3630f732969e5fb97e6d37', ' TRUMP','MAGAMemecoin' ,'

TRUMP '), ('0x88Faea256f789F8dD50dE54F9C807eEf24f71b16', ' WOLF' ,'LandWolf_Base','

WOLF '), ('0x51B75da3Da2e413eA1B8eD3Eb078dc712304761C', ' MOBY','BasedMoby','

MOBY ' ), ('0x38d513Ec43ddA20f323f26c7bef74c5cF80b6477', ' CARLO' ,'Carlo_TheDog','

CARLO '), ('0x13741C5dF9aB03E7Aa9Fb3Bf1f714551dD5A5F8a', ' NOGS','nogglescoin' ,'

NOGS '), ('0x42069de48741db40aef864f8764432bbccbd0b69', ' BETS','BasedStBets','

BETS ' ), ('0x85e90a5430af45776548adb82ee4cd9e33b08077', ' DINO','DinoBase','

DINO ' ), -- low 1M MC ('0xbfd5206962267c7b4b4a8b3d76ac2e1b2a5c4d5e', ' OSAK','OsakaProtocol','

OSAK ' ), ('0x50dA645f148798F68EF2d7dB7C1CB22A6819bb2C', ' SPX','spx6900' ,'

SPX '), ('0x881Ed0FCeF78120A135eC6cC66cEf2779FE95BBA', ' DOGEGF','dogegftoken' ,'

DOGEGF '), ('0xFC5462143A3178CF044E97C491F6bCb5e38f173E', ' BERF' ,'BerfBased','

BERF '), ('0x7ED613AB8b2b4c6A781DDC97eA98a666c6437511', ' AYB','AllYourBase_AYB','

AYB ' ), ('0x768BE13e1680b5ebE0024C42c896E3dB59ec0149', ' SKI','Ski_CTO' ,'

SKI '), ('0x0028e1E60167b48a938B785AA5292917E7eacA8b', ' COINYE','coinye_westcoin' ,'

COINYE '), ('0xDe7a416ac821C77478340EEBaA21b68297025eF3', ' BENI','benionbase' ,'

BENI '), ('0x4b61E2f1BBDEe6D746209a693156952936F1702C', ' LAMBO','basedlambow' ,'

LAMBO '), ('0xD9eA93A38D1771C870128f9134D4622d331C04C8', ' MABA' ,'mambaonbase','

MABA '), ('0x7157bD49F14835a412f1331D7c1e7F05Ae54dB85', ' BUDDHA','basedbuddhaxyz' ,'

BUDDHA '), ('0xcA8e8d244f0d219a6Fc9e4793C635cEA98d0399C', ' CAT','' ,'

CAT '), ('0x26F1bB40eA88B46CEB21557dC0FfAC7B7c0ad40f', ' ALF','AlfOnBase' ,'

ALF '), ('0x2075f6E2147d4AC26036C9B4084f8E28b324397d', ' CTO' ,'','

CTO '), ('0xfad8cb754230dbfd249db0e8eccb5142dd675a0d', ' AEROBUD','AeroBudCoin','

AEROBUD ' ), ('0x32E0f9d26D1e33625742A52620cC76C1130efde6', ' BASED','' ,'

BASED '), ('0x7d9CE55D54FF3FEddb611fC63fF63ec01F26D15F', ' FUNGI' ,'','

FUNGI '), ('0x8a24d7260cd02d3dfd8eefb66bc17ad4b17d494c', ' BSB' ,'','

BSB '), ('0x8931ee05ec111325c1700b68e5ef7b887e00661d', ' BGUY' ,'','

BGUY '), ('0x461eE40928677644b8195662Ab91BcdaAE6Ef105', ' DOOK','','

DOOK '), ('0x3afeae00a594fbf2e4049f924e3c6ac93296b6e8', ' MAMBA' ,'','

MAMBA '), -- micro mc UNDER 200K MC ('0x0002BcDaf53f4889BF2f43A3252d7c03Fe1b80bc', ' GORPLE' ,'Gorplescoin','

GORPLE '), ('0x7480527815ccAE421400Da01E052b120Cc4255E9', ' WORKIE','WorkieBase','

WORKIE ' ), ('0xC48E605c7b722a57277e087a6170B9E227e5AC0A', ' OMNI','OmniBase_xyz' ,'

OMNI '), ('0x72cCF64EE5E2C7629eE4eEe3e6AD6990289178ae', ' VINU','','

VINU ' ), ('0xe095780bA2A64a4Efa7a74830F0b71656f0b0AD4', ' BYTE' ,'','

BYTE '), ('0xC7DcCA0a3e69bD762C8DB257f868f76Be36c8514', ' KIBSHI','','

KIBSHI ' ), ('0x803b629C339941e2b77D2dC499DAc9e1fD9eAC66', ' EARN','','

EARN ' ), ('0xA067436Db77aB18b1a315095E4b816791609897c', ' WASSIE','' ,'

WASSIE '), ('0x2ad3d80c917ddBf08aCc04277f379e00e4D75395', ' COW','','

COW '), ('0xdb08CA267e824fb29eb455B7e06Fe73FB0370895', ' GODCAT','','

GODCAT '), ('0x76Baa16FF15D61d32E6B3576c3a8C83A25C2F180', ' PLEB','','

PLEB '), ('0xFd008f937B4d73EEb00Cf74CE90c392be5f07F96', ' MOON','','

MOON '), ('0xb043BaD01195700e737D0AEE852584eAE9393134', ' FLOWER','','

FLOWER '), ('0xD1dB4851bcF5B41442cAA32025Ce0Afe6B8EabC2', ' ZOOMER','','

ZOOMER '), ('0xfB18511F1590a494360069F3640c27d55c2B5290', ' WGC','','

WGC '), ('0xc9b6eF062Fab19D3F1eABc36b1f2E852AF1Acd18', ' BALT','','

BALT '), ('0x6223901eA64608c75Da8497d5eff15D19A1D8fd5', ' CORGI','','

CORGI ') ) AS t (contract_address, token_name, twitter,image) ), price AS ( SELECT c.contract_address AS token_address, p.price FROM contract_info c JOIN base.price.ez_prices_hourly p ON LOWER(c.contract_address) = p.token_address WHERE (hour, p.token_address) IN ( SELECT MAX(hour), token_address FROM base.price.ez_prices_hourly WHERE token_address IN (SELECT LOWER(contract_address) FROM contract_info) GROUP BY token_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 base.price.ez_prices_hourly p ON LOWER(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 base.price.ez_prices_hourly p ON LOWER(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) ) , mc as (SELECT ci.token_name as "Meme Coin", ci.twitter as "Twitter", ci.image as "Image", ci.contract_address as "Contract", round(p.price,10) as "Price", change_from_previous_day , change_from_previous_7_days , change_from_previous_30_days FROM price p JOIN contract_info ci ON LOWER(p.token_address) = LOWER(ci.contract_address) JOIN min_max ON LOWER(p.token_address) = LOWER(min_max.contract_address) JOIN current_price_info ON LOWER(p.token_address) = LOWER(current_price_info.contract_address) ) select "Meme Coin", "Price", "Contract" , case when change_from_previous_30_days > 0 then '🟢 '||change_from_previous_30_days else '🔴 '||change_from_previous_30_days end as "Price (30d)" , change_from_previous_30_days as "price30", --change_from_previous_7_days as "Price (7d)" , --change_from_previous_30_days as "Price (30d)" , "Twitter", "Image" , case when change_from_previous_30_days > 0 then 'Price Up' else 'Price Down' end as "Growth" from mc ORDER BY change_from_previous_30_days DESC