Website traffic sample database for PostgreSQL
A ready-to-run PostgreSQL script with daily sessions, users, bounce rate and conversions by channel. Tested on PostgreSQL 15.18. Updated 19 November 2026.
Website traffic database for PostgreSQL
Each row is one day and one traffic channel, starting 1 January 2024: sessions, users, new users, pageviews, session length, bounce rate, conversions and revenue. Weekends are quieter and traffic grows slowly over time.
This page has it as a PostgreSQL script: one table and 1,000 rows, with column types chosen for PostgreSQL, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the website traffic dataset page, where it is also available as CSV and Excel.
We loaded this exact file into PostgreSQL 15.18 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
psql
createdb practice
psql -d practice -f website-traffic-postgresql.sqlpgAdmin or DBeaver
Create a database, open the Query Tool, open the .sql file and run it.
Tables and columns
website_traffic 1,000 rows
| Column | PostgreSQL type |
|---|---|
| date | DATE |
| channel | VARCHAR(20) |
| sessions | INTEGER |
| users | INTEGER |
| new_users | INTEGER |
| pageviews | INTEGER |
| avg_session_seconds | INTEGER |
| bounce_rate | DECIMAL(7,3) |
| conversions | INTEGER |
| revenue | DECIMAL(8,2) |
Practice questions
answers from PostgreSQL 15.18
Which channel brings the most sessions and revenue?
SQL
SELECT channel, SUM(sessions) AS sessions, ROUND(SUM(revenue), 2) AS revenue
FROM website_traffic
GROUP BY channel
ORDER BY sessions DESC, channel;Result
| channel | sessions | revenue |
|---|---|---|
| Organic search | 70405 | 82,088.43 |
| Direct | 43804 | 84,283.22 |
| Social | 25148 | 12,545.48 |
| Paid search | 20031 | 31,823.21 |
| Referral | 15265 | 22,366.29 |
| 11777 | 26,983.47 |
What is the conversion rate (conversions ÷ sessions) by channel, in percent?
SQL
SELECT channel, ROUND(1.0 * 100 * SUM(conversions) / NULLIF(SUM(sessions), 0), 2) AS conversion_pct
FROM website_traffic
GROUP BY channel
ORDER BY conversion_pct DESC, channel;Multiplying by 1.0 avoids integer division.
Result
| channel | conversion_pct |
|---|---|
| 4.14 | |
| Direct | 3.42 |
| Paid search | 2.90 |
| Referral | 2.65 |
| Organic search | 2.13 |
| Social | 0.91 |
What were sessions and revenue per month?
SQL
SELECT to_char(date, 'YYYY-MM') AS month, SUM(sessions) AS sessions, ROUND(SUM(revenue), 2) AS revenue
FROM website_traffic
GROUP BY to_char(date, 'YYYY-MM')
ORDER BY month;to_char(date, 'YYYY-MM') formats a date as year-month text.
Result
| month | sessions | revenue |
|---|---|---|
| 2024-01 | 32878 | 46,805.88 |
| 2024-02 | 31186 | 44,242.43 |
| 2024-03 | 33725 | 47,569.13 |
| 2024-04 | 34636 | 47,654.42 |
| 2024-05 | 36634 | 49,130.90 |
| 2024-06 | 17371 | 24,687.34 |
Which 5 days had the highest total revenue?
SQL
SELECT date, ROUND(SUM(revenue), 2) AS revenue
FROM website_traffic
GROUP BY date
ORDER BY revenue DESC, date
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| date | revenue |
|---|---|
| 2024-05-03 | 2,240.96 |
| 2024-02-13 | 2,211.99 |
| 2024-05-23 | 2,161.21 |
| 2024-03-26 | 2,150.77 |
| 2024-06-12 | 2,142.47 |
What is the session-weighted bounce rate per channel?
SQL
SELECT channel, ROUND(1.0 * 100 * SUM(bounce_rate * sessions) / NULLIF(SUM(sessions), 0), 2) AS bounce_pct
FROM website_traffic
GROUP BY channel
ORDER BY bounce_pct, channel;Multiplying by 1.0 avoids integer division.
Result
| channel | bounce_pct |
|---|---|
| Social | 46.11 |
| Referral | 46.94 |
| Direct | 46.96 |
| Paid search | 47.00 |
| Organic search | 47.18 |
| 47.53 |
Show the running total of revenue by month.
SQL
WITH m AS (
SELECT to_char(date, 'YYYY-MM') AS month, SUM(revenue) AS revenue
FROM website_traffic
GROUP BY to_char(date, 'YYYY-MM')
)
SELECT month, ROUND(revenue, 2) AS revenue, ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM m
ORDER BY month;RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work on any supported version.
Result
| month | revenue | running_total |
|---|---|---|
| 2024-01 | 46,805.88 | 46,805.88 |
| 2024-02 | 44,242.43 | 91,048.31 |
| 2024-03 | 47,569.13 | 138,617.44 |
| 2024-04 | 47,654.42 | 186,271.86 |
| 2024-05 | 49,130.90 | 235,402.76 |
| 2024-06 | 24,687.34 | 260,090.10 |
PostgreSQL differences used here
- Multiplying by 1.0 avoids integer division.
- LIMIT n at the end returns the first n rows.
- RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work on any supported version.
- to_char(date, 'YYYY-MM') formats a date as year-month text.