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]

Loading problem...

Preparing your editor. You can read the problem while it loads.

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]