DataBrighten

    Website traffic sample database for DuckDB

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    Tables and columns

    website_traffic 1,000 rows

    ColumnDuckDB type
    dateDATE
    channelVARCHAR
    sessionsINTEGER
    usersINTEGER
    new_usersINTEGER
    pageviewsINTEGER
    avg_session_secondsINTEGER
    bounce_rateDECIMAL(7,3)
    conversionsINTEGER
    revenueDECIMAL(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

    channelsessionsrevenue
    Organic search7040582,088.43
    Direct4380484,283.22
    Social2514812,545.48
    Paid search2003131,823.21
    Referral1526522,366.29
    Email1177726,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

    channelconversion_pct
    Email4.14
    Direct3.42
    Paid search2.90
    Referral2.65
    Organic search2.13
    Social0.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

    monthsessionsrevenue
    2024-013287846,805.88
    2024-023118644,242.43
    2024-033372547,569.13
    2024-043463647,654.42
    2024-053663449,130.90
    2024-061737124,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

    daterevenue
    2024-05-032,240.96
    2024-02-132,211.99
    2024-05-232,161.21
    2024-03-262,150.77
    2024-06-122,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

    channelbounce_pct
    Social46.11
    Referral46.94
    Direct46.96
    Paid search47.00
    Organic search47.18
    Email47.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

    monthrevenuerunning_total
    2024-0146,805.8846,805.88
    2024-0244,242.4391,048.31
    2024-0347,569.13138,617.44
    2024-0447,654.42186,271.86
    2024-0549,130.90235,402.76
    2024-0624,687.34260,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).