Active Businesses

Write an SQL query to find all active businesses. An active business is a business that has more than one 

event type with occurrences greater than the average occurrences of that event type among all businesses.


Example:

Table: Events
+-------------+------------+------------+
| 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         |
+-------------+------------+------------+

Result
+-------------+
| business_id |
+-------------+
1           |
+-------------+ 

Approach

Mysql

SELECT t1.business_id
FROM (
    SELECT business_id, event_type, avg(occurences) as avgo
    FROM Events
    GROUP BY business_id, event_type) as t1 INNER JOIN 
    (SELECT event_type, avg(occurences) as avgo
    FROM Events
    GROUP BY event_type) as t2
on t1.event_type = t2.event_type
WHERE t1.avgo>t2.avgo
GROUP BY business_id
HAVING count(1)>1


No comments:

Post a Comment