DataBrighten

    Marketing campaigns sample database for SQL Server

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

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

    textnumberdateyes/no

    Marketing campaigns database for SQL Server

    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 SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, 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 SQL Server 2022 16.0.4325.4 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

    sqlcmd

    sqlcmd -S localhost -Q "CREATE DATABASE practice"
    sqlcmd -S localhost -d practice -i marketing-campaigns-sqlserver.sql

    SSMS or Azure Data Studio

    Create a database, open the .sql file in a new query window connected to it, and press Execute.

    Tables and columns

    marketing_campaigns 1,000 rows

    ColumnSQL Server type
    campaign_idNVARCHAR(10)
    campaign_nameNVARCHAR(50)
    channelNVARCHAR(20)
    start_dateDATE
    end_dateDATE
    budgetDECIMAL(8,2)
    spendDECIMAL(10,2)
    impressionsINT
    clicksINT
    ctrDECIMAL(8,4)
    conversionsINT
    cpaDECIMAL(8,2)
    revenueDECIMAL(11,2)
    roasDECIMAL(7,2)

    Practice questions

    answers from SQL Server 2022 16.0.4325.4

    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(CAST(SUM(revenue) AS DECIMAL(18,6)) / NULLIF(SUM(spend), 0), 2) AS roas
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY roas DESC, channel;

    Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.

    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 TOP 5 campaign_id, campaign_name, roas
    FROM marketing_campaigns
    ORDER BY roas DESC, campaign_id;

    SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.

    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(CAST(DATEDIFF(day, start_date, end_date) AS DECIMAL(18,4))), 2) AS avg_days
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY avg_days DESC, channel;

    DATEDIFF(day, earlier, later) returns the number of days between two dates.

    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 YEAR(start_date) AS start_year, ROUND(SUM(spend), 2) AS spend, SUM(conversions) AS conversions
    FROM marketing_campaigns
    GROUP BY YEAR(start_date)
    ORDER BY start_year;

    YEAR(date) returns the year as a number.

    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(CAST(SUM(spend) AS DECIMAL(18,6)) / NULLIF(SUM(conversions), 0), 2) AS cpa
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY cpa, channel;

    Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.

    Result

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

    SQL Server differences used here

    • DATEDIFF(day, earlier, later) returns the number of days between two dates.
    • Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
    • SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
    • YEAR(date) returns the year as a number.