Query editor
WITH orderfills AS (SELECT *,bytearray_ltrim(topic2) as maker,bytearray_ltrim(topic3) as taker,bytearray_to_uint256(bytearray_substring(data, 1, 32)) as makerAssetId,bytearray_to_uint256(bytearray_substring(data, 33, 32)) as takerAssetId,bytearray_to_uint256(bytearray_substring(data, 65, 32)) as makerAmountFilled,bytearray_to_uint256(bytearray_substring(data, 97, 32)) as takerAmountFilled,bytearray_to_uint256(bytearray_substring(data, 129, 32)) as feeFROM bnb.logsWHERE block_number >= 65733322AND contract_address = 0x5F45344126D6488025B0b84A3A8189F2487a7246AND topic0 = 0xd0a08e8c493f9c94f29311604c9de1b4e8c8d4c06bd0c789af57f2d65bfec0f6),daily AS (SELECT DATE_TRUNC('day', block_time) as "Date", maker as "User", tx_hashFROM orderfillsUNION ALLSELECT DATE_TRUNC('day', block_time) as "Date", taker as "User", tx_hashFROM orderfills),user_first_seen AS (SELECT"User",MIN("Date") AS first_seen_dateFROM dailyGROUP BY "User"),daily_metrics AS (SELECTd."Date",COUNT(DISTINCT d."User") AS "DAU",COUNT(DISTINCT CASE WHEN d."Date" = ufs.first_seen_date THEN d."User" END) AS "New",COUNT(DISTINCT CASE WHEN d."Date" > ufs.first_seen_date THEN d."User" END) AS "Old",COUNT(DISTINCT d.tx_hash) AS "Daily Transactions"FROM daily dLEFT JOIN user_first_seen ufs ON d."User" = ufs."User"GROUP BY d."Date")SELECT"Date","DAU","New","Old","Daily Transactions",SUM("New") OVER (ORDER BY "Date") AS "Cumulative Unique Users",-- Moving averagesAVG("DAU") OVER (ORDER BY "Date" ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "7-Day MA DAU",AVG("DAU") OVER (ORDER BY "Date" ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS "30-Day MA DAU",-- Growth metricsROUND(100.0 * ("DAU" - LAG("DAU", 1) OVER (ORDER BY "Date")) /NULLIF(LAG("DAU", 1) OVER (ORDER BY "Date"), 0), 2) AS "DAU Growth %"FROM daily_metrics