Query editor
WITH symbols_list(symbol, contract_address, circulating_supply) AS (VALUES('DOGE', FROM_HEX('ba2ae424d960c26247dd6c32edc70b295c744c43'), TRY_CAST('145687856383' AS DOUBLE)),('SHIB', FROM_HEX('95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce'), TRY_CAST('589261343942985' AS DOUBLE)),('PEPE', FROM_HEX('6982508145454ce325ddbe47a25d4ec3d2311933'), TRY_CAST('420690000000000' AS DOUBLE)),('WIF', FROM_HEX('c5f9fb32f49111ab20c33f2598fc836c113e291881ac21ee29169394011244e4'), TRY_CAST('998926392' AS DOUBLE)),('FLOKI', FROM_HEX('d7f8fa0f925bde14321ae5a2beed6db1b1b46335398077c6e43e9d75d8247d0e'), TRY_CAST('9690363531915' AS DOUBLE)),('BONK', FROM_HEX('bc07c56e60ad3d3f177382eac6548fba1fd32cfd90ca02b3e7cfa185fdce7398'), TRY_CAST('69474461693558' AS DOUBLE)),('BRETT', FROM_HEX('532f27101965dd16442e59d40670faf5ebb142e4'), TRY_CAST('9910173042' AS DOUBLE)),('POPCAT', FROM_HEX('5d0b159affcbccf165c09bc2f5d4bafb4aa6345af793b9b3222daa40293a950d'), TRY_CAST('979978669' AS DOUBLE)),('DOGS', FROM_HEX(''), TRY_CAST('550000000000' AS DOUBLE)),('BOME', FROM_HEX('0d8323c076f0e28718ca60d77e6b39cee8f23f43cfc4ff1f5852b8fc1b94a293'), TRY_CAST('68999659569' AS DOUBLE))),total_mcap AS (SELECTweek,SUM(marketcap) AS total_marketcapFROM query_4020874GROUP BY 1),marketshare AS (SELECTq.symbol,DATE_TRUNC('week', q.week) AS week,q.price,LAG(q.price, 1) OVER (PARTITION BY q.symbol ORDER BY DATE_TRUNC('week', q.week)) AS ytd_price,(q.price / LAG(q.price, 1) OVER (PARTITION BY q.symbol ORDER BY DATE_TRUNC('week', q.week))) - 1 AS "7d_changes",AVG(q.marketcap) AS marketcap,t.total_marketcap,(AVG(q.marketcap) / t.total_marketcap) AS marketshareFROM query_4020874 qLEFT JOIN total_mcap tON t.week = DATE_TRUNC('week', q.week)GROUP BY 1, 2, 3, t.total_marketcapORDER BY week DESC),btc_price AS (SELECTDATE_TRUNC('week', minute) AS week,AVG(price) AS btc_price,(AVG(price) / (LAG(AVG(price)) OVER (ORDER BY DATE_TRUNC('week', minute)))) - 1 AS btc_price_changesFROM prices.usdWHERE contract_address IS NULLAND symbol = 'BTC'AND minute >= CAST('2021-05-31' AS TIMESTAMP)GROUP BY 1ORDER BY week DESC),index_price_changes AS (SELECTm.week,SUM(m."7d_changes" * m.marketshare) AS meme_price_changes,b.btc_price_changesFROM marketshare mINNER JOIN btc_price bON b.week = m.weekGROUP BY 1,3ORDER BY week DESC)SELECT