Query editor
WITH fees_by_day AS (SELECT DATE_TRUNC('day', block_time) AS day,SUM(CASE WHEN varbinary_substring(topic1, 13, 20) = 0x0000000f7ccd4072641b091f59f2ccd107fed445 THEN CAST(varbinary_to_uint256(varbinary_substring(data, 1, 32)) AS DOUBLE) / 1e18 ELSE 0 END) AS protocol_total_eth,SUM(CASE WHEN varbinary_substring(topic1, 13, 20) <> 0x0000000f7ccd4072641b091f59f2ccd107fed445 THEN CAST(varbinary_to_uint256(varbinary_substring(data, 1, 32)) AS DOUBLE) / 1e18 ELSE 0 END) AS creator_total_ethFROM robinhood.logsWHERE contract_address = 0x50b9af842dacb8fc7476660cc86d3787cad66ac4AND topic0 = 0x5c0ce1b1916761250fab78a3ec6e398bbaabd1537003983889748c0c1e5644e3GROUP BY 1), token_flush_by_day AS (SELECT DATE_TRUNC('day', block_time) AS day,SUM(CAST(varbinary_to_uint256(varbinary_substring(data, 65, 32)) AS DOUBLE) / 1e18) AS protocol_token_eth,SUM(CAST(varbinary_to_uint256(varbinary_substring(data, 97, 32)) AS DOUBLE) / 1e18) AS creator_token_ethFROM robinhood.logsWHERE contract_address = 0x50b9af842dacb8fc7476660cc86d3787cad66ac4AND topic0 = 0x8fc7681d2c70f6592d28d17e35e4003bc6a431738dc3a5cd1486f4f9a34e7316GROUP BY 1), eth_price AS (SELECT DATE_TRUNC('day', timestamp) AS day, AVG(price) AS eth_usdFROM prices.dayWHERE blockchain = 'robinhood'AND contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73GROUP BY 1)SELECT f.day,COALESCE(t.creator_token_eth, 0) * p.eth_usd AS creator_earnings_meme_usd,GREATEST(f.creator_total_eth - COALESCE(t.creator_token_eth, 0), 0) * p.eth_usd AS creator_earnings_weth_usd,COALESCE(t.protocol_token_eth, 0) * p.eth_usd AS protocol_meme_fees_usd,GREATEST(f.protocol_total_eth - COALESCE(t.protocol_token_eth, 0), 0) * p.eth_usd AS protocol_revenue_usdFROM fees_by_day fLEFT JOIN token_flush_by_day t ON t.day = f.dayLEFT JOIN eth_price p ON p.day = f.dayWHERE f.day < DATE_TRUNC('day', NOW())ORDER BY 1