Users Who Have Converted — Subqueries in WHERE in SQL

The problem

Streamhub's growth team is analysing revenue-generating users for a targeting initiative.

Write a query to return the user ID and name for every user who has made at least one conversion.

Assumptions:

The users table contains every account on the platform.
The conversions table records each paid conversion; user_id identifies the user.
A user with multiple conversions appears once in the result.

Output:

One row per converting user, with columns id and name.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name
FROM
  users
WHERE
  id IN (
    SELECT
      user_id
    FROM
      conversions
  )

The shape

The list of converting user IDs lives inside the conversions table. IN (subquery) plugs that list straight into the outer WHERE so the filter stays current without anyone maintaining a hardcoded set.

Clause by clause

SELECT id, name FROM users reads every account on Streamhub. The filter will narrow it to the converters.
WHERE id IN (SELECT user_id FROM conversions) is the membership test. PostgreSQL runs the inner query, collects every user_id recorded against a conversion, and keeps an outer user row only when its id appears in that set. A user with five conversions still surfaces once. IN asks whether the value appears at least once on the inner side, not how many times, so the result is one row per converting user instead of one row per conversion.

Why this and not a hardcoded list

The growth team's targeting list shifts every time a new conversion fires. A literal-list IN ('alice', 'bob', ...) would be stale within minutes and wrong by the end of the day. The subquery form recomputes itself: whatever rows are in conversions at query time is what the membership check uses.

You practiced IN with a subquery in a different domain. The same answer comes out of a JOIN + DISTINCT or an EXISTS subquery — different scaffolds for the same set-membership question.

Return the user ID and name for every user who has made at least one conversion

The shape

Clause by clause

Why this and not a hardcoded list

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.