SQL - Active Businesses : Selecting 'xx' greater than AVG

Active Businesses

Medium

 

Table: Events

Write an SQL query to find all active businesses.

An active business is a business that has more than one event type with occurences greater than the average occurences of that event type among all businesses.

The query result format is in the following example:

 

ANALYSIS:

 

Here the question defines an active user as COUNT(DISTINCT event_type) > 1 and occurrences > AVG(occurrence) by event_type.

In other words, among the users whose occurrence for an event is greater than that of avg occurrence, select the ones that have more than one event_type. 

 

 

So, the first thing that I need to is to calculate the avg occurrences for each event. Then, I JOIN the subquery with the Events table. Here, the key is to use events.occurrences > temp.avg. This will return users who meet the first condition (occurrences for an event are greater than its avg).

 

Lastly, I need to count the event_type that each user participates. And, select the ones have more than 1 event.

SELECT 
    business_id
FROM
    Events e JOIN 
    
    -- calculating avg(occ) for each event
        (SELECT 
            event_type, AVG(occurences) as avg
        FROM 
            Events
        GROUP BY 
            event_type) temp
            
    -- selecting the ones have occurrences > avg for each event
    ON e.event_type = temp.event_type and e.occurences > temp.avg
GROUP BY 
    business_id 
HAVING 
    COUNT(DISTINCT e.event_type) >1