SQL Interview Questions – Active Businesses

Problem Description –

The average activity for a particular event_type is the average occurrences across all companies that have this event.

An active business is a business that has more than one event_type such that their occurrences is strictly greater than the average activity for that event.

Write an SQL query to find all active businesses.

Return the result table in any order.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Active Businesses

Solution –

with result as ( 
SELECT
    event_type,
    AVG(occurences) as avg_occurences
FROM events
GROUP BY 1
)

SELECT
     business_id
FROM events JOIN result
ON events.event_type = result.event_type
WHERE events.occurences > result.avg_occurences
GROUP BY 1
HAVING count(*) > 1

First we calculated the average occurences so that we can compare it with the occurences column. Then we joined this result table to the events table on event type but we also applied a condition that the occurences has to be greater than the average occurences. Then we selected only the business_id which has more than one event which satisfied the condition given in the question.

with result as ( 
SELECT
    event_type,
    AVG(occurences) as avg_occurences
FROM events
GROUP BY 1
)

SELECT
    business_id,
    events.event_type,
    occurences
FROM events JOIN result
ON events.event_type = result.event_type
WHERE events.occurences > result.avg_occurences

In this case we can see it’s only business_id 1 which has more than one event whose occurences is greater than the average occurences. So only business_id 1 is an active business.