Lesson 12.1

SQL Practice: Grouping Web Sessions by Channel

A second fresh dataset: put GROUP BY, COUNT, and AVG to work on marketing traffic.

This final chapter swaps the shop for a marketing analyticsdataset: users, campaigns, sessions, pageviews, and events for a SaaS website. It is the classic shape of data an analyst works with every day.

The sessions table has one row per visit, each tagged with a channel (organic, paid, social, email, direct) and a duration_seconds. "Which channels drive the most traffic?" is a grouping question.

Count and average in one pass

Grouping by channel collapses every visit into one row per channel. From there, count(*) measures volume and avg(duration_seconds) measures engagement. They are two very different questions answered by the same grouped query.

Pattern

select group_column,
       count(*) as row_count,
       round(avg(number_column), 1) as average
from table_name
group by group_column
order by row_count desc, group_column

Schema · Website AnalyticsTable · sessions7 columns · 30 rows

Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows

session_id intuser_id intsession_date datechannel textcampaign_id intdevice textduration_seconds int

Your task

For each channel, return the channel, the number of sessions (sessions), and the average duration in seconds rounded to one decimal (avg_seconds). Sort by sessions descending, then channel.

SQL Workbench

query.sqlwebsite_analytics · SQL engine loading

⌘↵ to run

Expected answer