𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the customers who placed orders on three or more consecutive days.

Assume the table structure:
orders(order_id, customer_id, order_date)

𝗠𝗲: Challenge accepted! 💪

WITH consecutive_orders AS (
SELECT
customer_id,
order_date,
DATE_SUB(
order_date,
INTERVAL ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
) DAY
) AS grp
FROM (
SELECT DISTINCT
customer_id,
order_date
FROM orders
) t
)
SELECT
customer_id
FROM consecutive_orders
GROUP BY
customer_id,
grp
HAVING COUNT(*) >= 3;

💡 Explanation:
This query identifies sequences of consecutive order dates for each customer.

• ROW_NUMBER() assigns a sequence number to each order date per customer.
• Subtracting the row number from the order date creates the same grp value for consecutive dates.
• GROUP BY customer_id, grp groups each consecutive streak.
• **HAVING COUNT(*) >= 3** returns customers with a streak of at least three consecutive days.

This question tests your understanding of: Common Table Expressions (CTEs), Window Functions (ROW_NUMBER()), Gaps and Islands Problem, Date Arithmetic.

🎯 Expected Output Example
Customer ID
101
205

(Customer 101 ordered on June 1, 2, and 3. Customer 205 ordered on July 10, 11, and 12.)

🚀 Tip for SQL Job Seekers:
The Gaps and Islands pattern is one of the most advanced and frequently discussed SQL interview topics.

Master it for solving:
• Consecutive login days
• Consecutive purchases
• Attendance streaks
• Consecutive transactions
• User activity analysis

Being comfortable with this pattern can set you apart in technical interviews.

❤️ React with ❤️ for more SQL interview challenges!