Users Who Returned Within 7 Days

SQLDate arithmeticMidSeniorUpdated Nov 10, 2026

In PostgreSQL, return the user_ids of people who started at least one session strictly AFTER their signup and within 7 days of signing up (i.e. signup < session <= signup + 7 days).

Schema

CREATE TABLE signups (
  user_id    BIGINT PRIMARY KEY,
  signed_up  TIMESTAMPTZ NOT NULL
);

CREATE TABLE sessions (
  id         BIGINT PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  started_at TIMESTAMPTZ NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT DISTINCT s.user_id
FROM signups s
JOIN sessions x ON x.user_id = s.user_id
WHERE x.started_at >  s.signed_up
  AND x.started_at <= s.signed_up + INTERVAL '7 days';

Postgres supports INTERVAL arithmetic directly, and the half-open inequalities match the spec exactly. (a) compares a TIMESTAMPTZ subtraction (which yields an INTERVAL) to a bare number, invalid. (c) uses DATEDIFF, which is SQL Server / MySQL, not Postgres. (d) adds a bare integer 7 to a TIMESTAMPTZ (error) and uses BETWEEN, which includes both endpoints (so signup itself counts).

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT DISTINCT s.user_id
FROM signups s
JOIN sessions x ON x.user_id = s.user_id
WHERE x.started_at - s.signed_up < 7;

Option (c)

SELECT DISTINCT s.user_id
FROM signups s
JOIN sessions x ON x.user_id = s.user_id
WHERE DATEDIFF(day, s.signed_up, x.started_at) BETWEEN 0 AND 7;

Option (d)

SELECT s.user_id
FROM signups s
LEFT JOIN sessions x ON x.user_id = s.user_id
WHERE x.started_at BETWEEN s.signed_up AND s.signed_up + 7;