Average Time to Convert — Sessionization and Funnels in SQL

The problem

Scenario: Streamhub's product team wants to know not just how many users reached the upgrade-intent step, but how long it typically takes them to get there.

Task: Write a query to return two metrics: the count of users who performed an 'upgrade_clicked' event strictly after a prior 'page_view' (users_converted), and the average elapsed time in seconds from each such user's earliest 'page_view' to their earliest qualifying 'upgrade_clicked' (avg_seconds_to_convert).

Assumptions:

A user qualifies as converted when they have at least one 'upgrade_clicked' event strictly later than their earliest 'page_view'.
For a converted user, the time-to-convert is the elapsed time, in seconds, between their earliest 'page_view' and their earliest qualifying 'upgrade_clicked'.
The avg_seconds_to_convert is the average time-to-convert across every converted user.

Output:

One row, holding the two metrics.
Columns in this order: users_converted, avg_seconds_to_convert.

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

WITH
  step_1 AS (
    SELECT
      user_id,
      MIN(occurred_at) AS step_1_time
    FROM
      events
    WHERE
      event_type = 'page_view'
    GROUP BY
      user_id
  ),
  upgrade_events AS (
    SELECT
      user_id,
      occurred_at
    FROM
      events
    WHERE
      event_type = 'upgrade_clicked'
  ),
  step_2 AS (
    SELECT
      ue.user_id,
      MIN(ue.occurred_at) AS step_2_time
    FROM
      upgrade_events ue
      JOIN step_1 s ON s.user_id = ue.user_id
    WHERE
      (ue.occurred_at - s.step_1_time) > INTERVAL '0 seconds'
    GROUP BY
      ue.user_id
  )
SELECT
  COUNT(*) AS users_converted,
  AVG(
    EXTRACT(
      epoch
      FROM
        (s2.step_2_time - s1.step_1_time)
    )
  ) AS avg_seconds_to_convert
FROM
  step_2 s2
  JOIN step_1 s1 ON s1.user_id = s2.user_id

The shape

A funnel with a velocity measurement is the standard two-CTE funnel plus a join back to step 1 at the outer query, so the result row carries both timestamps and the average time-to-convert can be computed in the same pass. EXTRACT(epoch FROM ...) converts the interval between the two timestamps into seconds, which is the unit the prompt asks for.

Clause by clause

step_1 selects user_id, MIN(occurred_at) AS step_1_time FROM events WHERE event_type = 'page_view' GROUP BY user_id. One row per user who ever viewed a page.
upgrade_events pre-filters events to event_type = 'upgrade_clicked' so the next join stays narrow.
step_2 joins upgrade_events to step_1 on user_id and keeps only the upgrade clicks where (ue.occurred_at - s.step_1_time) > INTERVAL '0 seconds', which enforces the funnel's time ordering. MIN(ue.occurred_at) AS step_2_time picks each converted user's earliest qualifying upgrade.
The final SELECT COUNT(*) AS users_converted, AVG(EXTRACT(epoch FROM (s2.step_2_time - s1.step_1_time))) AS avg_seconds_to_convert FROM step_2 s2 JOIN step_1 s1 ON s1.user_id = s2.user_id joins step 2 back to step 1 by user_id, which puts both timestamps on the same row. The subtraction produces an interval per converted user. EXTRACT(epoch FROM ...) returns the number of seconds in that interval as a numeric value. AVG over those per-user numbers gives the average time-to-convert across the 28 converted users.

Why this and not subtracting timestamps and averaging the interval directly

PostgreSQL will let you write AVG(s2.step_2_time - s1.step_1_time), and you'll get an interval back. That interval would be a correct duration but in interval form (something like 13 days 06:01:32), not a seconds count. The prompt asks for avg_seconds_to_convert as a numeric value, so EXTRACT(epoch FROM ...) has to wrap the subtraction before AVG runs. Going through epoch seconds also avoids subtle arithmetic differences in how intervals average across months and years.

The trap

It is tempting to average the per-user seconds inside step_2 itself by selecting AVG(...) in the same CTE that computes MIN(occurred_at). That cannot work in one CTE: MIN(occurred_at) is a GROUP BY user_id aggregate (one row per user), while AVG of the time-to-convert is a cross-user aggregate (one row total). Two different grains. The clean fix is the join back to step 1 in the outer query, where COUNT(*) and AVG(...) both operate over the per-user grain that step_2 already established.

You practiced layering the funnel pattern with a duration measurement — pairing each user's step-1 and step-2 timestamps and averaging the gap, so the funnel reports both volume and velocity.

Return two metrics: the count of users who performed an `'upgrade_clicked'` event strictly after a prior `'page_view'` (`users_converted`), and the average elapsed time in seconds from each such user's earliest `'page_view'` to their earliest qualifying `'upgrade_clicked'` (`avg_seconds_to_convert`)

The shape

Clause by clause

Why this and not subtracting timestamps and averaging the interval directly

The trap

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