How to calculate bounce rate in SQL

What is bounce rate and why it matters

A growth marketer at a Series B SaaS pings you on Slack at 4:47pm: "We doubled spend on the new Performance Max campaign, traffic is up 90% week-over-week, and signups are flat. What's wrong?" You pull a quick query. Bounce rate on the campaign landing page jumped from 47% to 81% the same week the new creatives went live. The marketer doesn't have a conversion problem. She has a traffic-quality problem, and bounce rate is the cheapest signal that can tell her on Friday morning instead of next quarter.

Bounce rate is a blunt indicator of three things at once: how well the acquisition channel matches the page, whether the page renders correctly, and whether the page promises what the visitor wants. None of those signals proves anything alone. Together, when bounce shifts ten or twenty points week over week, something real is happening, and you want to know before it has been happening for a month.

This post walks through the SQL you actually need: classic single-page bounce, the modern engagement-based definition, breakdowns by landing page and traffic source, and traps that quietly inflate or deflate the number. The queries assume a pageviews table with one row per pageview and a session identifier.

The two definitions you need to know

A bounce, in the original Google Analytics sense, was a session in which the user viewed exactly one page and then left. Simple to explain and easy to query, which is why it stuck for fifteen years. The downside is that it punishes pages designed to be read in one sitting. A long-form blog post where a visitor reads for nine minutes and leaves is a single-page session and therefore a "bounce", even though that was the intended outcome.

GA4 replaced that definition in 2021. A session is engaged if it lasts at least ten seconds, OR includes a conversion event, OR has at least two pageviews. Bounce rate is then 1 - engagement_rate. The new definition forgives the long-read scenario and is friendlier to single-page-app patterns where the URL never changes. The trade-off is that you now need event-level data, not just pageview counts.

Pick the definition that matches the question. For a top-of-funnel landing page with one call to action, the classic definition is usually closer to what the marketer cares about. For content marketing where the success metric is "did the visitor consume the page", engaged-session bounce is more honest. Many teams compute both, label them clearly, and let stakeholders pick.

Classic single-page bounce in SQL

Here is the baseline query. Source data: a pageviews table with session_id, page_url, and timestamp.

WITH session_pages AS (
    SELECT
        session_id,
        COUNT(*) AS page_count
    FROM pageviews
    GROUP BY session_id
)
SELECT
    COUNT(*) AS total_sessions,
    SUM(CASE WHEN page_count = 1 THEN 1 ELSE 0 END) AS bounced_sessions,
    SUM(CASE WHEN page_count = 1 THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(*), 0) AS bounce_rate_pct
FROM session_pages;

The CTE collapses every session to one row with its pageview count. The outer query counts sessions, counts the ones with exactly one pageview, divides, multiplies by 100. The NULLIF(COUNT(*), 0) guard returns NULL instead of throwing a division-by-zero error when a filter excludes every session — the dashboard does not break at 3am.

The classic query gives one number for the whole site. That number is almost never useful on its own. Bounce rate aggregated across a homepage, a pricing page, and 400 blog posts is a weighted average of completely different things. The next step is always to break it down.

Bounce by landing page and traffic source

The most useful breakdown is by landing page — the first page a session actually viewed. A landing page with a 90% bounce rate when the rest of the site sits at 45% tells you exactly where to focus. The query needs a window function to identify the first pageview per session.

WITH ranked AS (
    SELECT
        session_id,
        page_url,
        ROW_NUMBER() OVER (
            PARTITION BY session_id ORDER BY TIMESTAMP
        ) AS rn
    FROM pageviews
),
session_info AS (
    SELECT
        session_id,
        MAX(CASE WHEN rn = 1 THEN page_url END) AS landing_page,
        COUNT(*) AS page_count
    FROM ranked
    GROUP BY session_id
)
SELECT
    landing_page,
    COUNT(*) AS sessions,
    SUM(CASE WHEN page_count = 1 THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(*), 0) AS bounce_rate_pct
