Actions per session in SQL

What actions per session actually measures

If you've spent any time staring at a product analytics dashboard, you've probably seen "session depth" — the raw count of every event fired during a session. It's a noisy, generous metric. Page views, scrolls, hovers, autoplay starts, idle timeouts: they all pile up and inflate the number. A user who opens a long article and scrolls leisurely to the bottom looks just as engaged as a user who actually clicks a CTA, fills a form, and saves a result. That's the problem actions per session is designed to fix.

Actions per session counts only the meaningful interactions: clicks on a primary call to action, submitted forms, saves, shares, uploads, comments. It's the "useful output" of a session, the engagement that actually moves the product forward. When a PM at Notion or Linear asks how engaged users are after a redesign, total events will lie to them and actions per session will not. If traffic is climbing but actions per session is falling, that's a flag: either the new cohort is less engaged, the UX got noisier, or you started shipping pages that bait clicks but don't carry them through.

The most useful framing for an analyst interview is this: actions per session is the throughput of a product. If session depth tells you "how long the elevator ride lasted," actions per session tells you "how many floors people actually got off at." Treat it as a throughput metric, segment it ruthlessly, and you'll always have something more interesting to say than "engagement is up two percent."

What counts as an action

The first ten minutes of any actions-per-session conversation are spent arguing about the taxonomy, so it's worth pinning down a working definition before you ever open a SQL editor. An action is an event that changes state — the user's, the product's, or some external system's. A click on a primary CTA changes state because it advances the user. A form submit changes state because it sends data. A save, a bookmark, a like, a share, a comment, a file upload, a download — all state-changing.

What you specifically exclude is everything passive. Page views don't change state, they just record that bytes were served. Scrolls measure attention, not intent. Hovers are even softer. Session start, session end, heartbeat pings — all instrumentation noise. Autoplay starts on video players are a famous trap: they look like engagement but the user did nothing. If your event taxonomy bundles passive and active events under the same event_name, the first thing to do is fork it: add an event_type column that tags each row as interaction or passive, and build actions per session on top of that tag rather than re-listing event names everywhere.

If you work in a product with strong revenue weight — checkout, subscriptions, upgrades — also think about whether you want a flat count or a weighted one. A click on the FAQ and a click on "Start free trial" are both clicks, but they are not equally valuable. We'll come back to that in the pitfalls section.

The SQL formula

The core query is short. We aggregate events to the session level, count how many of them qualify as actions, and then average across sessions. The trick is doing the aggregation cleanly and not double-counting.

WITH session_events AS (
    SELECT
        session_id,
        SUM(CASE WHEN event_name IN ('click', 'submit', 'share', 'save', 'comment', 'LIKE', 'upload') THEN 1 ELSE 0 END) AS actions,
        COUNT(*) AS total_events
    FROM events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY session_id
)
SELECT
    COUNT(*)                                                AS sessions,
    AVG(actions)                                            AS avg_actions_per_session,
    AVG(total_events)                                       AS avg_events_per_session,
    AVG(actions::NUMERIC / NULLIF(total_events, 0))         AS action_ratio,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY actions)    AS median_actions
FROM session_events;

A few details worth narrating because interviewers love asking about them. The SUM(CASE WHEN ...) is the canonical way to count rows that match a condition inside a GROUP BY; you'll also see COUNT(*) FILTER (WHERE ...) in Postgres which is more readable but less portable. NULLIF(total_events, 0) guards against the (rare) case of zero-event sessions slipping through your join logic — divide-by-zero turns the entire row into NULL instead of erroring out. And the cast to NUMERIC (or FLOAT) is mandatory in Postgres: integer division silently truncates and you'll end up reporting an action_ratio of 0 to a horrified room.

Pay attention to the average vs the median. An action_ratio of 0.2 means roughly one action per five events, which is normal for a browse-heavy product where most events are page views. The median is the number you actually trust — averages get crushed by long tails of power users with hundreds of actions per session. If the average is 8 and the median is 2, you're looking at a heavily skewed distribution and any decision based on the average alone will mislead.

Breaking it down by action type

The single-number actions-per-session report is a fine headline but a terrible diagnostic. Stakeholders will ask "what kind of actions?" within thirty seconds, and you should have the breakdown ready.

