数据准备

Create table If Not Exists Events (business_id int, event_type varchar(10), occurences int);
Truncate table Events;
insert into Events (business_id, event_type, occurences) values ('1', 'reviews', '7');
insert into Events (business_id, event_type, occurences) values ('3', 'reviews', '3');
insert into Events (business_id, event_type, occurences) values ('1', 'ads', '11');
insert into Events (business_id, event_type, occurences) values ('2', 'ads', '7');
insert into Events (business_id, event_type, occurences) values ('3', 'ads', '6');
insert into Events (business_id, event_type, occurences) values ('1', 'page views', '3');
insert into Events (business_id, event_type, occurences) values ('2', 'page views', '12');

需求

– 写一段 SQL 来查询所有活跃的业务。
– 如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，
– 并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。

输入

输出

with t1 as (select event_type,avg(occurences) as avg_typefrom Eventsgroup by event_type
),t2 as (select Events.*,t1.avg_type,row_number() over (partition by business_id) as rn1from Events , t1where t1.event_type=Events.event_type and Events.occurences>t1.avg_type
)
select business_id
from t2
where rn1>=2
;