FROM session_info
GROUP BY landing_page
HAVING COUNT(*) >= 100
ORDER BY bounce_rate_pct DESC;

The HAVING COUNT(*) >= 100 filter is not optional. Pages with twelve sessions and 100% bounce are statistical noise; sort descending without a floor and the noisiest pages always sit on top. Pick a floor that fits your traffic — 1,000 for a site doing 5 million sessions a month, 50 for a small B2B site.

Breakdown by acquisition channel is the second most common cut. Assume a sessions table with utm_source, utm_medium, and device_type.

WITH session_summary AS (
    SELECT
        s.session_id,
        s.utm_source,
        s.utm_medium,
        s.device_type,
        COUNT(p.page_url) AS page_count
    FROM sessions s
    LEFT JOIN pageviews p
        ON p.session_id = s.session_id
    GROUP BY s.session_id, s.utm_source, s.utm_medium, s.device_type
)
SELECT
    utm_source,
    utm_medium,
    device_type,
    COUNT(*) AS sessions,
    SUM(CASE WHEN page_count <= 1 THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(*), 0) AS bounce_rate_pct
FROM session_summary
GROUP BY utm_source, utm_medium, device_type
HAVING COUNT(*) >= 100
ORDER BY sessions DESC;

page_count <= 1 instead of = 1 is on purpose: the LEFT JOIN to pageviews can leave a session with zero pageviews if your pipeline drops events. Treating both zero and one as "bounce" is the safer call until you know why the joins are missing.

GA4-style engaged-session bounce

The engagement-based definition requires event-level data. The session is engaged if any of: it lasted at least ten seconds, it had a conversion event, or it had at least two pageviews. Bounce is the complement.

WITH session_metrics AS (
    SELECT
        session_id,
        COUNT(*) FILTER (WHERE event_type = 'pageview') AS page_count,
        EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time)))
            AS duration_sec,
        BOOL_OR(event_type = 'conversion') AS had_conversion
    FROM events
    GROUP BY session_id
)
SELECT
    COUNT(*) AS total_sessions,
    SUM(CASE
        WHEN page_count >= 2 THEN 0
        WHEN duration_sec >= 10 THEN 0
        WHEN had_conversion THEN 0
        ELSE 1
    END) AS bounced_sessions,
    SUM(CASE
        WHEN page_count >= 2 THEN 0
        WHEN duration_sec >= 10 THEN 0
        WHEN had_conversion THEN 0
        ELSE 1
    END) * 100.0 / NULLIF(COUNT(*), 0) AS bounce_rate_pct
FROM session_metrics;

The duration is a little subtle. MAX(event_time) - MIN(event_time) is the gap between the first and last event — the GA4 convention. If a user fires only one pageview, that gap is zero, and the session is a bounce unless the page reports engagement signals (scroll, click, video play) as separate events. Instrument those signals on a content site, or every one-pageview session looks identical to the warehouse regardless of whether the visitor read for ten seconds or ten minutes.

A site that migrates from the classic to the engagement definition typically sees bounce rate drop ten to thirty percentage points. The traffic did not change — the definition did. Communicate that clearly when you publish the new dashboard, or someone in marketing will screenshot the new number and claim a win.

Common pitfalls

The first trap is computing bounce rate on a single-page application without instrumenting client-side route changes. In a SPA, navigating from /dashboard to /dashboard/billing does not trigger a pageview by default. If your analytics library does not hook into the router, every session ends with one pageview and bounce sits at 99%. The fix is in the front-end code: fire a synthetic pageview event on route change. Until that ships, do not publish bounce rate for the SPA part of the product.

