Active Business Problem


Description

LeetCode Problem 1126.

Table: Events

1
2
3
4
5
6
7
8
9
+---------------+---------+
| 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 query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Events table:
+-------------+------------+------------+
| 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 table:
+-------------+
| business_id |
+-------------+
| 1           |
+-------------+ 
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.


MySQL Solution

1
2
3
4
5
6
7
select business_id 
from
    (select *, avg(occurences) over(partition by event_type) as average
    from Events) t1
where occurences > average
group by business_id
having count(business_id) > 1




Related Posts

User Purchase Platform Problem

LeetCode 1127. Write an SQL query to find the total...

Reported Posts II Problem

LeetCode 1132. Write an SQL query to find the average...

Active Business Problem

LeetCode 1126. Write an SQL query to find all active...

Highest Grade For Each Student Problem

LeetCode 1112. Write a SQL query to find the highest...

Reported Posts Problem

LeetCode 1113. Write an SQL query that reports the number...