Query editor
WITH distributions AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(bytearray_to_uint256(bytearray_substring(data, 1, 32)) / 1e18) AS distributed_ethFROM base.logsWHERE contract_address = 0x5CC4Fb66D5FDeD01904f31d32F4083183700358DAND topic0 = 0x4def474aca53bf221d07d9ab0f675b3f6d8d2494b8427271bcf43c018ef1eeadGROUP BY 1),zlp_snapshot AS (SELECTDATE_TRUNC('day', block_time) AS day,MAX_BY(bytearray_to_uint256(bytearray_substring(data, 97, 32)) / 1e18 +bytearray_to_uint256(bytearray_substring(data, 161, 32)) / 1e18,block_time) AS tvl_usd,MAX_BY(bytearray_to_uint256(bytearray_substring(data, 129, 32)) / 1e18 +bytearray_to_uint256(bytearray_substring(data, 193, 32)) / 1e18,block_time) AS zlp_supplyFROM base.logsWHERE contract_address = 0xe7499dD441950F743044bf4Bf22D254690B601a3AND topic0 = 0x38dc38b96482be64113daffd8d464ebda93e856b70ccfc605e69ccf892ab981eGROUP BY 1)SELECTd.day,d.distributed_eth,d.distributed_eth * 2050 AS distributed_usd,z.tvl_usd,z.zlp_supply,CASE WHEN z.tvl_usd > 0THEN d.distributed_eth * 2050 / z.tvl_usd * 100ELSE 0END AS daily_yield_pct,CASE WHEN z.tvl_usd > 0THEN d.distributed_eth * 2050 / z.tvl_usd * 365 * 100ELSE 0END AS apy_pctFROM distributions dLEFT JOIN zlp_snapshot z ON d.day = z.dayORDER BY 1 DESC