The second trap is comparing bounce on a blog post to the homepage as if the numbers mean the same thing. A blog post at 70% is a healthy long-read; a homepage at 70% is a problem. Bounce is comparable across pages of the same type, not across types. Group by template — /blog/*, /pricing, /docs/* — then compare.

The third trap is not segmenting by device. Mobile bounce is structurally 5 to 15 points higher than desktop on most sites. Screen is smaller, connection is slower, the visitor is more interrupted, and the consent banner sometimes covers the call to action on iOS Safari. A total bounce number lumping both hides whether mobile is fine or actively broken.

The fourth trap is not filtering bot traffic. A scraper that loads one page and leaves is by definition a bounce. If 20% of "sessions" are bots and real bounce is 50%, the reported number reads 60% with no change in human behavior. Filter on a bot flag from your CDN or user-agent patterns.

The fifth trap is treating bounce and exit rate as interchangeable. Bounce is the share of sessions whose only page was page X; exit is the share whose last page was X. A marketer who asks "why is bounce on the checkout page so high?" usually means exit. Clarify before writing the query.

Optimization tips

Bounce rate queries are usually cheap because session cardinality is much lower than event cardinality. The slow one is the page-level breakdown over a long range, since you scan every pageview to find the first per session. Partition the pageviews table by date and filter the date range early — most warehouses prune partitions before reading. Maintain a sessions table with landing_page precomputed at session-creation time and skip the window function entirely; the query becomes a straight join on session_id, orders of magnitude cheaper on a 1B-row events table. Materialize the session-grain table as a daily incremental dbt model so the dashboard reads one row per session, not twenty.

For real-time dashboards, cost is dominated by the trailing window. A "last seven days" view recomputed every minute burns Snowflake or BigQuery credits. Compute hourly on a schedule and serve from a small aggregated table — five minutes of latency on a marketing dashboard is fine.

Related reading

• SQL window functions interview questions
• How to calculate actions per session in SQL
• How to calculate MAU in SQL
• A/B testing peeking mistake

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What counts as a "good" bounce rate?

There is no single benchmark, only a range tied to page type. Content blogs sit at 60 to 80% and that is fine. E-commerce homepages land at 30 to 50% because navigation is the point. A focused B2B landing page with a single call to action runs 40 to 60%. A logged-in SaaS dashboard should be in the single digits to low teens; higher means something is broken with how you count sessions for authenticated users. Compare same-type pages to same-type pages, and benchmark against your own historical trend.

My bounce rate just jumped to 100%. What do I check first?

In order: instrumentation, consent banner, then traffic. Open the page in a fresh browser, watch the network tab, confirm the pageview request fires. If it does not, a deploy broke tracking. Second, check whether the consent banner is blocking events — some CMPs default to "deny all" and silently drop pageviews on iOS. Third, check whether a new traffic source started this week, like a bot ring, a referrer that strips parameters, or a campaign with a misconfigured redirect landing on a 404.

Should I optimize for bounce rate or for session duration?

Depends on the page. For a blog post or documentation, session duration is closer to the success metric because "did the visitor read the thing" is the question. For a landing page with one call to action, bounce rate alongside conversion rate is the better pair, because long duration without a conversion is a person who got confused, not served. For a product catalog page, neither is enough on its own — pages-per-session and add-to-cart rate together tell a more useful story.

Can I lower bounce rate artificially?

Technically yes. A ten-second auto-redirect, a hidden "scroll past 25%" event registered as a second pageview, a popup that fires an interaction event — all will reduce reported bounce without anyone reading more of the page. Do not do this. You will compare the goosed number to a competitor's honest number, conclude your page is fine, and stop fixing the underlying problem. The team you most damage is your own.

Why does mobile bounce look so much worse than desktop?

Three structural reasons, none of which SQL fixes. Mobile users have shorter attention windows — a 6-second visit is much more common than at a desk. Mobile pages load slower on 4G, and a page that takes 6 seconds to become interactive gets abandoned. Cookie banners cover more of the screen on mobile and occlude the call to action above the fold. Segment by device and treat them as different products.

How long a date range should the dashboard cover?

Seven and twenty-eight days is the usual pair: a fast window that catches campaign or deploy issues, plus a slower window that smooths weekday-vs-weekend effects. Quarter-over-quarter is for retros, not operating decisions. Avoid windows shorter than seven days for any page below a few thousand sessions a week, or daily noise dominates the signal and people chase flukes.