Active Businesses

Active Businesses

What is this problem about?

The Active Businesses interview question revolves around analyzing user engagement data stored in a database. You are typically given an Events table containing business IDs, event types, and an "occurrences" value. An "active business" is defined as a business that has more than one event type where the occurrences for that specific event are greater than the average occurrences for that event type across all businesses.

Why is this asked in interviews?

Companies like Yelp use this Active Businesses coding problem to evaluate a candidate's proficiency with SQL aggregations and subqueries. It tests your ability to calculate a metric (the average) at one level of granularity (event type) and then apply a filter at a different level (business ID). It is a classic example of complex data filtering and business logic implementation.

Algorithmic pattern used

This problem follows the Database interview pattern of using Common Table Expressions (CTEs) or subqueries with GROUP BY and HAVING. The logic is usually split into two parts:

1. Calculate the average occurrences per event type using a window function or a subquery.
2. Join this back to the main table to filter rows where the business's occurrences exceed that average, then group by business ID to count those instances.

Example explanation

Imagine the following data:

• Business 1, 'Reviews', 10 occurrences
• Business 2, 'Reviews', 2 occurrences
• Business 1, 'Ads', 5 occurrences
• Business 2, 'Ads', 1 occurrence

Average for 'Reviews' is 6. Average for 'Ads' is 3. Business 1 exceeds the average for both (10 > 6 and 5 > 3). Since it has 2 such event types (which is > 1), Business 1 is "Active." Business 2 fails both.

Common mistakes candidates make

• Incorrect Averaging: Calculating a single average for all event types combined instead of calculating an average per specific event type.
• Filtering too early: Trying to filter the businesses before calculating the global averages.
• Off-by-one errors: Forgetting that the requirement usually specifies more than one event type (count > 1).

Interview preparation tip

Practice using window functions like AVG(occ) OVER(PARTITION BY event_type). They are often cleaner and more performant than joining a separate aggregation subquery in modern SQL environments.