🧠 SQL Interview Question (Tricky & Logic-Based)
📌

logins(user_id, login_date)

❓ Ques :

👉 Find users who logged in for 3 or more consecutive days.

🧩 How Interviewers Expect You to Think

• Understand consecutive date logic
• Use date arithmetic smartly
• Create groups using row-number difference trick
• Avoid complex self-joins
• Aggregate after forming streak groups

💡 SQL Solution

WITH numbered_logins AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date
) AS rn
FROM logins
),
grouped_logins AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM numbered_logins
)

SELECT
user_id
FROM grouped_logins
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

🔥 Why this question is powerful:

• Tests advanced window function usage
• Checks understanding of gaps & islands concept
• Evaluates real-world product analytics thinking
• Very common in growth / engagement analytics interviews

❤️ React if you want more scenario-based SQL questions