SQL面试

问题1：创建表act_output，保存以下信息：区分不同活动，统计每个活动对应所有用户在报名参与活动之后产生的总订单金额、总订单数（一个用户只能参加一个活动）

create table act_output as 
select a.act_id,sum(ord_amt) as total_amount,count(ord_id) as total_order
from ord as a inner join act_usr as a
on o.user_id = a.user_id
where o.create_time > a.create_time
group by a.act_id

（2）加入活动开始后每天都会产生订单，计算每个活动截止当前（2019-08-12）平均每天产生的订单数，活动开始时间假设为用户最早报名时间

select a.act_id,count(ord_id)/datediff(day, min(a.create_time),'2019-08-12') as day_order
from ord as a inner join act_usr as a
on o.user_id = a.user_id
where o.create_time > a.create_time
group by a.act_id

（1）计算网站每天的访客数以及他们的平均操作次数；

select log_time，count(distinct user_id) as uv,round(count(opr_type)/count(distinct user_id),2) as avg_opr
from tracking_log
group by log_time

（2）统计每天符合A操作后B操作的操作模式的用户数，即要求AB相邻。
select * from tracking_log as a

create view x as 
(select user_id,opr_type,log_time,row_number() over(partition by log_time order by user_id) as flag

(3) 计算网络每日新增访客表

select distinct user_id,log_time from traking_log

select * from
(select *,row_number() over(order by employee_salary) as num from tabl)
where num <4

select student_id ,count(distinct class_id) as num
from class 
group by student_id 

select student_id,school_year,avg(gpa) as avg_gpa
from table1
group by student_id,school_year
having avg(gpa) >= 3.5

create view view2 as 
(select employee_id,department_id ,employee_salary,row_number() over (partition by department_id order by employee_salary desc) rank
from table2;
select *
from view2
where rank >= 3

求每组商品的浏览用户数（同组内同一用户只能算一次）

create view view3 as 
(select goods_id,sum(amount) as total_amount,
case when row_number() over(order by sum(amount) desc) <= 10 then 'top10'
when  row_number() over(order by sum(amount) desc) > 10  and  row_number() over(order by sum(amount) desc) <=20 then 'top10~top20' else '其他' end as gp
from order1
group by goods_id;
select v.gp,count(distinct uid) as uv
from pv as p
left join view3 as v
on p.goods_id = v.goods_id
group by v.gp

select *
from table2
where t1 > t3 and t1 < t4 and