Website traffic sample database for DuckDB
A ready-to-run DuckDB script with daily sessions, users, bounce rate and conversions by channel. Tested on DuckDB 1.5.6. Updated 19 November 2026.
Website traffic database for DuckDB
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 DuckDB script: one table and 1,000 rows, with column types chosen for DuckDB, 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 DuckDB 1.5.6 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
Python
import duckdb
con = duckdb.connect("practice.duckdb")
con.execute(open("website-traffic-duckdb.sql").read())DuckDB CLI
duckdb practice.duckdb < website-traffic-duckdb.sqlTables and columns
website_traffic 1,000 rows
| Column | DuckDB type |
|---|---|
| date | DATE |
| channel | VARCHAR |
| 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 DuckDB 1.5.6
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;1.0 * a / b gives a decimal result.
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 strftime(date, '%Y-%m') AS month, SUM(sessions) AS sessions, ROUND(SUM(revenue), 2) AS revenue
FROM website_traffic
GROUP BY strftime(date, '%Y-%m')
ORDER BY month;strftime(date, '%Y-%m') formats a date as year-month text (note the argument order differs from SQLite).
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;1.0 * a / b gives a decimal result.
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 strftime(date, '%Y-%m') AS month, SUM(revenue) AS revenue
FROM website_traffic
GROUP BY strftime(date, '%Y-%m')
)
SELECT month, ROUND(revenue, 2) AS revenue, ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM m
ORDER BY month;DuckDB supports all standard window functions.
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 |
DuckDB differences used here
- 1.0 * a / b gives a decimal result.
- LIMIT n at the end returns the first n rows.
- DuckDB supports all standard window functions.
- strftime(date, '%Y-%m') formats a date as year-month text (note the argument order differs from SQLite).