Sweaty Imposter

Marking Inactive Users With an UPDATE

SQLUpdate with JOINSeniorStaffUpdated
In PostgreSQL, set status = 'inactive' for every user who has placed no orders in the last 365 days (including users who have NEVER placed an order). Update only users currently not already inactive.

Schema

CREATE TABLE users (
  id      BIGINT PRIMARY KEY,
  status  TEXT NOT NULL
);

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  placed_at   TIMESTAMPTZ NOT NULL
);

Drill This: Your Query First

The Correct Query

Option (b)

UPDATE users u
SET status = 'inactive'
WHERE u.status <> 'inactive'
  AND NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
      AND o.placed_at >= NOW() - INTERVAL '365 days'
  );
NOT EXISTS asks 'does this user have ANY recent order?' and negates it, which naturally includes users with zero orders. (a) only marks users who DO have an old order and still joins to the orders table, missing never-ordered users entirely. (c) works only if orders.user_id is guaranteed NOT NULL: it is here, but the bigger issue is (d) would never match never-ordered users because MAX returns NULL and NULL < anything is UNKNOWN, not true.

Going Deeper

Mass UPDATEs should be idempotent: the 'AND status <> target' guard avoids writing the same row repeatedly across cron runs, which matters for replication lag and audit logs.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

UPDATE users u
SET status = 'inactive'
FROM orders o
WHERE o.user_id = u.id
  AND o.placed_at < NOW() - INTERVAL '365 days'
  AND u.status <> 'inactive';

Option (c)

UPDATE users
SET status = 'inactive'
WHERE id NOT IN (
  SELECT user_id FROM orders
  WHERE placed_at >= NOW() - INTERVAL '365 days'
);

Option (d)

UPDATE users u
SET status = 'inactive'
WHERE (SELECT MAX(o.placed_at) FROM orders o WHERE o.user_id = u.id)
      < NOW() - INTERVAL '365 days';