【原创】4道SQL题:快速入门数据分析师面试

挖数网精选
挖数网精选
挖数网精选
446
文章
0
评论
2020-07-0813:07:00 评论 325 1826字
摘要

SQL作为数据分析师在日常工作中的必备技能,直接影响着分析师们的工作效率,用好学好SQL会使工作效率大幅度提升。

根据下面的例表编写SQL查询语句:

表名:order

【原创】4道SQL题:快速入门数据分析师面试

表名:product

【原创】4道SQL题:快速入门数据分析师面试

表名:store

【原创】4道SQL题:快速入门数据分析师面试

1.查找符合下列要求的产品,并按照产品价格降序排列:CategoryA 且颜色为yellow,或者Weight大于5;

参考代码

select *

from product

where Category = "CategoryA"

and (Color = "Yellow" OR weight > 5)

order by Price

2.请计算每一位客人的总购买金额(Amount),总购买订单数,总购买产品件数(Quantity),同一个客人同一天的订单算作一单,并筛选出总购买金额大于等于800的客人,按金额降序排列;

参考代码

SELECT NAME

,sum(amount) as sum_amt

,count(DISTINCT orderdate) as sum_order

,sum(quantity) as 总商品数

FROM `order`

GROUP BY NAME

having sum(amount) >= 800

order by sum_amt DESC;

3.请查找每个城市(City)购买金额排名第二的客人,列出其购买城市,姓名,和购买金额;

参考代码

SELECT *

FROM

(

SELECT city

,`NAME`

,sum(amount) as amt_total

,row_number()over(PARTITION by city order by sum(amount) desc) as ranking

FROM `order` as a

inner join store as b ON a.store = b.store

GROUP BY city,`NAME`) t

WHERE ranking = 2;

4.购买过ProductA 且 购买过 ProductB的顾客人数;

参考代码

法一:借助count

SELECT count(*) as cust_num

FROM (SELECT Name

,count(DISTINCT product) as scount

from `order`

WHERE product = "ProductA" or product = "ProductB"

group by Name

having scount > 1) t

法二:嵌套子查询

select name from `order`

where product = "ProductA"

and name in (select name from `order`

where product = "ProductB");

法三:借助 join

SELECT a.name

from

(select name from `order` where product = "ProductA") a

inner join

(select name from `order` where product = "ProductB") b

on a.name = b.name;

测试题4常见错误

错例①:"in" 为逻辑"或"的关系

# 形式①

(select `name` from `order`

where product = "ProductA"

or product = "ProductB")

# 形式②

select `name` from `order`

where product in ("ProductA","ProductB")

错例②:使用 in 和 union不能够实现需求

select name from product

where name in (select name from product where product = "productA"

union select name from product where product = "productB" )

错例③:在限制条件中使用 "and" 不能满足需求,且会导致结果为null;

select name from `order`

where `name`in(SELECT DISTINCT name

from product where product = "Product")

and`name`in(select name

from `order` where product = "ProductB");

本次考点

  • 考察知识点:join、having、子查询、聚合分组、窗口函数、条件筛选等语句的掌握以及sql技能综合运用的能力
  • 考察同学们的审题是否细致、考查理解业务的能力
  • 将业务需求转换成构建SQL语句、并得到分析结果的能力
  • 需要同学们熟记SQL子句的书写顺序和运行顺序
  • 考察对having语句的掌握,很多人会把聚合函数写到where子句中

End.

题目解析:爱数据小辉

数据分析更多有趣、实用的知识关注爱数据学院社区分享

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: