Marketing campaigns sample database for PostgreSQL
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.
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.sqlpgAdmin or DBeaver
Create a database, open the Query Tool, open the .sql file and run it.
Tables and columns
marketing_campaigns 1,000 rows
| Column | PostgreSQL type |
|---|---|
| campaign_id | VARCHAR(10) |
| campaign_name | VARCHAR(50) |
| channel | VARCHAR(20) |
| start_date | DATE |
| end_date | DATE |
| budget | DECIMAL(8,2) |
| spend | DECIMAL(10,2) |
| impressions | INTEGER |
| clicks | INTEGER |
| ctr | DECIMAL(8,4) |
| conversions | INTEGER |
| cpa | DECIMAL(8,2) |
| revenue | DECIMAL(11,2) |
| roas | DECIMAL(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
| channel | campaigns | spend | revenue | roas |
|---|---|---|---|---|
| 157 | 1,314,413.04 | 6,896,058.46 | 5.25 | |
| Affiliate | 162 | 1,609,714.32 | 8,018,193.68 | 4.98 |
| Paid search | 172 | 1,587,707.19 | 5,817,976.68 | 3.66 |
| Social | 156 | 1,715,189.90 | 4,893,437.33 | 2.85 |
| Display | 171 | 1,502,392.36 | 2,958,804.94 | 1.97 |
| Video | 182 | 1,649,066.57 | 3,172,947.98 | 1.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_id | campaign_name | roas |
|---|---|---|
| MC-0828 | Affiliate - Spring Sale - Q2 2023 | 12.32 |
| MC-1218 | Email - New Product - Q4 2023 | 11.36 |
| MC-0771 | Affiliate - Spring Sale - Q3 2024 | 11.10 |
| MC-0779 | Affiliate - Spring Sale - Q3 2023 | 10.91 |
| MC-1485 | Email - Holiday - Q3 2023 | 10.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
| channel | avg_days |
|---|---|
| Affiliate | 37.63 |
| Social | 37.46 |
| Display | 36.74 |
| Paid search | 36.24 |
| Video | 35.79 |
| 35.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_year | spend | conversions |
|---|---|---|
| 2023 | 5,052,869.99 | 163216 |
| 2024 | 4,325,613.39 | 136386 |
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
| channel | cpa |
|---|---|
| 20.28 | |
| Affiliate | 21.37 |
| Paid search | 28.74 |
| Social | 37.50 |
| Display | 52.09 |
| Video | 55.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.