1126 Active Businesses

Description

Table: Events

• (business_id, event_type) is the primary key (combination of columns with unique values) of this table.
• Each row in the table logs the info that an event of some type occurred at some business for a number of times.

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

Problem Statement

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 a solution to find all active businesses. Return the result table in any order. The result format is in the following example.

Example 1:

Input:

• Events table:

Output:

Explanation:
The average activity for each event can be calculated as follows:

• ‘reviews’: (7+3)/2 = 5
• ‘ads’: (11+7+6)/3 = 8
• ‘page views’: (3+12)/2 = 7.5
• The business with id=1 has 7 ‘reviews’ events (more than 5) and 11 ‘ads’ events (more than 8), so it is an active business.

Solution

Approach 1: Using Temporary views

The problem can be divided into following.

1. First find out the average_occurrences for each event_type in the table.

1WITH average_occurrences AS (
2    SELECT event_type, AVG(occurrences)
3        FROM Events
4        GROUP BY event_type
5) SELECT * 
6    FROM average_occurrences;

2. The next step is to find the number of occurrences for each business_id and event_type combination.

1SELECT business_id, event_type, occurrences num_occurrences
2    FROM Events
3    GROUP BY business_id, event_type

3. Next, you need to check which business_id has more occurrences than the average for their event_type.

1SELECT ca.business_id
2    FROM average_occurrences ao
3    JOIN company_averages ca
4    ON ao.event_type = ca.event_type
5    WHERE ca.num_occurrences > ao.avg_occurrences
6    GROUP BY ca.business_id
7    HAVING COUNT(1) > 1;

The final solution looks like this.

 1WITH average_occurrences AS (
 2    SELECT event_type, AVG(occurrences) avg_occurrences
 3        FROM Events
 4        GROUP BY event_type
 5), company_averages AS (
 6    SELECT business_id, event_type, occurrences num_occurrences
 7        FROM Events
 8        GROUP BY business_id, event_type
 9) SELECT ca.business_id
10    FROM average_occurrences ao
11    JOIN company_averages ca
12    ON ao.event_type = ca.event_type
13    WHERE ca.num_occurrences > ao.avg_occurrences
14    GROUP BY ca.business_id
15    HAVING COUNT(1) > 1;

Approach 2

If you look at above solution, you notice that second temporary view is not doing much as the table itself has one row per business_id and event_type. So, you can completely skip that temporary view and in the last step join with Events table.

 1WITH average_occurrences AS (
 2    SELECT event_type, AVG(occurrences) avg_occurrences
 3        FROM Events
 4        GROUP BY event_type
 5) SELECT e.business_id
 6    FROM average_occurrences ao
 7    JOIN Events e
 8    ON ao.event_type = e.event_type
 9    WHERE e.occurrences > ao.avg_occurrences
10    GROUP BY e.business_id
11    HAVING COUNT(1) > 1;