This Leetcode problem Active Businesses LeetCode Solution is done in SQL.
List of all LeetCode Solution
![Active Businesses LeetCode Solution](https://i0.wp.com/totheinnovation.com/wp-content/uploads/2024/02/LeetCode-Problem-Solution.png?resize=200%2C200&ssl=1)
Active Businesses LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
business_id | int |
event_type | varchar |
occurences | int |
(business_id, event_type) is the primary key of this table.
Each row in the table logs the info that an event of some type occured at some business for a number of times.
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 result format is in the following example.
Example 1:
Input:
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 |
Output:
business_id |
1 |
Explanation:
Average for ‘reviews’, ‘ads’ and ‘page views’ are (7+3)/2=5, (11+7+6)/3=8, (3+12)/2=7.5 respectively.
Business with id 1 has 7 ‘reviews’ events (more than 5) and 11 ‘ads’ events (more than 8) so it is an active business.
Active Businesses LeetCode Solution MySQL
select
business_id
from
Events e,
(
select
event_type,
avg(occurences) as avg_occurences
from
Events
group by
event_type
) as a
where
e.event_type = a.event_type
and e.occurences > a.avg_occurences
group by
e.business_id
having
count(*) > 1;
Code language: SQL (Structured Query Language) (sql)