📣 黑五价提前开抢|VIP一年通行证5折,蓝莓、Offer多多同步特惠
楼主: 李浩泉
跳转到指定楼层
上一主题 下一主题
收起左侧

[找工就业] 2020年 微软 亚麻 脸书 Capital ONE DE类编程题

🔗
renee1 2020-7-18 09:59:12 | 只看该作者
全局:
楼主, 看不到你发的内容, 需要密码。
回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-18 14:13:40 | 只看该作者
本帖为密码帖 ,请输入密码 
回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 01:53:06 | 只看该作者
本帖为密码帖 ,请输入密码 
回复

使用道具 举报

🔗
safun1995 2020-7-19 02:25:19 | 只看该作者
全局:
棒棒棒 必须收藏
回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 03:07:56 | 只看该作者
全局:
Create Make Business Metrics: [total cost], [total access page per customer]


  1. SELECT
  2. [total cost],. Χ
  3. [total access page per customer]
  4. FROM . 1point 3acres
  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
复制代码
. .и

回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 04:35:40 | 只看该作者
全局:
-baidu 1point3acres
需要注意保留小数两位:
. check 1point3acres for more.
  1. SELECT
    .1point3acres
  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
复制代码




回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 05:31:38 | 只看该作者
全局:
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. SELECT
  11. T.[Country],
  12. T.[total access page],. 1point3acres
  13. CTE.device_type
  14. FROM (
  15.        SELECT
  16.        name AS [Country],
  17.        browse_history.country_code,
  18.        SUM(access_page) AS [total access page], ..
  19.        FROM browse_history
  20.        LEFT JOIN country ON browse_history.country_code = country.country_code. check 1point3acres for more.
  21.        WHERE browse_history.country_code IN ('CN','US','JP','CA')
  22.        GROUP BY browse_history.country_code,name ) T
  23. LEFT JOIN CTE ON T.country_code = CTE.country_code AND CTE.RK = 1
复制代码


回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 06:47:46 | 只看该作者
全局:

3. output: top customer by sales + most expensive product + average price (weighted)
. .и
  1. SELECT . 1point 3acres
  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,. 1point 3acres
  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
复制代码

. .и
回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 09:53:47 | 只看该作者
全局:

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 -baidu 1point3acres
  2. (. Waral dи,
  3. SELECT Id, MAX(Month) AS Month
  4. FROM Employee
  5. GROUP BY Id
  6. )
  7. . check 1point3acres for more.
  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.1point3acres
  13. INNER JOIN CTE ON Employee.Id = CTE.Id AND Employee.Month <> CTE.Month
  14. ORDER BY 1 ASC, 2 DESC
复制代码


回复

使用道具 举报

🔗
 楼主| 李浩泉 2020-7-19 10:49:21 | 只看该作者
全局:
统计:(比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?. check 1point3acres for more.
. Χ
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.
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号
隐私提醒:
  • ☑ 禁止发布广告,拉群,贴个人联系方式:找人请去🔗同学同事飞友,拉群请去🔗拉群结伴,广告请去🔗跳蚤市场,和 🔗租房广告|找室友
  • ☑ 论坛内容在发帖 30 分钟内可以编辑,过后则不能删帖。为防止被骚扰甚至人肉,不要公开留微信等联系方式,如有需求请以论坛私信方式发送。
  • ☑ 干货版块可免费使用 🔗超级匿名:面经(美国面经、中国面经、数科面经、PM面经),抖包袱(美国、中国)和录取汇报、定位选校版
  • ☑ 查阅全站 🔗各种匿名方法

本版积分规则

>
快速回复 返回顶部 返回列表