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

[1,2,3,4,5], [2,3,4,5,6] compare mean and variance

Mean = (1+2+3+4+5)/5 = 3
Variance = ((1-3)^2 + (2-3)^2 + (3-3)^2 + (4-3)^2 + (5-3)^2) / 5 = ( 4 + 1 + 0 + 1 + 4 )/5 = 2

Mean = (2+3+4+5+6)/5 = 4
Variance = ((2-4)^2 + (3-4)^2 + (4-4)^2 + (5-4)^2 + (6-4)^2) / 5 = ( 4 + 1 + 0 + 1 + 4 )/5 = 2

1. SELECT
   
2.     product_id,
   
3.     CASE WHEN total_qty < 2 THEN 'low' ELSE 'not low' END AS CATEGORY
   
4. FROM product p1
   
5. INNER JOIN (
   
6.    Select product_id, sum(qty) as total_qty
   
7.    From product p
   
8.    INNER JOIN order_detail od ON p.product_id=od.product_id
   
9.    WHERE od.order_id NOT IN
   
10.     (
    
11.     select order_id from orders
    
12.     where order_datetime >= datediff(year, -1, getdate())
    
13.     )
    
14.     GROUP BY product_id ) T ON p1.prodcut_id = T.product_id

复制代码

感谢分享！

李浩泉 发表于 2020-7-19 03:07
Create Make Business Metrics: [total cost], [total access page per customer]

[total access page per customer] 我之前的代码有问题，现在更新一下：

1. SELECT
   
2. first_name + ' ' + last_name AS customer,
   
3. SUM(ISNULL(access_page,0)) AS [total access page per customer]
   
4. FROM customer
   
5. LEFT JOIN browse_history ON customer.id = browse_history.customer_id
   
6. GROUP BY first_name + ' ' + last_name
   
7. ORDER BY 2 DESC

复制代码

为空为零也要输出，疏忽大意了，还好及时发现：

.

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

复制代码

renee1 发表于 2020-7-17 15:47
谢谢楼主分享， 能说一下fb 的题吗？

也是要面fb的de岗么

xiaoyuetu 发表于 2020-7-24 08:57
也是要面fb的de岗么

是的，在约面试