← All tasksSQL · Junior

Users without an email

Find active users (not deleted: deleted_at is empty) who have no email: it's NULL, an empty string, or only spaces. Columns id and name, ordered by id ascending.

Tables

CREATE TABLE users (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  email      text,
  deleted_at timestamp
);

SELECT id, name
FROM users
WHERE email = NULL;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO users VALUES
  (1, 'Ann', 'ann@mail.com', NULL),
  (2, 'Bob', NULL, NULL),
  (3, 'Cid', '', NULL),
  (4, 'Dee', NULL, '2024-05-01');

-- whitespace-only email counts as missing
INSERT INTO users VALUES
  (1, 'Eva', '   ', NULL),
  (2, 'Max', 'max@mail.com', NULL);

-- everyone is fine
INSERT INTO users VALUES (1, 'Ann', 'a@b.c', NULL);