MEDIUMSolve on LeetCode
1126. Active Businesses
Problem Statement
A business is active if it has at least two events with an occurrence count strictly greater than the average occurrence count for that event type across all businesses. Return active businesses' ids, in any order.
Table Schema
Table: Events
+---------------+---------+
| business_id | int |
| event_type | varchar |
| occurences | int |
+---------------+---------+
(business_id, event_type) is the primary key.
Examples
Example 1
Input Table:
Events table:
+-------------+------------+------------+
| business_id | event_type | occurences |
+-------------+------------+------------+
| 1 | reviews | 7 |
| 3 | reviews | 3 |
| 1 | ads | 11 |
| 2 | ads | 7 |
| 3 | ads | 6 |
| 1 | page views | 3 |
| 2 | page views | 12 |
+-------------+------------+------------+
Expected Output:
+-------------+
| business_id |
+-------------+
| 1 |
+-------------+
SQL Solution
SELECT business_id
FROM Events e
WHERE occurences > (
SELECT AVG(occurences) FROM Events e2 WHERE e2.event_type = e.event_type
)
GROUP BY business_id
HAVING COUNT(*) >= 2;
Problem Info
DifficultyMEDIUM
Topics
correlated-subquerygroup-byhaving
Reference Links