You are given a table Events that logs business activities. Each row records how many times a particular event occurred at a specific business.
An active business is defined as a business that has more than one event type where the number of occurrences is strictly greater than the average occurrences for that event type across all businesses.
Your task is to write a SQL query to find all active businesses and return their business IDs.
Key Points:
- Calculate the average occurrences for each event type across all businesses
- For each business, count how many event types have occurrences above their respective averages
- A business is active if this count is greater than 1
Table Schema
| Column Name | Type | Description |
|---|---|---|
business_id
PK
|
int | Identifier for the business |
event_type
PK
|
varchar | Type of event that occurred |
occurrences
|
int | Number of times this event occurred at this business |
Input & Output
| business_id | event_type | occurrences |
|---|---|---|
| 1 | reviews | 7 |
| 1 | ads | 11 |
| 1 | page views | 3 |
| 2 | reviews | 3 |
| 2 | ads | 7 |
| 2 | page views | 12 |
| 3 | reviews | 6 |
| 3 | ads | 9 |
| 3 | page views | 4 |
| business_id |
|---|
| 1 |
First, calculate averages for each event type: reviews avg = (7+3+6)/3 = 5.33, ads avg = (11+7+9)/3 = 9.0, page views avg = (3+12+4)/3 = 6.33.
Business 1: reviews (7 > 5.33) ✓, ads (11 > 9.0) ✓, page views (3 > 6.33) ✗ → 2 qualifying events > 1 → Active
Business 2: reviews (3 > 5.33) ✗, ads (7 > 9.0) ✗, page views (12 > 6.33) ✓ → 1 qualifying event ≤ 1 → Not active
Business 3: reviews (6 > 5.33) ✓, ads (9 > 9.0) ✗, page views (4 > 6.33) ✗ → Wait, ads is exactly 9.0, not > 9.0. Let me recalculate: Business 3 has reviews above average and ads exactly at average, so only 1 qualifying event. Actually, let me check: ads average should be exactly 9.0, so business 3's ads (9) is not > 9.0. Business 3 should have 1 qualifying event only. Let me fix this - Business 3 should have ads = 10 to make it active.
| business_id | event_type | occurrences |
|---|---|---|
| 1 | reviews | 8 |
| 1 | ads | 2 |
| 2 | reviews | 4 |
| 2 | ads | 6 |
| business_id |
|---|
Calculate averages: reviews avg = (8+4)/2 = 6.0, ads avg = (2+6)/2 = 4.0.
Business 1: reviews (8 > 6.0) ✓, ads (2 > 4.0) ✗ → 1 qualifying event ≤ 1 → Not active
Business 2: reviews (4 > 6.0) ✗, ads (6 > 4.0) ✓ → 1 qualifying event ≤ 1 → Not active
No businesses have more than one event type above average, so the result is empty.
Constraints
-
1 ≤ business_id ≤ 1000 -
1 ≤ occurrences ≤ 1000 -
event_typeconsists of lowercase English letters and spaces - There are at least 2 distinct event types in the table