DataBrighten

    Website traffic sample database for SQLite

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

    A ready-to-run SQLite script with daily sessions, users, bounce rate and conversions by channel. Tested on SQLite 3.37.2. Updated 19 November 2026.

    textnumberdateyes/no

    Website traffic database for SQLite

    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 SQLite script: one table and 1,000 rows, with column types chosen for SQLite, 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 SQLite 3.37.2 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

    Command line

    sqlite3 practice.db < website-traffic-sqlite.sql
    sqlite3 practice.db ".tables"

    Or skip the script

    Download website-traffic.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 website-traffic.sqlite

    Tables and columns

    website_traffic 1,000 rows

    ColumnSQLite type
    dateTEXT
    channelTEXT
    sessionsINTEGER
    usersINTEGER
    new_usersINTEGER
    pageviewsINTEGER
    avg_session_secondsINTEGER
    bounce_rateREAL
    conversionsINTEGER
    revenueREAL

    Practice questions

    answers from SQLite 3.37.2

    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;

    Multiplying by 1.0 forces decimal division instead of integer division.

    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('%Y-%m', date) AS month, SUM(sessions) AS sessions, ROUND(SUM(revenue), 2) AS revenue
    FROM website_traffic
    GROUP BY strftime('%Y-%m', date)
    ORDER BY month;

    strftime('%Y-%m', date) turns a date into year-month text.

    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;

    Multiplying by 1.0 forces decimal division instead of integer division.

    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('%Y-%m', date) AS month, SUM(revenue) AS revenue
      FROM website_traffic
      GROUP BY strftime('%Y-%m', date)
    )
    SELECT month, ROUND(revenue, 2) AS revenue, ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
    FROM m
    ORDER BY month;

    Window functions need SQLite 3.25 or later.

    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

    SQLite differences used here

    • Multiplying by 1.0 forces decimal division instead of integer division.
    • LIMIT n at the end returns the first n rows.
    • Window functions need SQLite 3.25 or later.
    • strftime('%Y-%m', date) turns a date into year-month text.