Ordered in every month of Q1 2024

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

A loyalty team wants to find customers who were active every month in the first quarter of 2024. That means they placed at least one order in January, in February and in March 2024. From lb_orders, find the customers who have at least one order in each of the 3 months. Only orders from January to March 2024 count. Return customer_id, sorted from the smallest.

Requirements

• Look only at orders from 1 January 2024 up to the end of 31 March 2024.
• Count how many different months each customer ordered in.
• Keep customers who ordered in all 3 months.
• Return only customer_id, sorted ascending.

Constraints

• The table is lb_orders.
• Use a date range: order_date >= DATE '2024-01-01' AND order_date < DATE '2024-04-01'.
• Use HAVING COUNT(DISTINCT MONTH(order_date)) = 3.

Examples

Input: lb_orders (2024 Q1 month coverage) customer_id | order_month C01 | 1 C01 | 2 C01 | 3 C03 | 1 Output: customer_id | C01 | Why this passes: Need three distinct months in Q1 2024, not 3 orders in a single month.

Topics: lakebench, sql, cohort, having.

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