Conversions with Total Revenue — Scalar Subqueries in SQL

The problem

Streamhub's finance team wants to see each conversion record alongside the total revenue across all conversions.

Write a query to return the user ID, the conversion amount, and the combined total for every conversion record.

Assumptions:

The conversions table records each paid conversion.
The combined total (SUM(amount)) is computed over every row in conversions — the same value appears in the third column of every row.

Output:

One row per conversion record, with columns user_id, amount, and total_revenue.

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
  user_id,
  amount,
  (
    SELECT
      SUM(amount)
    FROM
      conversions
  ) AS total_revenue
FROM
  conversions

The shape

Same per-row-plus-overall pattern as the orders dashboard, applied to Streamhub's conversion revenue. (SELECT SUM(amount) FROM conversions) resolves to a single total — 8440 — and the outer query slots that figure next to every conversion record's own user_id and amount.

Clause by clause

SELECT user_id, amount returns the two row-level columns. These change with every output row.
(SELECT SUM(amount) FROM conversions) AS total_revenue is the scalar subquery. PostgreSQL runs the inner SELECT once, gets a single value (the combined total across every conversion record), and writes that value into every output row's third column. The subquery doesn't know which outer row is being emitted; it computes the same total regardless.
FROM conversions is the source. Both the outer query and the subquery read from conversions, but at different grains. The outer query reads every row once; the subquery collapses every row to one number.

Why this and not a join with a one-row summary table

A related shape some learners reach for: compute the total in a separate query, then "attach" it to every row through some join. The scalar-subquery form does the same job inline, with less structure. The single value is computed once at execution time and treated like a literal in the outer SELECT list. No join key, no auxiliary table — just an expression that resolves to a number, used wherever a number is valid.

You practiced the per-row-plus-overall pattern in a different domain. The structural shape (SELECT col1, col2, (SELECT AGG(col3) FROM table) FROM table) is what makes "this row's value, in context" reports possible without a second pass.

Return the user ID, the conversion amount, and the combined total for every conversion record

The shape

Clause by clause

Why this and not a join with a one-row summary table

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