2020年 微软 亚麻 脸书 Capital ONE DE类编程题

楼主， 看不到你发的内容， 需要密码。

棒棒棒 必须收藏

Create Make Business Metrics: [total cost], [total access page per customer]

1. SELECT
   
2. [total cost],
   
3. [total access page per customer]
   
4. FROM
   
5. ( SELECT SUM(unit_cost * quantity) AS [total cost]
   
6.   FROM order ) T1 ,
   
7. ( SELECT
   
8.   SUM(access_page)/COUNT(DISTINCT customer_id) AS [total access page per customer]
   
9.   FROM browse_history ) T2

复制代码

需要注意保留小数两位：

1. SELECT
   
2. [total cost],
   
3. [total access page per customer]
   
4. FROM
   
5. ( SELECT CAST(SUM(unit_cost * quantity) AS NUMERIC(10,2)) AS [total cost]
   
6.   FROM order ) T1 ,
   
7. ( SELECT
   
8.   CAST(SUM(access_page)*1.00/COUNT(DISTINCT customer_id) AS NUMERIC(10,2)) AS [total access page per customer]
   
9.   FROM browse_history ) T2

复制代码

2. output: CN, US, JP, CA total access pages, and most used device, 写两个简单的CTE，最后join在一起就行，也可以写in line query，个人口味，不过面试官会问CTE和sub-query的优缺点。所以最好一个写成CTE，一个写成 in line sub-query，然后直接告诉面试官，我这么写就是想告诉你，我两个都没问。

1. 
   
2. WITH CTE AS
   
3. ( SELECT
   
4.    country_code,
   
5.    device_type,
   
6.    DENSE_RANK() OVER(PARTITION BY country_code ORDER BY COUNT(customer_id) DESC) AS RK
   
7.   FROM browse_history
   
8.   WHERE country_code IN ('CN','US','JP','CA')
   
9.   GROUP BY country_code,device_type )
   
10. 
    
11. SELECT
    
12. T.[Country],
    
13. T.[total access page],
    
14. CTE.device_type
    
15. FROM (
    
16.        SELECT
    
17.        name AS [Country],
    
18.        browse_history.country_code,
    
19.        SUM(access_page) AS [total access page],
    
20.        FROM browse_history
    
21.        LEFT JOIN country ON browse_history.country_code = country.country_code
    
22.        WHERE browse_history.country_code IN ('CN','US','JP','CA')
    
23.        GROUP BY browse_history.country_code,name ) T
    
24. LEFT JOIN CTE ON T.country_code = CTE.country_code AND CTE.RK = 1
    

复制代码

3. output: top customer by sales + most expensive product + average price (weighted)

1. SELECT
   
2. T1.customer AS [top customer by sales],
   
3. T2.product AS [most expensive product],
   
4. T3.price AS [average price (weighted)]
   
5. ( SELECT DISTINCT
   
6.   first_name + last_name AS customer,
   
7.   DENSE_RANK() OVER(ORDER BY SUM(unit_cost * quantity) DESC) AS RK
   
8.   FROM browse_history
   
9.   LEFT JOIN order ON browse_history.customer_id = order.customer_id
   
10.   LEFT JOIN customer ON browse_history.customer_id = customer.customer_id
    
11.   GROUP BY first_name + last_name ) T1,
    
12. ( SELECT DISTINCT product,
    
13.   DENSE_RANK() OVER(ORDER BY unit_cost DESC) AS RK
    
14.   FROM order ) T2,
    
15. ( SELECT ROUND(SUM(unit_cost*quantity)/SUM(quantity),2) AS price
    
16.   FROM order ) T3
    
17. WHERE T1.RK = 1 AND T2.RK = 1

复制代码

Write a SQL to get the cumulative sum of an employee's salary over a period of 3 months but exclude the most recent month.

The result should be displayed by 'Id' ascending, and then by 'Month' descending.

1. WITH CTE AS
   
2. (
   
3. SELECT Id, MAX(Month) AS Month
   
4. FROM Employee
   
5. GROUP BY Id
   
6. )
   
7. 
   
8. SELECT
   
9. Employee.Id,
   
10. Employee.Month,
    
11. SUM(Salary) OVER(PARTITION BY Employee.Id ORDER BY Employee.Month ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS Salary
    
12. FROM Employee
    
13. INNER JOIN CTE ON Employee.Id = CTE.Id AND Employee.Month <> CTE.Month
    
14. ORDER BY 1 ASC, 2 DESC

复制代码

统计：（比GMAT简单）

A candidate is selected for interview for 3 posts.The number of candidates for the first, second and third posts are 3,3 and 3 respectively. What is the probability of getting at least one post?

The probability the candidate does not get an offer from the first interview is 2/3. The probability she doesn't get an offer from the second is 2/3, and the probability she doesn't get an offer from the third is 2/3. So the probability she does not get an offer at all is 2/3*2/3*2/3 = 8/27. Thus the probability she gets at least one offer is 19/27.