10 SQL Practice Projects for Your Portfolio

The best way to prove you know SQL is to show finished projects that answer real business questions. Below are 10 SQL practice projects, ordered from beginner to advanced, each with a dataset idea, the skills it demonstrates and the questions to answer. Build three or four of them well, publish them on GitHub, and you will have a portfolio that hiring managers can actually read.

How to choose and set up your projects

Recruiters skim. A project with a clear question, clean SQL and a short written conclusion beats ten half-finished notebooks. Before you start, set up a free local database: PostgreSQL is a solid default, SQLite needs no server at all, and MySQL or SQL Server Express work too. If you are unsure which one to pick, see PostgreSQL vs MySQL vs SQL Server for analytics.

For data, you have three good options:

• Classic sample databases such as Chinook (a music store), Northwind (a trading company), Sakila (a DVD rental store for MySQL) and the PostgreSQL DVD rental sample. They are realistic, relational and well documented.
• Public open data from city and government portals or Kaggle. Always read the license and cite the source in your README.
• Your own generated data made with Python or an AI assistant, useful when you want a specific business scenario.

A simple structure for every project

Keep each project in its own GitHub folder with the same layout so reviewers know where to look:

retail-sales-sql/
  README.md          -- question, data source, findings
  data/              -- small CSVs or a link to the source
  sql/
    01_setup.sql     -- CREATE TABLE and load steps
    02_cleaning.sql
    03_analysis.sql
  results/           -- exported tables or chart screenshots

Numbered SQL files show the order of your work, and comments inside each query explain why you made a choice, not just what the code does. Keep files small enough that someone can read them in a few minutes, and never commit passwords or connection strings.

Beginner projects (1 to 3)

1. Retail sales exploration

Dataset: Northwind or any orders and products dataset. Skills: SELECT, WHERE, ORDER BY, GROUP BY, basic joins. Questions: What are the top 10 products by revenue? Which month had the highest sales? Which customers have not ordered in the last six months?

2. Music store customer analysis

Dataset: Chinook. Skills: multi-table joins, aggregation, HAVING. Questions: Which genres earn the most per country? Which sales support agent generates the most revenue? Which customers bought from more than five genres?

3. Data cleaning audit

Dataset: a deliberately messy CSV (open data portals are full of them). Skills: CASE, TRIM, COALESCE, type casting, finding duplicates. Deliverable: a "before and after" report counting nulls, duplicates and invalid values, plus the SQL that fixed them. Cleaning is a huge part of real analyst work, so this project tells employers a lot.

Intermediate projects (4 to 7)

4. Customer cohort retention

Skills: date functions, CTEs, self-joins. Group customers by the month of their first purchase and calculate what share return in each following month. This is a classic interview topic and a classic dashboard.

5. RFM segmentation

Skills: window functions like NTILE. Score each customer on Recency, Frequency and Monetary value and label segments such as "champions" or "at risk." Here is a working PostgreSQL version:

with customer_stats as (
    select
        customer_id,
        current_date - max(order_date)  as days_since_last,
        count(*)                        as frequency,
        sum(amount)                     as monetary
    from orders
    group by customer_id
),
scored as (
    select
        customer_id,
        ntile(5) over (order by days_since_last desc) as r_score,
        ntile(5) over (order by frequency)            as f_score,
        ntile(5) over (order by monetary)             as m_score
    from customer_stats
)
select
    customer_id, r_score, f_score, m_score,
    case
        when r_score >= 4 and f_score >= 4 then 'Champion'
        when r_score <= 2 and f_score >= 4 then 'At risk'
        when r_score >= 4 and f_score <= 2 then 'New or occasional'
        else 'Regular'
    end as segment
from scored
order by r_score desc, f_score desc;

Ordering recency descending means the most recent buyers land in bucket 5, so a higher score is always better. If NTILE and OVER are new, read SQL window functions explained first.

6. Sales funnel analysis

Dataset: web or app event data (visit, add to cart, checkout, purchase). Skills: conditional aggregation with COUNT(DISTINCT CASE WHEN ...). Questions: Where do users drop off? Does the conversion rate differ by device or traffic source?

7. Inventory and reorder report

Skills: joins across products, stock and sales; running totals with window functions. Calculate average daily sales per product, days of stock remaining and which items need reordering. Operations and distribution teams use this exact logic every week.

Advanced projects (8 to 10)

8. Build a small data warehouse

Skills: dimensional modeling, CREATE TABLE, INSERT ... SELECT. Turn a normalized database like Chinook into a star schema with a sales fact table and dimensions for customer, product and date. Then connect Power BI or Tableau to it. Extra credit: rebuild the transformations with dbt and add tests.

9. Year-over-year KPI dashboard queries

Skills: LAG, date truncation, percentage calculations, handling division by zero with NULLIF. Produce monthly revenue, order count and average order value with month-over-month and year-over-year growth, ready to feed a dashboard.

10. Query performance tuning

Skills: indexes, EXPLAIN or EXPLAIN ANALYZE, rewriting slow queries. Generate a table with a few million rows, write a slow report query, then document how indexes and query changes improved it. Few beginner portfolios include this, so it stands out.

Summary table

Using AI without cheating yourself

AI assistants are great for generating realistic sample data, explaining error messages and reviewing your queries. They are a poor substitute for writing the first draft yourself, because the point of a portfolio is to show your own reasoning, and interviewers will ask you to explain every line.

When your projects are ready, present them well. The guide on building a data analyst portfolio covers GitHub structure, case-study write-ups and how to link projects from your resume.

Next steps

Pick one beginner project and one intermediate project this week. Set up PostgreSQL or SQLite, load a sample database, write your questions before you write any SQL, and finish each project with a README and three findings. Once both are on GitHub, move on to the cohort or RFM project and connect the results to a simple Power BI or Tableau dashboard. Three polished projects are enough to start applying.

Watch: video lessons

▶

Frequently asked questions