Career

SQL Projects for Your Portfolio: Six Ideas, Each With a Starter Query

By Owen Middleton · Updated November 2026 · We ran the examples on PostgreSQL 17

In good SQL projects for your portfolio, you answer one business question with queries on public data, and you show each query with its result. Here are six ideas:

  1. monthly sales with year-to-date revenue
  2. the customers who bring the most revenue
  3. session paths that end in a purchase
  4. session length by current plan
  5. pay raises by department
  6. a data check: orders with no line items

For each idea, we show a starter query and its result. You can change and run the query of project 3 on this page.

The data in our tables is from three made-up companies. Read the starter queries and their results to see the shape of each answer. Then do the same project on a public dataset, and publish that version. For projects 1, 2 and 6, we give the steps to load a public dataset and the changed queries.

We make SQLMaxx Learn, a free SQL course, and we link to its lessons on this page.

What kind of SQL projects belong in a data portfolio?

For a data portfolio, build analysis projects. In an analysis project, you ask a business question, write queries on a dataset, and publish the queries, the results and your findings.

In some lists of SQL projects, you also find a different kind: a database or app build. In these projects, you design tables and write INSERT, UPDATE and DELETE statements for an app. In a database build, you show database design. In an analysis project, you show how you answer a question with a query. This page is for a data analyst portfolio, so all six projects are analysis projects.

There are no lessons on INSERT, UPDATE or DELETE in SQLMaxx Learn, so for a database build, use a different course. Lesson 41, "Temp Tables and CREATE TABLE AS SELECT", is the one lesson on a table that you make from a query.

Six SQL portfolio projects, with a starter query each

All six are SQL projects for a data analyst portfolio. For each project, we give the question, the tables, the SQL skills with our lessons, and a starter query with its result. Then we say what to show in the finished project.

Project 6 is a data check. Do a check like it first, in every project.

If you want projects on SQL for beginners, start with project 6, and then add it to project 1 or 2. For project 6, you need only lessons from Foundations and Core SQL, the first two of the five levels of our course. For each of the other five projects, you need one or more lessons from a higher level.

For projects 1, 2 and 6, you use two tables of Brightlane, a made-up online store:

  • orders: one row for each order
  • order_items: one row for each product in an order
Schema · ecommerce2 tables? = nullable
orders
idinteger
customer_id?integer
ordered_attimestamptz
statustext
total_amountnumeric
order_items
idinteger
order_id?integer
product_id?integer
quantityinteger
unit_pricenumeric

Project 1: monthly sales and year-to-date revenue

Question: what is the revenue of each month, and the revenue from January 1 to the end of that month? Table: orders. SQL skills: GROUP BY (lesson 14, "GROUP BY") and date truncation (lesson 33, "Date Truncation and Extraction"). For running totals, practice Running Totals and Cumulative Metrics in lesson 64.

Count only delivered orders as revenue. In our orders table, ordered_at is a timestamp with a time zone (timestamptz). Use AT TIME ZONE 'UTC' to put each order in its month in UTC. If you do not, PostgreSQL uses the time zone of your session, and a reader in Tokyo gets different totals for some months.

