Interview Drills · LAG() · date arithmetic
23. Consecutive-day logins
Concept: LAG() · date arithmeticshow
LAG(col) returns col from the previous row in the window and LEAD(col) from the next. With PARTITION BY user_id ORDER BY login_date, LAG(login_date) is that user's previous login. In Postgres, subtracting two DATE values gives the gap in whole days.
Example (different data):
SELECT day, price, price - LAG(price) OVER (ORDER BY day) AS change FROM stock_prices;
Your task
Product wants to know which users formed a habit: anyone who logged in on two consecutive calendar days at least once.
Return the distinct user_id of every user with logins on two consecutive days. Sort by user_id.
Starting Postgres in your browser…
Checking your access…