Lesson 19.7

Landing Page Conversion in SQL

Find the first page of every session with ROW_NUMBER, then measure how often sessions starting on each page end in a purchase.

A landing page is the first page a session saw. Asking which landing pages lead to purchases is one of the most common questions asked of web data, and it takes two steps: find one row per session, the first, and then measure what those sessions went on to do.

The first row per group

"The first pageview of each session" is a top-1-per-group question, the shape from Lesson 19.6. Number each session's pageviews in the order they happened and keep number 1:

Pattern

with firsts as (
  select session_id, path
  from pageviews
  qualify row_number() over (
    partition by session_id
    order by view_order, pageview_id
  ) = 1
)
select path, count(*) as sessions
from firsts
group by path

Use row_number(), not rank(), here: a session has exactly one landing page, and two pageviews logged at the same moment must not both count. Ending the ORDER BY with the unique pageview_id breaks that tie the same way every run.

This dataset's pageviews table already numbers each session's views in view_order, so where view_order = 1 would work too. Most event logs only have a timestamp, though, and row_number() ordered by it is the form that carries over.

Count sessions, not pageviews

The conversion rate is purchasing sessions divided by sessions, per landing page. Joining the purchase events straight onto every pageview breaks both halves: a session that saw 4 pages counts 4 times, under 4 different paths, and its purchase is credited to all of them. Reduce each side to one row per session first: the landing page from the window, and the purchasing sessions from select distinct session_id. Then a left join can't multiply anything. The same fan-out is behind inflated counts after a join.

Read small rates with care. A landing page with 2 sessions and 1 purchase shows 50%, which says more about the 2 sessions than about the page. Report the session count beside every rate.

Schema · Website Analytics Tables · pageviews, events 2 tables

Table · pageviews

One row per page viewed within a session, in view_order.

4 columns · 75 rows

pageview_id int session_id int path text view_order int

Table · events

Funnel events (signup, add_to_cart, checkout, purchase). Only purchase events carry a value.

4 columns · 39 rows

event_id int session_id int event_name text value decimal

Syntax reference

Need syntax details? See the SQL window function reference

Your task

For each landing page (the first page viewed in a session, by view_order and then pageview_id), return landing_page, sessions landing there, purchases (how many of those sessions have a purchase event), and purchase_rate, the percentage of those sessions that purchased, rounded to 1 decimal. Sort by purchase_rate descending, then sessions descending, then landing_page.

Website Analytics · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: landing_page, sessions, purchases, purchase_rate.
Rows: 5 landing pages. The sessions add up to 30, one per session.
/ lands 20 sessions and 3 of them buy: 15.0%. /blog and /features convert 1 of 2 each, so 50.0% rests on two sessions.

← Previous · 19.6 Top N per group

End of Chapter 19

That's the last lesson in “Window Functions in Depth.”

The next chapter builds on what this one covered.

Start Chapter 20: Statistics and Distributions → Begins with 20.1 Medians and percentiles

Reference

SQL Window Functions

OVER, PARTITION BY, ranking, offsets, and running totals.

Interview

SQL Funnel Analysis Interview Questions

Practice distinct counts, conversion rates, and drop-off checks.

Mistake