SQL面经

【秋招】拼多多_数据分析岗_面试题整理

SQL查询实例

SQL面试题练习

SQL面经汇总
SQL实战
常见SQL面试题

mysql主键和唯一索引的区别

1）主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键； 2）主键不允许为空值，唯一索引列允许空值；
3）一个表只能有一个主键，但是可以有多个唯一索引；
4）主键可以被其他表引用为外键，唯一索引列不可以；
5）主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的差别

每门课的及格率

select
sum( case when course='c1' and score>=60 then 1 else 0 end)/sum( case when course='c1' then 1 else 0 end) as rate1,
sum( case when course='c2' and score>=60 then 1 else 0 end)/sum( case when course='c2' then 1 else 0 end) as rate2
from table;

表结构为user_id,reg_time,age, 写一句sql按user_id尾数随机抽样2000个用户 写一句sql取出按各年龄段（每10岁一个分段，如（0,10））分别抽样1%的用户

select user_id form table where mod(user_id/10,1)*10=round(rand()*10) limit 200;

mysql rank

sql求排名

SELECT a1.Name, a1.Sales, COUNT(a2.Sales) Sales_Rank 
FROM Total_Sales a1, Total_Sales a2 
WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name) 
GROUP BY a1.Name, a1.Sales 
ORDER BY a1.Sales DESC, a1.Name DESC;

mysql随机选取数据

mysql随机取出多条数据

交易表结构为user_id,order_id,pay_time,order_amount

写sql查询过去一个月付款用户量（提示 用户量需去重）最高的3天分别是哪几天

select date_format(pay_time,'%Y-%m-%d') as date, count(distinct user_id) as num_users
from table
where pay_time>=date_sub(now(),interval 1 month) #过去一个月
group by date
order by num_users desc
limit 3;

写sql查询昨天每个用户最后付款的订单ID及金额

select a.user_id, a.order_amount 
from
(select user_id, order_amount,
row_number() over(partition by user_id order by pay_time desc) as rank
from table
where date_format(pay_time,"%Y-%m-%d")=date_sub(curdate(),interval 1 day) #昨天) 
as a
where rank=1

2、用户登录日志表为user_id,log_time,session_id,plat

写sql查询近30天，每天平均登录用户数量？

select a.date, average(a.numUsers) 
from
(select date_format(log_time,"%Y-%m-%d") as date, count(distinct user_id) as numUsers
from table
where date>=datesub(curdate(),interval 30 day)#近30天
group by date) as a;

表user_id,visit_date,page_name,plat 统计近7天每天到访的新用户数 统计每个访问渠道plat7天前的新用户的3日留存率和7日留存率

对于通过不同渠道拉新进来的用户，经过一段时间许多用户可能会流失，而留下来的用户我们称之为留存用户。分析用户留存是拉新和用户运营的重要指标。假如我们有一张用户访问表：person_visit,记录了所有用户的访问信息，包含字段：用户ID-user_id,访问时间-visit_date,访问页面page_name,访问渠道（android,ios)-plat等

1）请统计近7天每天到访的新用户数

select firstDate, count(user_id) from
(select user_id, min(date_format(visit_date,"%Y-%m-%d")) as firstDate
from person_visit
group by user_id
having firstDate>=datesub(curdate(),interval 7 day) and
firstDate<curdate()
) as temp
groupby firstDate;

2）请统计每个访问渠道7天前（D-7）的新用户的3日留存和7日留存率

七天前的新用户数
select firstDate, plat, count(user_id) from
(select user_id, plat, min(date_format(visit_date,"%Y-%m-%d")) as firstDate
from person_visit
group by user_id
havng firstDate=datesub(curdate(),interval 7 day)
) as temp
group by plat;

3日留存
select plat, count(user_id)
from person_visit,
where date_format(visit_date,"%Y-%m-%d")=datesub(curdate(),interval 4 day)
and user_id in
(
select user_id as newUsers
from person_visit
where min(date_format(visit_date,"%Y-%m-%d"))=datesub(curdate(),interval 7 day)
group by user_id
) as temp
group by plat

如何用SQL做留存率分析

select firstLog,
sum(case when by_day=0 then 1 else 0 end) day_0,
sum(case when by_day=3 then 1 else 0 end) day_3,
sum(case when by_day=7 then 1 else 0 end) day_7
from
(
select p.user_id, date_format(p.log_time,"%Y-%m-%d") as log, firstLog, datediff(log,firstlog) as by_day
from person_visit as p
inner join
(select user_id, min(date_format(log_time,"%Y-%m-%d")) as firstLog
from person_visit
group by user_id
having firstlog=datesub(curdate(), interval 7 day)
) as temp
on p.user_id=temp.user_id
where by_day=0 or by_day=3 or by_day=7
) as temp2

用户登录日志表为user_id,log_id,session_id,plat 用sql查询近30天每天平均登录用户数量 用sql查询出近30天连续访问7天以上的用户数量