SELECT
    event_name,
    COUNT(*)                                                              AS total_occurrences,
    COUNT(DISTINCT session_id)                                            AS sessions_with_event,
    COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT session_id), 0)             AS avg_per_session_when_present,
    COUNT(DISTINCT session_id)::NUMERIC
        / NULLIF((SELECT COUNT(DISTINCT session_id) FROM events
                  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'), 0) AS reach
FROM events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
  AND event_name IN ('click', 'submit', 'share', 'save', 'comment', 'LIKE', 'upload')
GROUP BY event_name
ORDER BY total_occurrences DESC;

Two columns matter most in this breakdown. reach is the share of all sessions that contained at least one of this action — 0.04 means four percent of sessions had a comment. avg_per_session_when_present is the average count of that action among sessions that had at least one of them — that's the depth signal. A high reach with low depth means the action is widespread but shallow; a low reach with high depth means a small group of power users is doing all of it. Slack's famous internal metric "messages per user per day" is essentially this same idea applied to one event type at the user level.

If you find an action with a reach below one percent, ask whether the feature is broken, hidden, or just unloved. If you find one with depth above twenty, ask whether it's a power-user pattern (great) or a bot pattern (bad).

Tracking dynamics after a release

The interview classic: "you shipped a feature on the fifteenth, did it work?" Actions per session is one of the cleanest answers, provided you trend it weekly and mark the release date explicitly.

WITH release AS (
    SELECT DATE '2026-04-15' AS release_date
),
session_actions AS (
    SELECT
        session_id,
        MIN(event_timestamp) AS session_start,
        SUM(CASE WHEN event_name IN ('click', 'submit', 'share', 'save') THEN 1 ELSE 0 END) AS actions
    FROM events
    WHERE event_timestamp >= DATE '2026-03-01'
    GROUP BY session_id
),
weekly AS (
    SELECT
        DATE_TRUNC('week', session_start)::DATE AS week,
        COUNT(*)                                AS sessions,
        AVG(actions)                            AS avg_actions_per_session
    FROM session_actions
    GROUP BY DATE_TRUNC('week', session_start)
)
SELECT
    w.week,
    w.sessions,
    w.avg_actions_per_session,
    CASE WHEN w.week >= r.release_date THEN 'post' ELSE 'pre' END AS period
FROM weekly w
CROSS JOIN release r
ORDER BY w.week;

You want at least four weeks before the release and four after, ideally more. Plot the weekly average with a vertical line at the release date and you'll see one of three patterns. A clean step-up immediately after release that holds for several weeks is a real lift. A spike that decays back to baseline in two weeks is a novelty effect. A flat line or a decline is a feature that didn't move the needle, and you should be honest about that rather than carving the chart until it shows a lift. Be especially suspicious of the first week after release — heavy user attention plus instrumentation churn can produce a temporary bump that has nothing to do with the feature itself.

Common pitfalls

When teams compute actions per session for the first time, the most damaging mistake is to count every event in the numerator. Page views, scrolls, autoplay starts, heartbeats — they all inflate the number into meaninglessness. The fix is to maintain an explicit allow-list of action event names (or, better, an event_type column), apply it in a CASE WHEN or FILTER clause, and make that allow-list a code-reviewed artifact rather than something every analyst guesses at. The day you discover two teams are reporting different numbers because they disagree on whether "video_play" is an action is the day you wish you'd done this.

A second trap is leaving bots in the data. A single scraping bot can produce a session with five hundred clicks and yank the average up by ten percent. The fix is to filter aggressively on the user-agent string, identify and exclude known bot device IDs, and cap per-session action counts at a sane upper bound — say the ninety-ninth percentile — before averaging. If you can, log a bot flag at ingestion time and apply it in every aggregation by default. Trimming after the fact is fine; not trimming at all is not.

A third pitfall is ignoring session length. A five-minute session and a sixty-minute session are not comparable raw counts of actions, even if both record meaningful interactions. The fix is to normalize: actions per minute is often a more honest comparison across cohorts whose session durations differ. This matters especially when you're comparing mobile (short, intense) and desktop (long, browsy) sessions, or when a redesign changes the natural session duration.

A fourth trap is treating all actions as equally important. A click on a footer link and a click on "Complete purchase" are both clicks, and they will be weighted identically in a naive SUM. The fix is to attach a value or weight column to each action — revenue contribution for commerce, completion contribution for funnels, content contribution for media — and report weighted-actions-per-session alongside the raw count. The weighted view often tells a completely different story.

The final pitfall is stopping at the session-aggregate level. Two products can have identical actions per session and wildly different distributions across users. The fix is to always pair the session-level number with a per-user view: actions per user per week, the share of users with at least one action in a given window, and the actions-per-session distribution segmented by user tenure. If the top one percent of users is doing thirty percent of the actions, you don't have an engagement story — you have a power-user story, and the actions-per-session number is mostly measuring that small group.

Optimization tips

For tables in the billions-of-rows range, the join-and-aggregate pattern above starts to hurt. The first lever is partitioning the events table by event date — most warehouses (Snowflake, BigQuery, Redshift) push a WHERE event_timestamp >= ... predicate into a partition prune and skip the rest, which makes a thirty-day rollup linear in thirty days instead of the entire history. The second lever is materializing a session_actions table — one row per session with the precomputed action count — on a daily schedule. Once it's materialized, all the downstream queries (breakdowns, dynamics, segmentations) read kilobytes instead of terabytes.

If you're on a warehouse with clustering or sort keys, cluster the events table on session_id so that the GROUP BY session_id becomes a streaming aggregation rather than a shuffle. On BigQuery, the equivalent is clustering on session_id and partitioning on event_date. On Snowflake, the equivalent is a clustering key on (event_date, session_id). The difference between an optimized and unoptimized actions-per-session query at scale is often two orders of magnitude in runtime.

Finally, if you find yourself running this query interactively, dogfooded on a real production table, consider building a small session_summary rollup table that stores session_id, user_id, session_start, session_end, total_events, total_actions, and a weighted_actions column. Most downstream questions — daily averages, cohort breakdowns, post-release dynamics — become a one-line query against the rollup, and the rollup itself is cheap to refresh nightly.

Related reading

• SQL window functions interview questions
• A/B testing peeking mistake
• Why are you leaving your job — interview answer

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

FAQ

Which events should count as an action?

Anything that changes state — a submit, a save, a like, a share, an upload, a click on a primary CTA. Anything passive — a page view, a scroll, a hover, a session start, an autoplay — should be excluded. The cleanest way to enforce this is to add an event_type column to your events table that tags each row as interaction or passive at ingestion time, and to filter on that column rather than maintaining a list of event names in every query. When the taxonomy is in code instead of in analysts' heads, the numbers stop drifting between teams.

What's a healthy benchmark for actions per session?

It depends heavily on the product category. E-commerce sites typically see two to five actions per session — a few searches, a few product views, maybe one add-to-cart. Productivity SaaS like Notion or Linear is usually five to fifteen, because users actively author content. Social products like a chat app or a feed-heavy network can run twenty to fifty. Marketplaces and content discovery sites sit in between. Compare yourself against products with a similar core loop, not against an industry-wide average — those averages are mostly noise.

A low action ratio looks bad. Is it always?

No. Some surfaces are designed to be passive — a search results page, an article, a feed. Low action ratio there is expected and not a problem. What matters is the trend: a stable low ratio is fine, a declining ratio on a surface that used to convert is a flag. Always pair the absolute number with a year-over-year or month-over-month comparison before you draw any conclusions, and always segment by surface before drawing conclusions about the whole product.

Can we use actions per session as a North Star metric?

Sometimes, and Slack's famous "messages per user per day" is the canonical example of doing it well. The criteria are: the metric correlates with retention, it's hard to game without delivering real value, and it's understandable to the whole company. Actions per session can clear all three bars if your product has a single dominant interaction loop. It typically fails as a North Star if the product is browsing-heavy, because the metric will reward distraction over depth. A safer compromise is to make a weighted actions per session — actions multiplied by revenue or completion weight — your headline metric, and keep the raw count as a diagnostic underneath.

What's the difference between an action and an interaction?

In most companies they're synonyms, and you can use whichever your analytics platform defaults to. When teams do draw a distinction, "interaction" tends to be broader and includes soft signals like hover or scroll, while "action" is reserved for state-changing events. Pick one definition, write it down, and enforce it in the taxonomy — the worst outcome is that the words mean different things to different teams and your reports stop reconciling.

How do I handle very long or very short sessions?

Cap session length at a reasonable maximum (often thirty minutes of inactivity is the standard for splitting a session in two) and exclude single-event sessions if they're dominated by accidental opens or instrumentation noise. For long sessions, the right move is usually to normalize by minutes: report actions per minute alongside actions per session, so a sixty-minute power-user session and a five-minute mobile session can be compared on equal footing. Trim aggressively, document the trim, and explain it whenever you present the number.