https://www.1point3acres.com/bbs/interview/amazon-data-science-478865.html 亚麻BIE physical retail seatle 1)一个manuafacture 有三种product 求每天三种产品里最早生产的三个产品 product_name, product_date, type

SELECT * FROM
(SELECT *, ROW_NUMBER() OVER(PARTITION BY Type ORDER BY product_date ASC) AS num
FROM Orders)
WHERE num <=3

TOP N ROWS:

WITH orders_ranked as ( SELECT-baidu 1point3acres date_trunc(‘month’,datetime)::date as month, , row_number() over (partition by date_trunc(‘month’,datetime) order by amount desc, datetime) FROM orders_cleaned ) SELECT . check 1point3acres for more.

FROM orders_ranked WHERE row_number<=2 ORDER BY 1

2) 用group by还是 rank 的sql run的时间最短， total 1B row数据，两种情况：

1. 3 个类型产品，每个类型333333333 rows
2. 1000个类型产品，每个类型1000000 row

group by比较快。例子，一摞纸，要是rank的话要一张一张看数字。group by 就直接归类。

3)day-to-day production change: d-o-d% = (current_day_amt - Previous_day_amt)/(current_day_amt) 这里用到window function and lag()

SELECT ROUND((current_day_amt - Previous_day_amt)/(current_day_amt) ,2) 
FROM(
SELECT current_day_amt, LAG(current_day_amt,1) OVER (ORDER BY DAY) as previous_day_amt
FROM (
SELECT COUNT(PRODUCT_NAME) OVER(PARTITION BY product_date) AS current_day_amt, *
FROM..) a) b

Calculate Revenue: 100(m1-m0)/m0

WITH monthly_revenue as ( SELECT date_trunc(‘month’,datetime)::date as month, state, sum(amount) as revenue FROM orders GROUP BY 1,2 ) ,prev_month_revenue as ( SELECT , lag(revenue) over (partition by state order by month) as prev_month_revenue FROM monthly_revenue ) SELECT , round(100.0*(revenue-prev_month_revenue)/prev_month_revenue,1) as revenue_growth FROM prev_month_revenue ORDER BY 2,11.duplicate data

FB DS:

What is the default order of ORDER BY? Ascending How do you combine the result-set of two statement? UNION & UNION ALL, depends on requirement What is the value of 1+ null? null