Define and calculate Weekly Active Users (WAU) for Slack

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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).

ONE 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.