新人发帖，分享DS、BIE、DE、SDE、TPM、PM面试经验

李浩泉

注册一亩三分地论坛，查看更多干货！

您需要登录才可以下载或查看附件。没有帐号？注册账号

x

本帖最后由李浩泉于 2020-6-15 20:33 编辑

新人不能回帖，只能在聊聊记录一下自己的思路。

感谢@anna51100 - 亚麻BIE电面

Q1. Find the number of the products available by market place.

Q2. Find the top 3 and bottom 3 product sold in Dec 2019.
Hint1：可否用join替代windows function提高效率？

Q3. Find the products in the marketplace 1 with at least 100 repeat customers in the last 1 month
Repeat customers is a customer who buys same products on 2 consecutive days

Q4. We have customers, their total purchases (dollar value) in last year (2019)
As a BIE, what visualization would you do to report to stakeholders about this.

wIInTer

Mark一下,做笔记

酥酥酥酥茶

LZ太良心了，本new grad看你的帖子真的很长知识

李浩泉

本帖最后由李浩泉于 2020-6-16 09:04 编辑

10. 这道题比较费时间，一次需要创建4个business metrics，除了第一很简单，后面三个都需要花一些时间。表结构越简单的，往往题目越难。需要LAG()

Date Source: visit log table
date，             visitor_ID
2020-03-15       545468
2020-03-15       289320
.....
2020-03-20       841312

Ouput:
      date    total_visitor，visitor_gain，visitor_loss，total_new_visitors

2020-03-15    101                0                   0                      0
2020-03-16    130                29                0                   29
2020-03-17    155                25                0                   54
2020-03-18    150                0                   5                   49
2020-03-19    135                0                15                   34
2020-03-20    160                25                0                   59

WITH CTE AS (
SELECT
date,
COUNT(visitor_ID) AS total_visitor,
LAG(COUNT(visitor_ID),1,0) OVER (ORDER BY date) AS lastday_total_visitor
FROM visit
GROUP BY date
),
CTE1 AS (
SELECT
date,
total_visitor,
CASE WHEN total_visitor - lastday_total_visitor > 0 THEN total_visitor - lastday_total_visitor
ELSE 0 END AS visitor_gain,
CASE WHEN lastday_total_visitor - total_visitor > 0 THEN lastday_total_visitor - total_visitor
ELSE 0 END AS visitor_loss
FROM CTE
),
CTE2 AS (
SELECT
date,
total_visitor,
visitor_gain,
visitor_loss,
visitor_gain - visitor_loss AS daily_change
FROM CTE1
)
SELECT
date,
total_visitor,
visitor_gain,
visitor_loss,
daily_change + (LAG((daily_change),1,0) OVER (ORDER BY date)) AS total_new_visitors
FROM CTE2

复制代码

李浩泉

SELECT
marketplace_id,
COUTN(Product_id) AS [products available]
FROM Product
WHERE discountinued_at IS NULL OR discountinued_at > GETDATE()

复制代码

Product_id is the primary key in the table of Product, duplication will not be a question, so no need to add the "distinct".

discountinued_at IS NULL, it will return products that have no expire date
discountinued_at > GETDATE(), it will return products that will be expired in the future but currently are still available.

李浩泉

WITH CTE AS (
SELECT Product.Product_id,
DENSE_RANK() OVER(ORDER BY ISNULL(SUM(quantity),0) ASC) AS RANK_ASC,
DENSE_RANK() OVER(ORDER BY ISNULL(SUM(quantity),0) DESC) AS RANK_DESC,
FROM Product LEFT JOIN Purchase ON Product.Product_id = Purchase.Product_id
WHERE YEAR(purchase_date) = 2019 AND MONTH(purchase_date) = 12
GROUP BY Product.Product_id
)
SELECT
Product_id AS [TOP 3],
RANK_DESC
FROM CTE
WHERE RANK_DESC <= 3
SELECT
Product_id AS [BOTTOM 3],
RANK_ASC
FROM CTE
WHERE RANK_ASC <= 3

复制代码

李浩泉

本帖最后由李浩泉于 2020-6-15 21:25 编辑

Q2b: Joins Solution (Not Sure if SQL allows order by in the where constraints, but in general you can select top 3 and bottom 3 distinct salaries and then join to the product by quantity )

WITH CTE AS (
SELECT Product.Product_id,ISNULL(SUM(quantity),0) AS quantity
FROM Product LEFT JOIN Purchase ON Product.Product_id = Purchase.Product_id
WHERE YEAR(purchase_date) = 2019 AND MONTH(purchase_date) = 12
)
SELECT
Product_id AS [TOP 3]
FROM CTE
WHERE quantity IN (
SELECT TOP3 DISTINCT quantity
FROM CTE
ORDER BY quantity DESC )
SELECT
Product_id AS [BOTTOM 3]
FROM CTE
WHERE quantity IN (
SELECT TOP3 DISTINCT quantity
FROM CTE
ORDER BY quantity ASC )

复制代码

李浩泉

本帖最后由李浩泉于 2020-6-15 21:43 编辑

通过@anna51100小姐姐的介绍，感觉亚麻厂的livecode就是一个共享桌面的虚拟白板，并不能运行和调试代码，所谓的技术面试，更看重的是理论基础，只要讲明白就可以通过。这和脸书的coderpad完全不一样，coderpad要运行和测试出结果，理论强代码运算不出结果也是不行的。我看过2018年的亚麻厂DE面试视频，好像那个时候用的还是hackerrank平台，和Leetcode差不多，不仅仅要出结果，而且后台还有多套test case。

