SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 14 minutes. Part of the Pro drill bank.
customer_orders has one row per order. A customer is new on the date of their first ever order and repeat on any later date they order. A customer who orders several times on their first day is new once and never repeat that day. For each order_date, return the number of distinct new customers and distinct repeat customers. Columns: order_date, new_customers, repeat_customers. Order by order_date.
Input: customer_orders order_id | customer_id | order_date 1 | A | 2024-02-01 2 | B | 2024-02-01 3 | A | 2024-02-02 4 | C | 2024-02-02 5 | C | 2024-02-02 6 | B | 2024-02-03 7 | A | 2024-02-03 8 | D | 2024-02-03 Output: order_date | new_customers | repeat_customers 2024-02-01 | 2 | 0 2024-02-02 | 1 | 1 2024-02-03 | 1 | 2 Why this passes: Feb 1: A and B are new. Feb 2: A returns, C is new (two orders, one customer). Feb 3: B and A return, D is new.
Input: customer_orders order_id | customer_id | order_date 1 | A | 2024-02-01 2 | A | 2024-02-02 Output: order_date | new_customers | repeat_customers 2024-02-01 | 1 | 0 2024-02-02 | 0 | 1 Why this passes: A new customer on day 1 who returns on day 2.
Topics: lakebench, sql, first order, distinct, customers.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: For each order date, how many customers ordered for the first time and how many were returning.
`customer_orders` has one row per order. A customer is **new** on the date of their first ever order and **repeat** on any later date they order. A customer who orders several times on their first day is new once and never repeat that day. For each `order_date`, return the number of distinct new customers and distinct repeat customers. Columns: `order_date`, `new_customers`, `repeat_customers`. Order by `order_date`.