DB Exercises
0
← Exercises/
Window Functions•4 of 14
PrevNext
Window FunctionsAdvancedPRO

User Login Inactivity Gap with LEAD()

Scenario

SaaS product engagement tracking requires detecting expanding gaps between user logins. An increasing interval between active sessions is the single strongest precursor to user churn.

Task
For each login event in `user_logins`, look ahead to the user's next login date using `LEAD()`. Calculate: 1. `user_id` 2. `login_date` 3. `next_login_date`: The user's subsequent login date using `LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date)` 4. `days_until_next_login`: (`LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date) - login_date`) Order results by `user_id` ascending, then by `login_date` ascending.
Expected Output Format

User login history with next session date and gap in days.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “User Login Inactivity Gap with LEAD()” SQL Problem

Business Scenario

SaaS product engagement tracking requires detecting expanding gaps between user logins. An increasing interval between active sessions is the single strongest precursor to user churn.

Expected Output & Criteria

User login history with next session date and gap in days.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE user_logins (
        login_id INT PRIMARY KEY,
        user_id INT NOT NULL,
        login_date DATE NOT NULL
      );

Target SQL Clauses & Operators

LEADOVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Window Functions Track→