用白板写Pseudocode，不会有硬伤，只要能说服面试官即可。坏处是，主观因素太多，感觉像写作文了。还是喜欢hackerrank，Leetcode，简单直接，不废话，对就对，错就是错。

微软的面试完全都是他们自己的软件，如果面试微软的BIE，DE，必须会用Power BI，SSIS等，更传统，看重的是具体软件工具的操作熟练程度。而不是考察基础的SQL，PYTHON，JAVA。

李浩泉

Q3. Find the products in the marketplace 1 with at least 100 repeat customers in the last 1 month.
Repeat customers is a customer who buys same products on 2 consecutive days

WITH CTE AS (
SELECT Product.Product_id,marketplace_id,customer_id,purchase_date
FROM Product LEFT JOIN Purchase ON Product.Product_id = Purchase.Product_id
WHERE marketplace_id = 1
AND DATEPART(m, DATEADD(m, -1, getdate())) = DATEPART(m, DATEADD(m, 0, purchase_date))
)
SELECT C1.Product_id
FROM CTE C1 INNER JOIN CTE C2 ON C1.Product_id = C2.Product_id
AND C1.customer_id = C2.customer_id AND DATEADD(DAY,1,C1.purchase_date) = C2.purchase_date
GROUP BY C1.Product_id
HAVING COUNT(C1.customer_id) >= 100

复制代码

李浩泉

本帖最后由李浩泉于 2020-6-15 22:04 编辑

Q3. Find the products in the marketplace 1 with at least 100 repeat customers in the last 1 month. Repeat customers is a customer who buys same products on 2 consecutive days。

@anna51100，面试的时候写成hard code。我们还是应该做好准备写dynamic。我记得我曾经的面试，当时就被中国程序员小哥哥面试官直接怼过，too much talking, too much questions, just focus on coding，do not waste time。

WITH CTE AS (
SELECT DISTINCT Product.Product_id,customer_id,purchase_date
FROM Product LEFT JOIN Purchase ON Product.Product_id = Purchase.Product_id
WHERE marketplace_id = 1
AND DATEPART(m, DATEADD(m, -1, getdate())) = DATEPART(m, DATEADD(m, 0, purchase_date))
)
SELECT C1.Product_id
FROM CTE C1 INNER JOIN CTE C2 ON C1.Product_id = C2.Product_id
AND C1.customer_id = C2.customer_id AND DATEADD(DAY,1,C1.purchase_date) = C2.purchase_date
GROUP BY C1.Product_id
HAVING COUNT(C1.customer_id) >= 100

复制代码

李浩泉

本帖最后由李浩泉于 2020-6-15 22:32 编辑

Q4. We have customers, their total purchases (dollar value) in last year (2019). As a BIE, what visualization would you do to report to stakeholders about this.

Correct me if I am wrong. We have 2 business metrics, one is the # of customers, the other is the # of sales in 2019. If I would present the two metrics to the management, I could

either

divide the total number of customers, for example let's say 1 million, into 10 subgroups, group 1 from 0 - 100K, group 2 from 100K - 200K and so on. To show the distribution of sales in different subgroups in a bar chart. According to the 20-80 rules, we can see how much our top 20% customers contribute the sales, is it over 80% or below. So we can focus on our top tier customer segmentation to leverage more resource in the next fiscal year.

or

divide the total sales, for example let's say 1 billion, into 5 groups , group A is from 0 - 200M, group by B is from 200M - 400M, by number of customers. It will show the distribution of customers by sales. if possible, we can also add gender or age dimensions to illustrate the full picture more clear.

李浩泉

本帖最后由李浩泉于 2020-6-16 02:45 编辑

刚才发题库的帖子被管理员移到面经版块了，我现在不能更新了，所以把题库放在这里：

亚麻SQL面试题库 #5

5a. find out the median price of sold products in Dec. 2019

SELECT AVG(unit_price) AS Median
FROM (
SELECT DISTINCT
product_id,
unit_price,
ROW_NUMBER() OVER(ORDER BY unit_price DESC) AS RN,
COUNT(ID) OVER() AS COUNTS
FROM orders
WHERE YEAR(Orders_date) = 2019 AND MONTH(Orders_date) = 12 ) T
WHERE RN BETWEEN COUNTS/2 AND COUNTS/2+1

复制代码

李浩泉

5b. find out the product with the highest unit price based on the most recent completed orders

WITH CTE AS (
SELECT product_id,MAX(order_date) AS Most_Recent_Date
FROM orders
WHERE status = 'Completed'
GROUP BY product_id )
SELECT product_id,unit_price
FROM (
SELECT CTE.product_id,unit_price,
DENSE_RANK() OVER(ORDER BY unit_price DESC) AS RK
FROM CTE INNER JOIN orders ON CTE.product_id = orders.product_id AND CTE.Most_Recent_Date = orders.order_date ) T
WHERE RK = 1

复制代码

新人发帖，分享DS、BIE、DE、SDE、TPM、PM面试经验

注册一亩三分地论坛，查看更多干货！

评分

相关帖子

本帖被以下淘专辑推荐:

评分

评分

评分

评分

评分

浏览过的版块