How to calculate Average Session Duration

Formula

Average session duration = total session time ÷ number of sessions

SQL query

This example assumes: sessions(id, user_id, started_at, ended_at). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT DATE_TRUNC('week', started_at) AS week,
       COUNT(*) AS sessions,
       AVG(EXTRACT(EPOCH FROM (ended_at - started_at)) / 60) AS avg_minutes,
       PERCENTILE_CONT(0.5) WITHIN GROUP (
         ORDER BY EXTRACT(EPOCH FROM (ended_at - started_at)) / 60) AS median_minutes
FROM sessions
WHERE ended_at IS NOT NULL
GROUP BY 1
ORDER BY 1;

Common mistakes that make Average Session Duration wrong

• Idle tabs left open create very long sessions; cap or use the median.
• Single-event sessions have zero duration in many tracking tools.
• Longer is not always better: a tool that gets people to the answer faster should shorten sessions.

Ask for Average Session Duration in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What is the median session length by week?
• Do users on the paid plan have longer sessions?