DataBrighten

    Website traffic sample database for PostgreSQL

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

    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.

    textnumberdateyes/no

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

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    website_traffic 1,000 rows

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

    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 avoids 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 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

    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 avoids 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 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

    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

    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.