select avg(numUser)  as averageUsers from
(
select date_format(visit_time,'%Y-%m-%d') as visit_date, count(distinct user_id) as numUser
from table
where datediff(curdate(),visit_date)<=30
group by visit_date
);

表结构为user_id,reg_time,age, 写一句sql按user_id尾数随机抽样2000个用户 写一句sql取出按各年龄段（每10岁一个分段，如（0,10））分别抽样1%的用户

学校中有一个年级表，有两个核心字段，student_id和class_id，把年级中大于30人的班级查出来？

select class_id
from table
group by class_id
having count(student_id)>=30;

找出每个班级里面的及格的人数和不及格的人数

select
sum(case when grade<60 then 1 else 0) as num1,
sum(case when grade>=60 then 1 else 0) as num2
from table
group by class;

两个表TB_0(订单号，用户名，订单金额，下单时间，商品ID)，TB_1(用户名，创建时间，余额)用SQL解答，

(1) 提取用户余额>=10，半年前下过单买过ID=A，且半年内只买过ID=B的用户信息;

select user_name from TB_1
where balance>=10
intersect
select user_name
from TB_0
where 
(item_id=A
and date_format(order_time,"%Y-%m-%d")=date_sub(curdate(),interval 6 month))
intersect
select user_name
from TB_0
where date_format(order_time,"%Y-%m-%d")>datesub(curdate(),interval 6 month)
group by user_name
having count(distinct item_id)=1 and max(item_id)='B'

(2) 删去TB_1中重复值(只保留一条)

##Leetcode SQL题

Swap salary

update salary
set sex=
case when sex='f' then 'm' else 'f' end;

Second Highest salary

要注意null的情况

select ifnull(
(select distinct Salary from Employee
order by Salary desc
limit 1,1),null) as SecondHighestSalary;

Rank Scores

Mysql cast 和convert的用法

	cast(value as type)
	convert(value, type)

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

二进制，同带binary前缀的效果 : BINARY    
字符型，可带参数 : CHAR()     
日期 : DATE     
时间: TIME     
日期时间型 : DATETIME     
浮点数 : DECIMAL      
整数 : SIGNED     
无符号整数 : UNSIGNED 

Select Score, cast(Rank as signed) as Rank from
(
select Score,
if(@score=Score, @rank:=@rank, @rank:=@rank+1) as Rank, @score=Score
from Scores,
(select @rank:=0,@score:=-1) as t1
order by Score desc
);

select s1.score, (select count(distinct s2.score) from scores as s2 where s2.score>=s1.score) as Rank
from scores as s1
order by s1.score desc;

Trips and Users

case when .. then .. else .. end
round(value,numDigits)

select Request_at as Day, round((sum(case when Status='completed' then 0 else 1 end)/count(*)),2) as 'Cancellation Rate'
from Trips
left join Users as u1
on Trips.Client_Id=u1.Users_id
left join Users as u2
on Trips.Driver_Id=u2.Users_id
where Request_at between '2013-10-01' and '2013-10-03'
and u1.Banned='No' and u2.Banned='No'
group by Request_at;

Find Meadian Given Frequency of numbers

select avg(number) as median
from numbers as n
where n.frequency>=abs(
(select sum(frequency) from numbers where numbers.number>=n.number)-
(select sum(frequency) from numbers where numbers.number<=n.number)
);

Department Top Three Salaries

select  d.name as Department, e1.name as Employee, e1.salary
from Employee as e1,
join Department as d
on es.departmentid=d.id
where (select count(distinct e2.salary) from Employee as e2 where e2.salary>e1.salary and e2.departmentid=e1.departmentid)<=2;

Department Highest Salary

select temp.Name as Department, Employee.Name as Employee, Salary from 
Employee,
(select e.DepartmentId, d.Name, max(Salary) as maxS
from Employee as e
join Department as d
on e.DepartmentId=d.Id
group by e.DepartmentId) as temp
where Employee.Salary=temp.maxS
and Employee.DepartmentId=temp.DepartmentId;

Human Traffic of Stadium

select s1.id, s1.date,s1.people
from stadium as s1, stadium as s2, stadium as s3
where datediff(s2.date,s1.date)=1 and datediff(s3.date,s2.date)=1 and s1.people>=100 and s2.people>=100 and s3.people>=100
union 
select s2.id, s2.date,s2.people
from stadium as s1, stadium as s2, stadium as s3
where datediff(s2.date,s1.date)=1 and datediff(s3.date,s2.date)=1 and s1.people>=100 and s2.people>=100 and s3.people>=100
union
select s3.id,s3.date,s3.people
from stadium as s1, stadium as s2, stadium as s3
where datediff(s2.date,s1.date)=1 and datediff(s3.date,s2.date)=1 and s1.people>=100 and s2.people>=100 and s3.people>=100
order by id;