🧠 SQL Interview Question (Moderate–Tricky & Retention Analysis)
📌

subscriptions(user_id, start_date, end_date)

❓ Ques :

👉 Find users who renewed their subscription immediately after the previous one ended (no gap between subscriptions).

🧩 How Interviewers Expect You to Think

• Sort subscriptions by start_date for each user
• Use a window function to access the previous subscription end date
• Check if the next start_date equals the previous end_date

💡 SQL Solution

WITH sub_cte AS (
SELECT
user_id,
start_date,
end_date,
LAG(end_date) OVER (
PARTITION BY user_id
ORDER BY start_date
) AS prev_end_date
FROM subscriptions
)

SELECT DISTINCT user_id
FROM sub_cte
WHERE start_date = prev_end_date;

🔥 Why This Question Is Powerful

• Tests ability to analyze subscription lifecycle data
• Evaluates knowledge of window functions for sequential comparisons
• Similar logic used in retention and churn analysis

❤️ React if you want more real interview-level SQL questions like this. 🚀