DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • CockroachDB TIL: Volume 11
  • Building an Enterprise CDC Solution
  • How Doris Connects to Multiple Databases for Federated Analytics and Data Migration
  • Recover Distributed Transactions in MySQL

Trending

  • Evolution of Cloud Services for MCP/A2A Protocols in AI Agents
  • It’s Not About Control — It’s About Collaboration Between Architecture and Security
  • How to Practice TDD With Kotlin
  • Java Virtual Threads and Scaling
  1. DZone
  2. Data Engineering
  3. Databases
  4. Event Analytics: How to Define User Sessions With SQL

Event Analytics: How to Define User Sessions With SQL

Analyzing user sessions with SQL gives you flexibility and full control over how metrics are defined for your unique business.

By 
Artyom Keydunov user avatar
Artyom Keydunov
·
Feb. 10, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
13.3K Views

Join the DZone community and get the full member experience.

Join For Free

Recently, we’ve built event analytics for our team and thought to share this experience with you in this post and in an upcoming free webinar. Ready to learn how to transform raw events data into events flow and user sessions?

Many of out-of-the-box analytics solutions come with automatically defined user sessions. It’s good to start with, but as your company grows, you’ll want to have your own session definitions based on your event data. Analyzing user sessions with SQL gives you flexibility and full control over how metrics are defined for your unique business.  

What Is a Session and Why Should I Care?

The session is usually defined as a group of interactions that one user executes within a given time frame on your app. Usually, that time frame defaults to 30 minutes. One session includes any events which a user completes on your app before leaving, for example, visiting pages, downloading materials, and performing actions.

user sessions

Having sessions in place, we’ll be able to answer questions like:

  • How much time did users spend on the app?
  • What is the bounce rate?
  • What are the most used areas of the app?
  • Where are the users who make target actions coming from?
  • Where are users spending most of their time?

Defining User Sessions the Right Way

To define a user session, we need to have an event table in our database with at least user_id and timestamp.

Usually, you will have a lot of additional information in the event tables, such as event type, name, device info, referrer, and much more. All these properties are going to be very useful to give more context to our sessions and to build things, such as an attribution model.

Note: In this post, we’re going to use window functions, so the following example will not work with MySQL. Also, all these queries are dedicated to Redshift database. 

Step 1

The first step for defining user sessions with SQL is to find out the difference between the timestamp of the current row and the previous one, by user_id. We will use the LAG function to accomplish it. This will give us an inactivity time between events.

SELECT
*
, DATEDIFF(minutes, lag(timestamp) over (PARTITION BY user_id order by timestamp), timestamp) as inactivity_time
FROM events

The inactivity_time for the first event is NULL since it’s the first event and we don’t have anything before it.

Step 2

We can use inactivity_time to group events into sessions based on 30-minute intervals of inactivity. First, we’ll select all events wherein activity_time is either NULL or more than 30 minutes, which means it is the first event in the session.

Based on this first event, we definesession_start_at, which is the timestamp of the first event. We use the ROW_NUMBER function to calculate session sequence, which is used in session_id.

SELECT
      event.user_id || '-' || row_number() over(partition by event.user_id order by event.timestamp) as session_id
      , event.user_id
      , event.timestamp as session_start_at
      , lead(timestamp) over(partition by event.user_id order by event.timestamp) as next_session_start_at
    FROM
      (SELECT
            e.user_id
     , e.timestamp
             , DATEDIFF(minutes, LAG(e.timestamp) OVER(PARTITION BY e.user_id ORDER BY e.timestamp), e.timestamp) AS inactivity_time  
          FROM events AS e
      ) as event
    WHERE (event.inactivity_time > 30 OR event.inactivity_time is null)

We can save this table as a sessions data mart to use it in our future queries.

Once we have this table, it’s easy to answer the user analytics questions we outlined in the beginning. For example, to calculate average session duration, we can use the following SQL.

SELECT
  COUNT(*) AS sessions_count,
  AVG(duration) AS average_session_duration
FROM (
  SELECT session_id
        , DATEDIFF(minutes, MIN(events.timestamp), MAX(events.timestamp)) AS duration
  FROM sessions
  LEFT JOIN events on events.user_id = sessions.user_id
        AND events.timestamp >= events.session_start_at
        AND (events.timestamp < sessions.next_session_start_at OR sessions.next_session_start_at is null)
  GROUP BY 1
)

As you see, we join events table to sessions to map every event to its session. It allows us to get the end of each session, which is the max timestamp of the event within the given session. More complex calculations of session duration could optionally include a window of inactivity, as well.

Session (web analytics) Event sql MySQL Analytics Database

Published at DZone with permission of Artyom Keydunov. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • CockroachDB TIL: Volume 11
  • Building an Enterprise CDC Solution
  • How Doris Connects to Multiple Databases for Federated Analytics and Data Migration
  • Recover Distributed Transactions in MySQL

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: