SQL Window Functions for Sessionization and Funnel Analysis

Raw events are not sessions

A tracking script emits one row per pageview. Almost every question you actually care about — bounce rate, session duration, entry and exit pages, funnel conversion — is defined over sessions, which do not exist in the raw data. Window functions are how you construct them in SQL.

Assume this table throughout:

CREATE TABLE events (
  visitor_id VARCHAR,
  timestamp  TIMESTAMP,
  path       VARCHAR,
  event_name VARCHAR
);

The gap-and-island pattern

Sessionization is a gap-and-island problem: find the gaps, and everything between them is an island.

Step one — measure the gap to the previous event per visitor.

SELECT visitor_id, timestamp, path,
       timestamp - lag(timestamp) OVER (
           PARTITION BY visitor_id ORDER BY timestamp
       ) AS gap
FROM events;

Step two — flag rows that start a new session. The first event for a visitor has a NULL gap, which also counts as a start.

CASE WHEN gap > INTERVAL 30 MINUTE OR gap IS NULL THEN 1 ELSE 0 END AS is_session_start

Step three — running sum of the flag gives each session a number.

sum(is_session_start) OVER (
    PARTITION BY visitor_id ORDER BY timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_num

That running sum is the trick worth remembering: it only increments at boundaries, so every row inside an island shares a value.

Put together:

WITH gaps AS (
  SELECT visitor_id, timestamp, path,
         CASE WHEN timestamp - lag(timestamp) OVER w > INTERVAL 30 MINUTE
                OR lag(timestamp) OVER w IS NULL
              THEN 1 ELSE 0 END AS is_start
  FROM events
  WINDOW w AS (PARTITION BY visitor_id ORDER BY timestamp)
),
sessions AS (
  SELECT *, sum(is_start) OVER (
             PARTITION BY visitor_id ORDER BY timestamp
             ROWS UNBOUNDED PRECEDING) AS session_num
  FROM gaps
)
SELECT visitor_id, session_num,
       min(timestamp) AS started_at,
       max(timestamp) AS ended_at,
       count(*)       AS pageviews
FROM sessions
GROUP BY visitor_id, session_num;

Entry pages, exit pages, and bounces

With sessions in hand, first_value and last_value give you entry and exit paths directly:

SELECT visitor_id, session_num,
       first_value(path) OVER w AS entry_page,
       last_value(path)  OVER w AS exit_page,
       count(*)          OVER (PARTITION BY visitor_id, session_num) AS depth
FROM sessions
WINDOW w AS (
  PARTITION BY visitor_id, session_num ORDER BY timestamp
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

The explicit frame on last_value is not optional. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so without it last_value returns the current row — one of the most common window-function bugs, and it fails silently.

Bounce rate is then a session-level average:

SELECT round(100.0 * avg(CASE WHEN depth = 1 THEN 1 ELSE 0 END), 1) AS bounce_rate_pct
FROM session_summary;

Time on page

Time on page is the gap to the next event in the same session:

SELECT path,
       median(seconds) AS median_seconds
FROM (
  SELECT path,
         epoch(lead(timestamp) OVER (
             PARTITION BY visitor_id, session_num ORDER BY timestamp
         ) - timestamp) AS seconds
  FROM sessions
)
WHERE seconds IS NOT NULL AND seconds < 1800
GROUP BY path;

Two deliberate choices: the last page in each session has no next event, so its duration is unknowable — NULL, not zero. And use the median, because a handful of abandoned tabs will drag any mean into fiction.

Funnels with ordered steps

A funnel asks: of the people who did step 1, how many went on to step 2, then step 3 — in order.

WITH steps AS (
  SELECT visitor_id, session_num, timestamp,
         CASE path
           WHEN '/pricing'  THEN 1
           WHEN '/signup'   THEN 2
           WHEN '/checkout' THEN 3
           WHEN '/success'  THEN 4
         END AS step
  FROM sessions
  WHERE path IN ('/pricing','/signup','/checkout','/success')
),
progress AS (
  SELECT visitor_id, session_num,
         max(CASE WHEN step = 1 THEN 1 ELSE 0 END) AS s1,
         max(CASE WHEN step = 2 AND timestamp > min_ts_1 THEN 1 ELSE 0 END) AS s2
  FROM (
    SELECT *, min(CASE WHEN step = 1 THEN timestamp END)
                OVER (PARTITION BY visitor_id, session_num) AS min_ts_1
    FROM steps
  )
  GROUP BY visitor_id, session_num
)
SELECT sum(s1) AS reached_step_1,
       sum(s2) AS reached_step_2,
       round(100.0 * sum(s2) / nullif(sum(s1), 0), 1) AS step_1_to_2_pct
FROM progress;

The timestamp > min_ts_1 condition enforces ordering. Without it you are counting people who hit /signup before /pricing as converters, which inflates every funnel.

Returning-visitor cohorts

SELECT visitor_id,
       min(started_at)::DATE AS first_seen,
       count(*)              AS total_sessions,
       datediff('day', min(started_at), max(started_at)) AS lifespan_days
FROM session_summary
GROUP BY visitor_id;

Group by first_seen week and you have a retention cohort table without leaving SQL.

Frequently asked questions

What is the gap-and-island technique?

A two-step pattern: use LAG to compare each event's timestamp with the previous one and flag gaps over your threshold, then take a running SUM of those flags to number the resulting islands. Each island is a session.

What inactivity timeout should I use?

Thirty minutes is the industry default and keeps your numbers comparable with other tools. What matters more is stating it explicitly — session counts are meaningless without the threshold that produced them.

How is time on page calculated for the last pageview?

It cannot be, from pageviews alone — there is no next event to subtract from. Either treat it as null and exclude it, or bound it with an engagement ping. Silently counting it as zero drags every average down.

Why do funnel steps need explicit ordering?

Because without an ordering constraint you are measuring who did all the steps, not who did them in sequence. Comparing each step's timestamp against the previous one is what makes it a funnel rather than a set intersection.

Are window functions slow on large tables?

They require a sort, which is the main cost. Partitioning by visitor keeps each sort small, and columnar engines parallelize them well — filter to the date range before the window rather than after.

Why DuckDB is pleasant for this

These queries scan a lot of rows, touch few columns, and sort within partitions — precisely the shape a vectorized columnar engine handles well. Sessionizing tens of millions of events on a laptop is routine, which means you can iterate on the logic interactively instead of submitting jobs.

For ready-made queries see 12 SQL queries every dashboard needs, and for the funnel metric itself, funnel analysis. InsightsTrack's SQL Editor exposes exactly this: read-only DuckDB access to your own event data, so you can write sessionization and funnel queries directly rather than waiting for a feature to be built.