How would you build a weekly active user dashboard?

WHAT THIS TESTS: This question tests your ability to think end-to-end about a data product. It's not just about SQL. It's about translating a business need ('track WAU') into technical requirements. The interviewer wants to see if you can define the metric, identify the source data, model it for performance, and then implement it in a BI tool. It separates candidates who just write queries from those who build and own data systems.

A GOOD ANSWER COVERS: A strong answer has four parts. First, clarify the requirements: what exact user action signifies 'active' for this specific feature? Is it a button click, a page view, or a specific API call? Second, identify the data needed: a raw events table with at least a unique user ID, a precise timestamp, and an event name or type. Third, describe the data modeling. This involves creating a clean, aggregated table (e.g., a daily active user fact table) via an ETL or dbt job to make BI queries fast and cheap. This table might pre-calculate daily active users for the feature. Fourth, explain the BI tool implementation. In Looker or Tableau, you would connect to this new fact table and create a metric that calculates a rolling 7-day distinct count of user IDs.

COMMON WRONG ANSWERS: A major red flag is jumping directly into writing a complex SQL query against a raw production database. This shows a lack of concern for performance, cost, and scalability. Another weak answer is being vague about the data source, just saying 'we need the user data.' A senior engineer names the specific tables and fields (e.g., 'the frontend_events table with user_id, timestamp, and event_name columns'). Finally, failing to define 'active' with the product manager is a sign of not thinking like a product-minded engineer.

LIKELY FOLLOW-UPS: How would you backfill this data for the past year? How would you handle changes in the definition of 'active'? How would you ensure this dashboard is performant if the events table has 500 billion rows? How would you calculate Monthly Active Users (MAU) and the WAU/MAU ratio to measure stickiness?

ONE CONCRETE EXAMPLE: To get WAU for a 'video play' feature, I'd first ensure we have an event like video_played logged with user_id and timestamp. I'd run a daily dbt model that scans the raw events, filters for video_played, and creates a table fct_daily_feature_active_users. In Looker, I'd define a measure using a count_distinct on user_id and use a rolling timeframe to create a 7-day active user count. This pre-aggregation avoids querying a massive event table for every dashboard load, keeping it under a 2-second load time.