面试公司ETL工程师（实习生）——笔试面试题（SQL）

表结构

电商平台表“D（code、app）”

购买表“T1（name、date、app、money）”

退货表“T2（name、date、app、money）”

题目及答案

1. 查出“张三”的购买清单。

SELECT * FROM t1 WHERE `name`='张三'
1

2. 统计“张三”每个月的购买金额。

select
name,
date_format(Buydate,'%Y%m'),
sum(B_money)
from(
select
*
from T1 WHERE NAME = '张三') t3
group by month(Buydate);
1
2
3
4
5
6
7
8
9

3. 统计每个人在每个平台下的购买总次数和购买总金额。

SELECT 
NAME,
App as '电商名称',
count(T1.code) as '总次数',
sum(B_Money) as '总金额'
from t1 LEFT JOIN D on t1.code=D.code
GROUP BY name,App;
1
2
3
4
5
6
7

4. 查出在“京东”买过东西的人，在所有电商平台的购买清单。

with t4 as(
select
name
FROM t1 
where code = 'JD'
)
SELECT 
t4.name,
Buydate,
code,
B_Money
from t4 LEFT JOIN t1 ON t4.name=t1.name;
1
2
3
4
5
6
7
8
9
10
11
12

5. 统计“天猫”购买的前2名，按每个人的购买总金额排名。

select
t5.`购买人`,
t5.`总金额`,
ROW_NUMBER() OVER(ORDER BY t5.`总金额` DESC) as '排名'
from(
select 
T1.name as '购买人',
sum(B_money) as '总金额'
from T1 left join D 
on D.code=T1.code
where app='天猫'
group by name
) t5
limit 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6. 统计“张三”每个月的购买金额，每个月比上个月多花多少钱。

with t6 as(
select
ta.name,
date_format(Buydate,'yyyy-MM') date1,
sum(B_money) summ
from(
select
*
from T1 WHERE NAME = '张三')ta
group by month(Buydate)
),
t7 as(
select *,
row_number() over(order by date1) n 
from t6
),
t8 as(
select 
a.name,
a.summ cc,
b.summ dd 
from t7 a left join t7 b on b.n - 1 = a.n
)
select 
t8.name,
cc,
case dd when null then null else dd-cc end 
from t8; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

7. 统计每个人的消费情况，按以下结果显示，消费总额大于1000为星级客户。

with t9 as (
SELECT
name,
sum(B_Money)as '购买总金额'
FROM t1
GROUP BY name
),
t10 as (
SELECT
name,
sum(-R_Money)as '退货总金额'
FROM t2
GROUP BY name
),
t11 as (
SELECT
a.name,
a.`购买总金额`+b.`退货总金额` as '消费总金额'
from t9 a LEFT JOIN t10 b ON a.name=b.name
GROUP BY a.name
),
t12 as (
SELECT
name,
IF(c.`消费总金额`>1000,'星级','普通') as '客户级别'
from t11 c
GROUP BY name
)
SELECT
c.name,
c.`购买总金额`,
d.`退货总金额`,
e.`消费总金额`,
f.`客户级别`
FROM t9 c LEFT JOIN t10 d ON c.name=d.name
LEFT JOIN t11 e ON d.name=e.name
LEFT JOIN t12 f on e.name=f.name
GROUP BY name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

8. 写出你知道的sql优化方法。

引擎优化、索引优化、读写分离、limit、避免select *