Multi-Signal Above-Benchmark Businesses

Medium

You are analyzing a business telemetry table that tracks how often each business triggers different event categories. Table: events business_id: unique business identifier event_type: activity category name occurrences: observed count for this business and event type Data property: (business_id, event_type) is unique. Definitions: Benchmark for an event_type = average occurrences across all businesses that have that event_type. A business is considered active when it beats this benchmark (strictly greater than average) in more than one event_type. Task: Return all active businesses. Output requirements: Return exactly one column: business_id One row per qualifying business Sort by business_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

events:
| business_id | event_type  | occurrences |
|-------------|-------------|-------------|
| 1           | reviews     | 7           |
| 2           | reviews     | 3           |
| 3           | reviews     | 5           |
| 1           | ads         | 11          |
| 2           | ads         | 7           |
| 3           | ads         | 6           |
| 1           | page_views  | 9           |
| 2           | page_views  | 12          |
| 3           | page_views  | 6           |

Output

[
  {"business_id":1}
]

Explanation

Business 1 is above average in reviews and ads. It is above benchmark in two event types, so it qualifies.

Example 2

Input

events:
| business_id | event_type     | occurrences |
|-------------|----------------|-------------|
| 10          | search_clicks  | 50          |
| 11          | search_clicks  | 20          |
| 12          | search_clicks  | 30          |
| 10          | support_tickets| 9           |
| 11          | support_tickets| 3           |
| 12          | support_tickets| 3           |
| 10          | shares         | 5           |
| 11          | shares         | 8           |
| 12          | shares         | 8           |
| 11          | wishlists      | 18          |
| 12          | wishlists      | 6           |

Output

[
  {"business_id":10},
  {"business_id":11}
]

Explanation

Business 10 beats averages for search_clicks and support_tickets. Business 11 beats averages for shares and wishlists.

Example 3

Input

events:
| business_id | event_type | occurrences |
|-------------|------------|-------------|
| 101         | visits     | 20          |
| 102         | visits     | 20          |
| 101         | calls      | 10          |
| 102         | calls      | 10          |

Output

[]

Explanation

All values equal their event averages, and strict inequality is required.

Constraints

1 <= number of rows in events <= 10,000
1 <= business_id <= 1,000,000,000
1 <= occurrences <= 1,000,000,000
1 <= length of event_type <= 64
(business_id, event_type) is unique
A business qualifies only if it beats event averages in more than one event type
Comparison must be strictly greater than average, not greater-or-equal
Output must contain one row per qualifying business_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with column [business_id]

events: | business_id | event_type | occurrences | |-------------|-------------|-------------| | 1 | reviews | 7 | | 2 | reviews | 3 | | 3 | reviews | 5 | | 1 | ads | 11 | | 2 | ads | 7 | | 3 | ads | 6 | | 1 | page_views | 9 | | 2 | page_views | 12 | | 3 | page_views | 6 |

events: | business_id | event_type | occurrences | |-------------|----------------|-------------| | 10 | search_clicks | 50 | | 11 | search_clicks | 20 | | 12 | search_clicks | 30 | | 10 | support_tickets| 9 | | 11 | support_tickets| 3 | | 12 | support_tickets| 3 | | 10 | shares | 5 | | 11 | shares | 8 | | 12 | shares | 8 | | 11 | wishlists | 18 | | 12 | wishlists | 6 |

events: | business_id | event_type | occurrences | |-------------|------------|-------------| | 101 | visits | 20 | | 102 | visits | 20 | | 101 | calls | 10 | | 102 | calls | 10 |