SQL校招社招刷题系列之投资信息查询

源表

现有投资表和业务员表两张表，其表结构如下：

投资表（cmn_investment_request）

字段名	注释	类型
Created_at	投资时间	date
User_id	用户ID	String
Invest_item	投资产品	String
Invest_amount	投资金额	Int

业务员表（dim_agent）

字段名	注释	类型
User_id	用户id	String
Start_date	开始时间	Date
End_date	结束时间	Date
Agent_id	业务员ID	String

问题

题目：

1. 计算2017年每笔投资均大于50万的用户。
2. 计算2017年仅投资过CFH和AX产品的用户
3. 计算归属10002业务员的投资金额

问题一的解答

SELECT User_id 
FROM xiaoliu.cmn_investment_request
WHERE YEAR(Created_at)='2017'
GROUP BY User_id
HAVING MIN(Invest_amount)>500000;

思路解析：因为是计算2017年每笔投资均大于50万的用户，所以从含有投资信息的投资表中SELECT User_id ，由因为是2017年，所以加WHERE YEAR(Created_at)='2017’过滤，
最后再用group by和having语句筛选出每笔投资均大于50万的用户

问题二的解答

SELECT a.User_id
FROM
(SELECT User_id, COUNT(DISTINCT Invest_Item) AS count1
FROM 
xiaoliu.cmn_investment_request
WHERE YEAR(Created_at)='2017'
GROUP BY User_id) a
INNER JOIN
(SELECT 
User_id, COUNT(DISTINCT Invest_Item) AS count2
FROM 
xiaoliu.cmn_investment_request
WHERE YEAR(Created_at)='2017'
AND Invest_Item IN ('CFH','AX')
GROUP BY User_id) b
ON a.User_id=b.User_id
AND a.count1=2
AND b.count2=2

思路解析：因为是计算2017年仅投资过CFH和AX产品的用户，所以有两个过滤条件，分别是YEAR(Created_at)='2017’和Invest_Item IN (‘CFH’,‘AX’)，并且要同时满足所以要根据用户 id 相同联结这两个过滤条件的表，最后由
a.User_id=b.User_id
AND a.count1=2
AND b.count2=2筛选出2017年仅投资过CFH和AX产品的用户

问题三的解答

SELECT SUM(C.Invest_amount)
FROM
xiaoliu.cmn_investment_request C
INNER JOIN
xiaoliu.dim_agent D
ON C.User_id=D.User_id
WHERE D.Agent_id='10002'
AND C.Created_at BETWEEN D.Start_date AND D.End_date;

思路解析：因为是计算归属10002业务员的投资金额，所以应该SELECT SUM(C.Invest_amount)通过WHERE D.Agent_id='10002’来过滤由投资表和业务员表根据用户 id 相同联结而成的表