【数据分析面试】45.亚马逊SQL订单数据问题

亚马逊SQL面试问题

在最近的一次Amazon数据分析师面试中，要求候选人编写SQL查询获取一下数据

1. 昨天订购了多少商品？
2. 在过去7天（包括今天），每个类别订购了多少商品？
3. 获取每位客户在他们下订单的每个日期的最早 order_id
4. 找出每位客户在他们下两次或更多订单的每个日期的第二早的 order_id

示例数据：

orders 表：

order_id	customer_id	order_datetime	item_id	order_quantity
O-001	42489	2024-05-14 04:35:22	C004	3
O-005	11733	2024-05-02 11:48:35	C005	1
O-005	11733	2024-05-02 11:48:35	C008	1
O-006	83167	2024-05-03 02:52:07	C012	2

items 表：

item_id	item_category
C004	Books
C005	Books
C006	Apparel
C007	Electronics
C008	Electronics

答案

1. 昨天订购了多少商品？

   SELECT
       SUM(order_quantity) AS units_ordered_yesterday
   FROM
       orders
   WHERE
       DATE(order_datetime) = CURDATE() - INTERVAL 1 DAY;
   

2. 在过去7天（包括今天），每个类别订购了多少商品？

   SELECT
       i.item_category,
       COALESCE(SUM(o.order_quantity), 0) AS units_ordered
   FROM
       items i
   LEFT JOIN
       orders o
   ON
       i.item_id = o.item_id
   AND 
       DATE(o.order_datetime) >= CURDATE() - INTERVAL 6 DAY 
   AND 
       DATE(o.order_datetime) <= CURDATE()
   GROUP BY
       i.item_category;
   

3. 获取每位客户在他们下订单的每个日期的最早 order_id：

   SELECT
       customer_id,
       DATE(order_datetime) AS order_date,
       MIN(order_id) AS earliest_order_id
   FROM
       orders
   GROUP BY
       customer_id,
       DATE(order_datetime);
   

4. 找出每位客户在他们下两次或更多订单的每个日期的第二早的 order_id：

WITH ranked_orders AS (
       SELECT
           customer_id,
           DATE(order_datetime) AS order_date,
           order_id,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id,DATE(order_datetime)
               ORDER BY order_datetime, order_id
           ) AS order_rank
       FROM
           (SELECT 
	           DISTINCT(order_id) as order_id, 
	           customer_id,
	           order_datetime 
           FROM 
	           orders
           )new_orders
   )
   SELECT
       customer_id,
       order_date,
       order_id AS second_earliest_order_id
   FROM
       ranked_orders
   WHERE
       order_rank = 2;

更多详细答案可关注公众号查阅。