Bounce Rate by Landing Page — Solution & ExplanationProblems / Bounce Rate by Landing Page / EditorialApproach

Pageviews → sessions. Group by session_id. COUNT(*) gives the session's length; MAX(CASE WHEN view_order = 1 THEN page END) extracts its landing page — the CASE yields NULL on non-landing rows, and MAX over one non-NULL value returns it.

Sessions → landing pages. Group the per-session rows by landing_page; COUNT(*) is total sessions, and a conditional count of pageviews = 1 is the bounce count.

Rate. bounces * 100.0 / sessions, rounded to 2 decimals; order by landing_page.

Key InsightBounce rate lives at the session grain, and the landing page is a *session attribute* that must be derived, not a row attribute you can group by directly. Grouping the raw table by page counts every page a session visited — inflating denominators and making bounces (which visited only one page) uncountable. The MAX(CASE ...) pivot inside the first GROUP BY is the standard idiom for hoisting a "row-1 attribute" up to its parent entity.Hints

The output grain is landing pages, but the input grain is pageviews — you need an intermediate grain of sessions. Two stacked GROUP BYs.

Extracting the landing page during the per-session GROUP BY is the trick: MAX(CASE WHEN view_order = 1 THEN page END). The CASE is NULL for every other row, so MAX just picks the one landing page.

A bounce is pageviews = 1, judged per session — never per page. A session that lands on home and leaves is a home bounce even though it has only one row.

The second GROUP BY (by landing_page) then counts sessions and conditionally counts bounces with FILTER or SUM(CASE ...).

Solve Bounce Rate by Landing Page yourself →