Customers with more than 2 orders

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 12 minutes. Part of the Pro drill bank.

The sales team wants a list of repeat customers, which means customers who have placed more than 2 orders. You have customers (names) and lb_orders (1 row per order). Find every customer with more than 2 rows in lb_orders. Return customer_id, name and order_count (how many orders they have). Sort by order_count from the highest, then by customer_id.

Requirements

• Join customers to lb_orders on customer_id.
• Count the orders of each customer.
• Keep only customers whose count is greater than 2. Exactly 2 does not qualify.
• Sort by order_count descending, then customer_id.

Constraints

• The tables are customers and lb_orders.
• Count order rows with COUNT(*). Do not count distinct products.
• Return only these 3 columns, in this order: customer_id, name, order_count.

Examples

Input: customers customer_id | name C01 | Ana C02 | Ben C03 | Cam lb_orders (order counts) customer_id | order_count C01 | 3 C02 | 1 C03 | 2 Output: customer_id | name | order_count C01 | Ana | 3 Why this passes: Only customers clearing the > 2 threshold appear.

Topics: lakebench, sql, having, join.

More SQL interview questions · All interview problems · Learn data engineering