DataBrighten

    Marketing campaigns sample database for PostgreSQL

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

    A ready-to-run PostgreSQL script with budget, spend, impressions, clicks, CPA and ROAS per campaign. Tested on PostgreSQL 15.18. Updated 19 November 2026.

    textnumberdateyes/no

    Marketing campaigns database for PostgreSQL

    Each row is one fictional ad campaign: channel, dates, budget and actual spend, impressions, clicks, click-through rate, conversions, cost per acquisition, revenue and return on ad spend.

    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 marketing campaigns 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 marketing-campaigns-postgresql.sql

    pgAdmin or DBeaver

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

    Tables and columns

    marketing_campaigns 1,000 rows

    ColumnPostgreSQL type
    campaign_idVARCHAR(10)
    campaign_nameVARCHAR(50)
    channelVARCHAR(20)
    start_dateDATE
    end_dateDATE
    budgetDECIMAL(8,2)
    spendDECIMAL(10,2)
    impressionsINTEGER
    clicksINTEGER
    ctrDECIMAL(8,4)
    conversionsINTEGER
    cpaDECIMAL(8,2)
    revenueDECIMAL(11,2)
    roasDECIMAL(7,2)

    Practice questions

    answers from PostgreSQL 15.18

    What are spend, revenue and ROAS (revenue ÷ spend) by channel?

    SQL

    SELECT channel, COUNT(*) AS campaigns, ROUND(SUM(spend), 2) AS spend, ROUND(SUM(revenue), 2) AS revenue,
           ROUND(1.0 * SUM(revenue) / NULLIF(SUM(spend), 0), 2) AS roas
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY roas DESC, channel;

    Multiplying by 1.0 avoids integer division.

    Result

    channelcampaignsspendrevenueroas
    Email1571,314,413.046,896,058.465.25
    Affiliate1621,609,714.328,018,193.684.98
    Paid search1721,587,707.195,817,976.683.66
    Social1561,715,189.904,893,437.332.85
    Display1711,502,392.362,958,804.941.97
    Video1821,649,066.573,172,947.981.92
    Which 5 campaigns have the best ROAS?

    SQL

    SELECT campaign_id, campaign_name, roas
    FROM marketing_campaigns
    ORDER BY roas DESC, campaign_id
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    campaign_idcampaign_nameroas
    MC-0828Affiliate - Spring Sale - Q2 202312.32
    MC-1218Email - New Product - Q4 202311.36
    MC-0771Affiliate - Spring Sale - Q3 202411.10
    MC-0779Affiliate - Spring Sale - Q3 202310.91
    MC-1485Email - Holiday - Q3 202310.89
    How long do campaigns run on average, in days, per channel?

    SQL

    SELECT channel, ROUND(AVG((end_date - start_date)), 2) AS avg_days
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY avg_days DESC, channel;

    Subtracting one DATE from another returns the number of days as an integer.

    Result

    channelavg_days
    Affiliate37.63
    Social37.46
    Display36.74
    Paid search36.24
    Video35.79
    Email35.27
    How many campaigns spent more than their budget?

    SQL

    SELECT COUNT(*) AS over_budget
    FROM marketing_campaigns
    WHERE spend > budget;

    Result

    over_budget
    149
    What was the total spend and number of conversions per start year?

    SQL

    SELECT EXTRACT(YEAR FROM start_date)::int AS start_year, ROUND(SUM(spend), 2) AS spend, SUM(conversions) AS conversions
    FROM marketing_campaigns
    GROUP BY EXTRACT(YEAR FROM start_date)::int
    ORDER BY start_year;

    EXTRACT(YEAR FROM date) returns a number; ::int casts it to integer.

    Result

    start_yearspendconversions
    20235,052,869.99163216
    20244,325,613.39136386
    Which channel has the lowest cost per acquisition (spend ÷ conversions)?

    SQL

    SELECT channel, ROUND(1.0 * SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cpa
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY cpa, channel;

    Multiplying by 1.0 avoids integer division.

    Result

    channelcpa
    Email20.28
    Affiliate21.37
    Paid search28.74
    Social37.50
    Display52.09
    Video55.67

    PostgreSQL differences used here

    • Subtracting one DATE from another returns the number of days as an integer.
    • Multiplying by 1.0 avoids integer division.
    • LIMIT n at the end returns the first n rows.
    • EXTRACT(YEAR FROM date) returns a number; ::int casts it to integer.