WITH monthly AS (
  SELECT date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month,
         SUM(total_amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY 1
)
SELECT month,
       revenue,
       SUM(revenue) OVER (PARTITION BY EXTRACT(YEAR FROM month) ORDER BY month) AS revenue_ytd
FROM monthly
WHERE month >= DATE '2024-01-01'
ORDER BY month;
monthrevenuerevenue_ytd
2024-01-01 2145.00 2145.00
2024-02-01 3096.99 5241.99
2024-03-01 2596.00 7837.99
2024-04-01 2794.00 10631.99
2024-05-01 1557.98 12189.97
2024-06-01 3394.99 15584.96
2024-07-01 8843.00 24427.96
2024-08-01 2634.99 27062.95
2024-09-01 3145.99 30208.94
2024-10-01 3795.00 34003.94
2024-11-01 4246.00 38249.94
2024-12-01 2745.00 40994.94
2025-01-01 3393.00 3393.00
2025-02-01 8038.00 11431.00
2025-03-01 8786.00 20217.00
delivered revenue by month (UTC) and revenue since January 1

The window SUM(revenue) OVER (PARTITION BY EXTRACT(YEAR FROM month) ORDER BY month) is the revenue from January of the same year to the end of the month on that row.

What you see: at Brightlane, the revenue from January to March is 20,217.00 in 2025 and 7,837.99 in 2024. The last order is on March 22, 2025 (UTC), so there is no data after that day. The first order is from February 2022. We use WHERE month >= DATE '2024-01-01' to show only the months from January 2024, so that the table is short. In project 2, we use the delivered orders of all years, so the revenue total there is larger.

For your portfolio: on a public dataset, show the monthly table and one line chart. Write one finding about the trend. Write down which orders you count as revenue, and why.

Project 2: the customers who bring the most revenue

Question: which customers bring the most revenue, and what share of the revenue does each group of customers bring? Table: orders. SQL skills: CTEs (lesson 30, "Common Table Expressions (CTEs)") and NTILE (lesson 45, "NTILE and Percentile Functions").

In the first CTE, get the spend of each customer on delivered orders. Then use NTILE(4) to put the customers into four groups of about the same size, from the highest spend to the lowest. Put customer_id after spent in the ORDER BY, so that customers with the same spend are always in the same order.

SUM(SUM(spent)) OVER () is the total revenue of all four groups. Divide the revenue of each group by this total to get pct_of_revenue.

WITH spend AS (
  SELECT customer_id, SUM(total_amount) AS spent
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
),
grouped AS (
  SELECT customer_id, spent,
         NTILE(4) OVER (ORDER BY spent DESC, customer_id) AS spend_group
  FROM spend
)
SELECT spend_group,
       COUNT(*) AS customers,
       MIN(spent) AS min_spent,
       MAX(spent) AS max_spent,
       SUM(spent) AS revenue,
       ROUND(100.0 * SUM(spent) / SUM(SUM(spent)) OVER (), 1) AS pct_of_revenue
FROM grouped
GROUP BY spend_group
ORDER BY spend_group;
spend_groupcustomersmin_spentmax_spentrevenuepct_of_revenue
1 15 2597.00 5096.00 51058.95 48.9
2 15 1548.00 2246.00 27426.94 26.3
3 15 1048.00 1547.00 18982.98 18.2
4 14 129.99 898.00 6862.94 6.6
customers in four spend groups, the spend range of each group, and each group's share of delivered revenue

What you see: at Brightlane, 59 customers have delivered orders. The 15 customers in the top group bring 48.9% of the revenue from delivered orders. The 14 customers in the bottom group bring 6.6%.

For your portfolio: show the table of groups, with the lowest and the highest spend in each group (min_spent and max_spent). Give one recommendation for the business.

Project 3: session paths that end in a purchase

For projects 3 and 4, you use tables of Streamhub, a made-up B2B SaaS analytics company:

  • users: one row for each user
  • sessions: one row for each session of a user
  • events: one row for each action in the product
  • conversions: one row for each user with a plan change in this table (only for a note in project 4)

Below is the analytics schema, with the four tables.

Schema · analytics4 tables? = nullable
users
idinteger
nametext
emailtext
countrytext
plantext
signed_up_attimestamptz
is_activeboolean
conversions
idinteger
user_id?integer
converted_attimestamptz
plantext
amountnumeric
sessions
idinteger
user_id?integer
started_attimestamptz
ended_at?timestamptz
event_countinteger
events
idinteger
user_id?integer
session_id?integer
event_typetext
occurred_attimestamptz
properties?jsonb

Question: what are the most common paths of events in a session, and which of them end in a purchase? Table: events. SQL skills: session analysis (lesson 65, "Sessionization and Funnel Analysis Patterns"). For STRING_AGG, see how STRING_AGG and ARRAY_AGG work in lesson 50.

Use STRING_AGG(event_type, ' > ' ORDER BY occurred_at, id) to join the events of each session into one text, in time order. Put id after occurred_at, so that two events at the same time are always in the same order. In the CTE, keep only the events with a session_id.

WITH paths AS (
  SELECT session_id,
         STRING_AGG(event_type, ' > ' ORDER BY occurred_at, id) AS path
  FROM events
  WHERE session_id IS NOT NULL
  GROUP BY session_id
)
SELECT path, COUNT(*) AS sessions
FROM paths
GROUP BY path
ORDER BY sessions DESC, path
LIMIT 7;
pathsessions
page_view > feature_used 25
page_view 11
page_view > feature_used > feature_used > upgrade_clicked > checkout > purchase 8
page_view > upgrade_clicked > checkout > purchase 8
page_view > upgrade_clicked 4
page_view > feature_used > upgrade_clicked 3
page_view > feature_used > feature_used > feature_used > upgrade_clicked > checkout > purchase 2
the seven most common event paths in a session

What you see: in the Streamhub data, there are rows in the events table for 68 sessions, and you get 14 different paths. There is a purchase in 19 sessions, and in each of them, the purchase is the last event. The most common path is page_view > feature_used, in 25 sessions.

For 112 of the 180 sessions, there are no rows in events. For 1 event, there is no session_id. For each of the 180 sessions, the event_count value in sessions is larger than the number of rows in events. Find this type of problem with a data check, and report it as in project 6.

Change the query here. For example, remove LIMIT 7 to see all 14 paths. Or add WHERE path LIKE '%purchase' before the last GROUP BY to see only the 4 paths that end in a purchase.

WITH paths AS (
  SELECT session_id,
         STRING_AGG(event_type, ' > ' ORDER BY occurred_at, id) AS path
  FROM events
  WHERE session_id IS NOT NULL
  GROUP BY session_id
)
SELECT path, COUNT(*) AS sessions
FROM paths
GROUP BY path
ORDER BY sessions DESC, path
LIMIT 7;

For your portfolio: on public event data, show the top paths. Show the share of the sessions with events that have a purchase. From the paths, write one question for the product team.

We found no public event dataset that we can recommend for projects 3 and 4. We checked one, the "eCommerce behavior data from multi category store" on Kaggle, and its license is "Data files © Original Authors". For these projects, you need a table of events with a session id, an event type and a time. For project 4, you also need sessions with a start time, an end time and the plan of the user. Use only data that you can publish under its license.

Project 4: session length by current plan

Question: how long is a session, by the plan that each user has today? Tables: sessions and users. SQL skills: INNER JOIN (lesson 17, "INNER JOIN"), date arithmetic (lesson 34, "Date Arithmetic and Intervals") and percentiles (lesson 45, "NTILE and Percentile Functions").

Join sessions to users to get the current plan of the user of each session. EXTRACT(EPOCH FROM s.ended_at - s.started_at) / 60 is the length of a session in minutes, and PERCENTILE_CONT(0.5) is the median. In Streamhub, there is no ended_at for an open session, so keep only the sessions with an end time: WHERE s.ended_at IS NOT NULL.

SELECT u.plan,
       COUNT(*) AS sessions,
       ROUND(AVG(EXTRACT(EPOCH FROM s.ended_at - s.started_at) / 60)::numeric, 1) AS avg_minutes,
       PERCENTILE_CONT(0.5) WITHIN GROUP (
         ORDER BY EXTRACT(EPOCH FROM s.ended_at - s.started_at) / 60
       ) AS median_minutes
FROM sessions s
JOIN users u ON u.id = s.user_id
WHERE s.ended_at IS NOT NULL
GROUP BY u.plan
ORDER BY avg_minutes DESC;
plansessionsavg_minutesmedian_minutes
enterprise 30 70.3 75
pro 71 50.1 50
starter 49 42.0 45
free 18 35.6 37.5
sessions, average and median length in minutes, by plan

What you see: at Streamhub, the sessions of users on the enterprise plan today are the longest, with an average of 70.3 minutes and a median of 75. The sessions of users on the free plan are the shortest, with an average of 35.6 and a median of 37.5.

For 73 of the 168 sessions with an end time, there is a plan change of the same user in the conversions table. Each of these changes is after the start of the session. So treat this split by plan as a first look.

For the plan at the time of each session, you need a history of plan changes. We do not use conversions in the starter query, and some plan changes are not in it. In conversions, there are 60 rows, one for each user with a conversion. For 22 of these 60 users, the plan today is not the plan in their row, so some plan changes are not in this table.

For your portfolio: say which plan you use: the plan today or the plan at the time of the session. Show the average and the median for each plan, and explain why the two are different. Write down your rule for open sessions.

Project 5: pay raises by department

For project 5, you use three tables of Helix Systems, a made-up tech company:

  • employees: one row for each employee
  • departments: one row for each department
  • salaries: one row for each salary of an employee, with its effective_date
Schema · hr3 tables? = nullable
departments
idinteger
nametext
locationtext
budgetnumeric
salaries
idinteger
employee_id?integer
amountnumeric
effective_datedate
end_date?date
employees
idinteger
nametext
emailtext
department_id?integer
manager_id?integer
hire_datedate
titletext
is_activeboolean

Question: how big are the pay raises in each department? Tables: salaries, employees and departments. SQL skills: LAG (lesson 42, "LAG and LEAD") and joins of three tables (lesson 22, "Joining Multiple Tables").

Use LAG(amount) OVER (PARTITION BY employee_id ORDER BY effective_date) to get the previous salary of the same employee on each salary row, in a new column, prev_amount. For the first salary of an employee, there is no previous salary, so prev_amount is NULL. Remove these rows with WHERE c.prev_amount IS NOT NULL.

WITH changes AS (
  SELECT employee_id, amount, effective_date,
         LAG(amount) OVER (PARTITION BY employee_id ORDER BY effective_date) AS prev_amount
  FROM salaries
)
SELECT d.name AS department,
       COUNT(*) AS changes,
       ROUND(AVG(100.0 * (c.amount - c.prev_amount) / c.prev_amount), 1) AS avg_change_pct
FROM changes c
JOIN employees e ON e.id = c.employee_id
JOIN departments d ON d.id = e.department_id
WHERE c.prev_amount IS NOT NULL
GROUP BY d.name
ORDER BY avg_change_pct DESC, d.name;
departmentchangesavg_change_pct
Support 5 16.8
Sales 9 16.7
Marketing 5 16.3
HR 4 15.7
Data 6 15.5
Finance 4 15.0
Engineering 16 14.3
Product 7 13.9
salary changes and the average change in percent, by department

What you see: at Helix Systems, each of the 56 salary changes is a raise. The average raise is 16.8% in Support and 13.9% in Product. In some departments, there are only 4 raises. The department on each row is the current department of the employee.

For your portfolio: show the raise table and your method: two salary rows of one person, in date order. Write one caveat, for example the small groups.

Project 6: find orders with no line items (a SQL data check)

Question: which orders have no line items, and how many of them are delivered orders? Tables: orders and order_items. SQL skills: LEFT JOIN (lesson 18, "LEFT JOIN and RIGHT JOIN"), IS NULL (lesson 5, "NULL Semantics and IS NULL") and GROUP BY (lesson 14, "GROUP BY").

Use LEFT JOIN to keep every order. For an order with no line item, each column of order_items is NULL, so use WHERE oi.id IS NULL to keep only those orders.

SELECT o.status,
       COUNT(*) AS orders_with_no_items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE oi.id IS NULL
GROUP BY o.status
ORDER BY o.status;
statusorders_with_no_items
cancelled 11
delivered 77
pending 11
shipped 10
orders with no line item, by status

What you see: at Brightlane, there is no line item for 109 of the 200 orders. Of the 161 delivered orders, there is no line item for 77. In projects 1 and 2, we use total_amount in orders as the revenue, so we still count these orders there. If you count the products sold from order_items, you miss 77 delivered orders.

For your portfolio: put a section on data checks in your README. For each check, give the query, the count, and what you do about it. Say which source of revenue you use, and why.

What to show in a finished SQL portfolio project

In a finished SQL portfolio project, show these five things:

  1. The business question, in one sentence.
  2. The data source, with its link and its license.
  3. Your data checks, and what you did about each one.
  4. Each query with its result, as a table or a screenshot. Show the query, not only the finding.
  5. Your findings and one recommendation.

Abid Ali Awan of KDnuggets gives similar advice: "To make it portfolio-ready, include your SQL queries, a short business summary, and simple visualizations" (KDnuggets, 5 Real-World SQL Projects to Build Your Data Portfolio). In our view, put each project in a GitHub repository, with the findings in the README file.

Where to find SQL projects and the data for them

You find SQL project ideas in lists like this one, and the data for them in public datasets, for example on Kaggle and on city open-data portals.

The Olist data on Kaggle, for SQL projects 1, 2 and 6

In the "Brazilian E-Commerce Public Dataset by Olist" on Kaggle, there is "information of 100k orders from 2016 to 2018 made at multiple marketplaces in Brazil" (Kaggle, Brazilian E-Commerce Public Dataset by Olist). The license is CC BY-NC-SA 4.0. The data is in CSV files. Load three of them into PostgreSQL:

  1. Download olist_orders_dataset.csv, olist_order_items_dataset.csv and olist_customers_dataset.csv from the dataset page.
  2. Make the three tables, with the columns of the files in the same order:
CREATE TABLE orders (
  order_id text PRIMARY KEY,
  customer_id text,
  order_status text,
  order_purchase_timestamp timestamp,
  order_approved_at timestamp,
  order_delivered_carrier_date timestamp,
  order_delivered_customer_date timestamp,
  order_estimated_delivery_date timestamp
);

CREATE TABLE order_items (
  order_id text,
  order_item_id integer,
  product_id text,
  seller_id text,
  shipping_limit_date timestamp,
  price numeric,
  freight_value numeric
);

CREATE TABLE customers (
  customer_id text PRIMARY KEY,
  customer_unique_id text,
  customer_zip_code_prefix text,
  customer_city text,
  customer_state text
);
  1. In psql, load each file with \copy. In each file, the first line is the column names, so add HEADER:
\copy orders FROM 'olist_orders_dataset.csv' WITH (FORMAT csv, HEADER)
\copy order_items FROM 'olist_order_items_dataset.csv' WITH (FORMAT csv, HEADER)
\copy customers FROM 'olist_customers_dataset.csv' WITH (FORMAT csv, HEADER)

In PostgreSQL, the command for a file is COPY. The documentation says: "COPY FROM copies data from a file to a table (appending the data to whatever is in the table already)" (PostgreSQL 17 documentation, COPY). For COPY with a file name, PostgreSQL reads the file on the database server. For a file on your own computer, use \copy in psql, as in step 3.

The names and keys in Olist are different from our tables, so change the starter queries:

  • Use order_id for id, order_status for status, and order_purchase_timestamp for ordered_at.
  • There is no order total in the orders file. We use the sum of price and freight_value in the order items. You can also use payment_value in olist_order_payments_dataset.csv. Write down which one you use.
  • There is no id column in the order items file. In project 6, use WHERE oi.order_id IS NULL.
  • In Olist, "Each order has a unique customer_id". For project 2, join the customers file and group by customer_unique_id.
  • In Olist, there is no time zone in order_purchase_timestamp, so load it as timestamp and do not use AT TIME ZONE 'UTC'. On a timestamp, AT TIME ZONE 'UTC' gives a timestamptz. Then PostgreSQL uses the time zone of your session in date_trunc, and a reader in Tokyo gets different months.

Here are the three changed queries. We ran them on the three Olist files on October 21, 2026.

Project 1 on Olist (no WHERE month filter, so you get every month with delivered orders). Olist has no orders in November 2016, so that month is not in the result.

WITH monthly AS (
  SELECT date_trunc('month', o.order_purchase_timestamp)::date AS month,
         SUM(oi.price + oi.freight_value) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.order_status = 'delivered'
  GROUP BY 1
)
SELECT month,
       revenue,
       SUM(revenue) OVER (PARTITION BY EXTRACT(YEAR FROM month) ORDER BY month) AS revenue_ytd
FROM monthly
ORDER BY month;

Project 2 on Olist, with the join to the customers file:

WITH spend AS (
  SELECT c.customer_unique_id, SUM(oi.price + oi.freight_value) AS spent
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.order_status = 'delivered'
  GROUP BY c.customer_unique_id
),
grouped AS (
  SELECT customer_unique_id, spent,
         NTILE(4) OVER (ORDER BY spent DESC, customer_unique_id) AS spend_group
  FROM spend
)
SELECT spend_group,
       COUNT(*) AS customers,
       MIN(spent) AS min_spent,
       MAX(spent) AS max_spent,
       SUM(spent) AS revenue,
       ROUND(100.0 * SUM(spent) / SUM(SUM(spent)) OVER (), 1) AS pct_of_revenue
FROM grouped
GROUP BY spend_group
ORDER BY spend_group;

Project 6 on Olist:

SELECT o.order_status,
       COUNT(*) AS orders_with_no_items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE oi.order_id IS NULL
GROUP BY o.order_status
ORDER BY o.order_status;

Other public data for SQL projects 3, 4 and 5

For project 5, use "Citywide Payroll Data (Fiscal Year)" on NYC Open Data, from the Office of Payroll Administration (NYC Open Data, Citywide Payroll Data (Fiscal Year)). In it, you find the base salary of each city employee by fiscal year. Use Fiscal Year for effective_date, Agency Name for the department, and Base Salary for amount.

  • There is no employee id column, so decide how you match one person across years.
  • The data of some agencies is redacted, and "Some of these redactions will appear as XXX in the name columns". You cannot match those rows by name.
  • In the description, the city tells readers that "increments of salary increases received over the course of any one fiscal year will not be reflected".
  • Read the terms of the portal before you publish.

For projects 3 and 4, we name no dataset. The data that you need is at the end of project 3.

There is also a different meaning of the words "SQL database project". Microsoft uses them for a development tool: "A SQL database project is a local representation of SQL objects that comprise the schema for a single database, such as tables, stored procedures, or functions" (Microsoft Learn, Get Started with SQL Database Projects). That is not a portfolio project.

Start SQLMaxx Learn lesson 1

SQLMaxx Learn is a free SQL course, where you learn SQL online from scratch. Lesson 1 is "SELECT and Column Expressions", and you can start it in your browser with no account.

In 63 of the 66 lessons, you solve graded problems on the same three made-up companies as this page. But there is no case-study project in the course, and no lesson on how to load your own data.

Questions about SQL portfolio projects

How many SQL projects do you need in a portfolio?

We found no source that gives a number. Abid Ali Awan of KDnuggets writes: "A small, well-explained project will always be more valuable than a large project with no clear story." In our view, one finished project with its queries, results and findings is better than three unfinished ones. Source: KDnuggets, 5 Real-World SQL Projects to Build Your Data Portfolio.

Where can you find SQL database files for a project?

Look on Kaggle and on city open-data portals. A dataset there is not always a database file. For example, the Olist dataset on Kaggle is a set of CSV files that you load into tables. The three Olist files for projects 1, 2 and 6 are about 42 MB together, with 99,441 orders, 112,650 order items and 99,441 customer rows. By customer_unique_id, there are 96,096 customers.

Do SQL projects help in a SQL interview?

In an interview, you can use your project as an example. You explain a query that you wrote and the result that you got. For a practice method and graded tasks, read our guide on SQL interview practice.