运营岗SQL面试题

披头

发布于 2020-05-25 23:26:33

1.3K0

发布于 2020-05-25 23:26:33

文章被收录于专栏：datartisan

近日在群里看到一份运营岗位SQL笔试题目，感觉不错，拿来体验一下，还是有点难度的。

建表语句及插入数据

CREATE TABLE `order` (
  `orderid` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `orderdate` date DEFAULT NULL,
  `store` varchar(20) DEFAULT NULL,
  `product` varchar(20) DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `amount` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into `order` values(1, 'CustomerA', '2018-01-01', 'StoreA', 'ProductA', 1, 100);
insert into `order` values(1, 'CustomerA', '2018-01-01', 'StoreA', 'ProductB', 1, 200);
insert into `order` values(1, 'CustomerA', '2018-01-01', 'StoreA', 'ProductC', 1, 300);
insert into `order` values(2, 'CustomerB', '2018-01-12', 'StoreB', 'ProductB', 1, 200);
insert into `order` values(2, 'CustomerB', '2018-01-12', 'StoreB', 'ProductD', 1, 400);
insert into `order` values(3, 'CustomerC', '2018-01-12', 'StoreC', 'ProductB', 1, 200);
insert into `order` values(3, 'CustomerC', '2018-01-12', 'StoreC', 'ProductC', 1, 300);
insert into `order` values(3, 'CustomerC', '2018-01-12', 'StoreC', 'ProductD', 1, 400);
insert into `order` values(4, 'CustomerA', '2018-01-01', 'StoreD', 'ProductD', 2, 800);
insert into `order` values(5, 'CustomerB', '2018-01-23', 'StoreB', 'ProductA', 1, 100);

CREATE TABLE `product` (
    product VARCHAR(20),
    category VARCHAR(20),
    color VARCHAR(20),
    weight DOUBLE,
    price INT
)

insert into product values('ProductA', 'CategoryA', 'Yellow',  5.6, 100);
insert into product values('ProductB', 'CategoryA', 'Red'   ,  3.7, 100);
insert into product values('ProductC', 'CategoryB', 'Blue'  , 10.3, 100);
insert into product values('ProductD', 'CategoryB', 'Black' ,  7.8, 100);

CREATE TABLE `store` (
`store` varchar(20) DEFAULT NULL,
`city` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into `store` values('StoreA', 'CityA');
insert into `store` values('StoreB', 'CityA');
insert into `store` values('StoreC', 'CityB');
insert into `store` values('StoreD', 'CityC');
insert into `store` values('StoreE', 'CityD');
insert into `store` values('StoreF', 'CityB');

问题1

category 为 CategoryA, 且颜色为 Yellow, 或者 weight 大于 5，并按照产品价格降序排列；思路：常规题目，注意且和或的关系

SELECT 
    *
FROM
    product
WHERE
    category = 'CategoryA'
        AND (color = 'Yellow' OR weight > 5)
ORDER BY price DESC;

问题2

计算每位顾客的总购买金额 aomunt, 总购买订单数，总购买产品件数 quantity, 同一顾客同一天的订单算做一单，并筛选出总购买金额大于等于800的客人，按照金额降序排列; 解题思路：此处总购买金额大于等于门限，该值为汇总值，需要用到having

SELECT 
    name, 
    SUM(amount) as sum_smount, 
    COUNT(DISTINCT orderdate) as cnt_order, 
    SUM(quantity) as sum_quantity
FROM
    `order`
GROUP BY name
HAVING SUM(amount) >= 800
ORDER BY SUM(amount) DESC;

问题3

请查找出每个城市（city）购买金额排名第二的客人，列出其购买城市，姓名，和购买金额；解题思路：排名问题，需要用到开窗函数row_number(使用MySQL的话，8.0以上版本才支持)找出不同客户在每个城市中总购买金额排名，然后过滤出排名=2的客户，会用到多层子查询

SELECT *
from(
 SELECT 
  aa.name, aa.city, sum(aa.amount) as sum_amount,
  ROW_NUMBER () OVER ( PARTITION BY aa.city ORDER BY sum(aa.amount) desc) AS rankn
 from (
   SELECT 
    a.name, a.amount, b.city
   FROM
    `order` a
    left join `store` b on a.store = b.store
    ) aa
  group by aa.name, aa.city ) aaa
where rankn = 2;

问题4

找出购买 ProdunctA 和 ProductB 的客户

解题思路①：同时购买ProdunctA 和 ProductB的客户，可以分两步走，先找出购买了ProdunctA的客户，在从中筛选也购买了ProductB的客户

select name from `order` where 
product = 'ProductA' and 
name in (select name from `order` where product = 'ProductB') ;

解题思路②：分别找出购买了ProdunctA 和 ProductB的客户，两张临时表inner join，关联到的客户即为同时购买了ProdunctA 和 ProductB的客户

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;