SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…