Query editor
WITHstock AS (SELECT DISTINCT address AS tokenFROM robinhood.creation_tracesWHERE "from" = 0x4783c67b63de2b358ac5951a7d41f47a38f3c046),rialto_txs AS (SELECT DISTINCT tx_hash, block_date AS dayFROM robinhood.logsWHERE contract_address = 0xc94135b63772b91d79d0a2daab2a8801f32359bdAND topic0 = 0x57eea6b72a8f2bcabded826d953a575345bad3cf4be03293bc0199fd15b7d61cAND block_date >= CURRENT_DATE - INTERVAL '45' DAY),tx_token AS (SELECT tx_hash, token, dayFROM (SELECTt.tx_hash,t.contract_address AS token,MIN(t.block_date) AS day,ROW_NUMBER() OVER (PARTITION BY t.tx_hashORDER BY COUNT(*) DESC, t.contract_address) AS rkFROM tokens.transfers tJOIN rialto_txs rON r.tx_hash = t.tx_hashAND r.day = t.block_dateWHERE t.blockchain = 'robinhood'AND t.block_date >= CURRENT_DATE - INTERVAL '45' DAYAND t.contract_address IN (SELECT token FROM stock)GROUP BY 1, 2) zWHERE rk = 1),price_obs AS (SELECT block_date AS day, token, amount_usd / NULLIF(stock_amount, 0) AS pxFROM (SELECT block_date, token_bought_address AS token,token_bought_amount AS stock_amount, amount_usdFROM dex.tradesWHERE blockchain = 'robinhood'AND block_date >= CURRENT_DATE - INTERVAL '60' DAYAND token_bought_address IN (SELECT token FROM stock)UNION ALLSELECT block_date, token_sold_address,token_sold_amount, amount_usdFROM dex.tradesWHERE blockchain = 'robinhood'AND block_date >= CURRENT_DATE - INTERVAL '60' DAYAND token_sold_address IN (SELECT token FROM stock)) pWHERE amount_usd > 0AND stock_amount > 0),daily_px AS (SELECT day, token, APPROX_PERCENTILE(px, 0.5) AS pxFROM price_obsWHERE px > 0