Metric Definition Reverse Engineering

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L6

Problem

We're a food delivery company like DoorDash. Our exec dashboard tracks five metrics: DAU, revenue per user, order completion rate, average delivery time, and customer lifetime value. The current SQL is a mess of nested subqueries on raw event tables. Can you design a clean dimensional model that makes these metrics simple to compute?

Five numbers on a dashboard. Your job: figure out where they come from.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate can derive a schema from a set of metric definitions and recognize when measures need decomposition. Revenue per user, DAU, completion rate, delivery time, and CLV each point at a different grain; the design has to honor all five simultaneously.

Break down the requirements

Step 1: Decompose revenue

orders.subtotal, delivery_fee, tip, promo_discount, refund as separate additive columns. Lumping them into total kills any ability to explain a delta.

Step 2: Separate activity from orders

user_daily_activity has one row per active user per day. DAU is a COUNT on this; you cannot derive DAU from orders because non-ordering sessions are missing.

Step 3: Track order state transitions

order_events captures placed, accepted, picked_up, delivered, cancelled with timestamps. Delivery time is delivered_ts - placed_ts at query time.

Step 4: Preserve dimensional spine

users, restaurants, drivers stay as dimensions so that metric slicing by segment is a single GROUP BY.

The solution

Below is one defensible model. The split between orders (what happened) and user_daily_activity (who showed up) is the conceptual anchor for metric derivability.

`user_daily_activity` enables DAU for non-orderers; revenue decomposes into additive components on orders
  • users: user_id BIGINT PK, signup_date DATE, region TEXT
  • restaurants: restaurant_id BIGINT PK, name TEXT, cuisine TEXT, city TEXT
  • drivers: driver_id BIGINT PK, region TEXT, hired_at DATE
  • orders: order_id BIGINT PK, user_id BIGINT FK, restaurant_id BIGINT FK, driver_id BIGINT FK, placed_at TIMESTAMP, subtotal DECIMAL, delivery_fee DECIMAL, tip DECIMAL, promo_discount DECIMAL, refund DECIMAL
  • order_events: event_id BIGINT PK, order_id BIGINT FK, event_type TEXT, event_ts TIMESTAMP
  • user_daily_activity: user_id BIGINT FK, activity_date DATE, session_count INT, last_seen_ts TIMESTAMP
  • users references orders
  • restaurants references orders
  • drivers references orders
  • orders references order_events
  • users references user_daily_activity

The analysis pattern

Five metrics derived from the schema
WITH daily AS (
    SELECT activity_date, COUNT(DISTINCT user_id) AS dau
    FROM user_daily_activity
    GROUP BY activity_date
),
revenue AS (
    SELECT
        DATE(placed_at) AS d,
        SUM(subtotal + delivery_fee + tip - promo_discount - refund)
          / NULLIF(COUNT(DISTINCT user_id), 0) AS rev_per_user
    FROM orders
    GROUP BY DATE(placed_at)
),
completion AS (
    SELECT DATE(e1.event_ts) AS d,
           COUNT(*) FILTER (WHERE e2.event_type = 'delivered') * 1.0
             / COUNT(*) AS completion_rate
    FROM order_events e1
    LEFT JOIN order_events e2 ON e2.order_id = e1.order_id AND e2.event_type = 'delivered'
    WHERE e1.event_type = 'placed'
    GROUP BY DATE(e1.event_ts)
)
SELECT daily.activity_date, dau, rev_per_user, completion_rate
FROM daily
LEFT JOIN revenue ON revenue.d = daily.activity_date
LEFT JOIN completion ON completion.d = daily.activity_date

Trade-offs and alternatives

Decomposed revenue + activity fact
Revenue split into additive parts; DAU from its own fact. * Every metric is derivable with a GROUP BY * Deltas can be explained component by component * Two facts to keep fresh
Single orders fact with total column
One orders table with a precomputed total. * Simpler ingest * DAU query silently wrong * Cannot isolate promo or tip contribution

Common follow-up questions