Define and calculate Weekly Active Users (WAU) for Slack

What's really being asked

This question tests your product sense and data literacy, not just SQL syntax. The interviewer wants to see if you can translate an ambiguous business goal ('measure active users') into a robust, defensible technical definition. They are evaluating your ability to think critically about what 'active' truly means for a product like Slack and to anticipate real-world data quality issues. A senior candidate must move beyond a simple count to a nuanced discussion of user intent and data integrity.

The full answer

A strong answer has three parts. First, define the business logic. Propose a specific definition of 'active' tied to Slack's core value. This should be a 'meaningful action' like sending a message, adding a reaction, or reading a new channel, not just opening the app. Second, describe the technical implementation. This involves querying an event stream table to COUNT(DISTINCT user_id) over a rolling 7-day window where the event name is in your defined set of meaningful actions. Third, proactively identify pitfalls. A great answer immediately discusses filtering out non-human users (bots, service accounts) via a user properties table and excluding low-intent automated events like background syncs, which do not represent active engagement.

The mistakes people make

The most common mistake is a naive, generic answer. Defining 'active' as merely logging in or opening the app is a red flag; these metrics are easily inflated and don't correlate with real usage. Another weak answer involves writing a simple SELECT COUNT(DISTINCT user_id) FROM users without any time window or event-based logic. A candidate who doesn't spontaneously bring up the problem of bots or automated system events signals a lack of experience with real-world product analytics data, which is never clean.

What usually comes next

Expect questions that dig deeper into the nuances. "How would you differentiate a power user from a casual user?" (Frequency or variety of actions). "How would this definition change for MAU vs. DAU?" (The core definition of 'active' might become more or less stringent). "We saw a sudden 5% drop in WAU. How would you investigate?" (Segment the drop by user type, platform, geography; check for data pipeline errors). "How would you store this data for efficient querying?" (Discuss pre-aggregation in a daily snapshot table).

A concrete example

Assume an event table client_events (user_id, timestamp, event_name) and a user table users (user_id, is_bot). A robust WAU definition might count users who send a message or add a reaction. The query would be: SELECT COUNT(DISTINCT T1.user_id) FROM client_events AS T1 JOIN users AS T2 ON T1.user_id = T2.user_id WHERE T1.timestamp >= CURRENT_DATE - INTERVAL '7 days' AND T1.event_name IN ('message_sent', 'reaction_added') AND T2.is_bot = FALSE;. This query explicitly defines actions, sets the time window, and filters bots.