How would you build a weekly active user dashboard?

WHAT THIS TESTS: This question assesses your ability to translate a business request into a concrete technical plan. It's not just about SQL. Interviewers are looking for product sense (clarifying requirements), data modeling fundamentals (designing efficient data structures), and knowledge of the standard analytics stack (ETL/ELT -> Data Warehouse -> BI Tool).

A GOOD ANSWER COVERS: Four key steps in order. First, Clarification: Work with the PM to create a precise definition of "active" for this feature. Is it a page view, a button click, or a specific API call? Also, clarify the definition of a "user" (e.g., logged-in user ID, not a device ID). Second, Data Identification: State that you need an events table containing, at minimum, a unique user_id, a precise timestamp, and an event_name or feature_id. Third, Data Pipeline/Modeling: Describe creating an intermediate, aggregated table in the data warehouse. An ETL/ELT job would run daily or hourly to count distinct users per feature, per day. This avoids querying massive raw event logs from the BI tool, which is slow and expensive. Fourth, BI Tool Setup: Explain connecting the BI tool (like Looker) to this new aggregate table. You would then build the visualization: a time-series chart with the date on the x-axis and a measure of COUNT(DISTINCT user_id) on the y-axis, grouped by week. Mentioning a 7-day rolling count is a great detail.

COMMON WRONG ANSWERS: Jumping straight into writing a SQL query without first asking clarifying questions about the definition of "active." This signals a junior engineer who executes tasks without understanding the business context. Another major red flag is suggesting COUNT(user_id) instead of COUNT(DISTINCT user_id), which would incorrectly count every single event instead of unique users. Finally, proposing to connect the BI tool directly to a production application database is a critical error; it risks performance and security.

LIKELY FOLLOW-UPS: How would you calculate the WAU/MAU ratio? (Tests understanding of user stickiness). The dashboard is loading slowly; what are three ways you could speed it up? (Tests knowledge of materialized views, changing the data refresh cadence, or using a more efficient aggregation strategy). How would you exclude internal employees or bot traffic from this dashboard? (Tests data cleaning and filtering expertise).

ONE CONCRETE EXAMPLE: Let's say the feature is a new "Export to CSV" button. The key user interaction is the export_csv_click event. The raw data lives in an event stream like Segment or a database table application_events. A daily dbt or Airflow job would process these raw events and populate a table called analytics.daily_feature_usage with columns: event_date, user_id, feature_name. In Looker, you would connect to this table, create a measure for distinct_users using type: count_distinct on the user_id column, and build a line chart. You would filter by feature_name = 'export_csv_click' and set the time dimension to display by week.