E-Commerce Analytics with SQL

Analyze 1M+ transactions using window functions, CTEs, and cohort analysis.

Introduction

30 Second Summary

Every online retailer sits on a goldmine of transaction data. The difference between a junior analyst and a senior one is knowing how to extract real business insights from raw numbers using advanced SQL.

In this project, you will analyze over 1 million real e-commerce transactions using PostgreSQL window functions, CTEs, and cohort analysis to answer the kinds of business questions that hiring managers love to see in portfolios.

What You'll Build

A complete SQL analytics project with 6 progressive scripts that demonstrate advanced querying techniques on a real UK online retailer dataset containing 1,067,371 transactions.

By the end of this project, you'll have:

A GitHub repository containing organized SQL scripts that clean, analyze, rank, and segment real e-commerce data.
A revenue trend analysis with running totals, moving averages, and month-over-month growth calculations using window functions.
A customer cohort retention matrix tracking how monthly customer groups behave over time.
An RFM segmentation model classifying customers into value tiers like Champions, Loyal Customers, and At Risk.
Secret Mission: Build a comprehensive customer analytics VIEW that combines all your KPIs into a single reusable query, then write a polished README for your GitHub repo.

Are there any prerequisites?

You should be comfortable with intermediate SQL concepts like JOINs, GROUP BY, and subqueries. You also need PostgreSQL installed with psql and pgAdmin available on your machine.

Basic familiarity with the command line is helpful. Git setup is covered in Step 1.

Set Up Git and Project Structure

A portfolio project is only as useful as its visibility. Without version control and a public repository, your SQL scripts live on your hard drive where no hiring manager can see them.

In this step, you will install Git, set up your project folder structure, and push your first commit to GitHub. This gives you a clean foundation for the SQL scripts you will write in later steps.

In this step, get ready to:

Install and configure Git.
Create a project folder with an organized subdirectory for SQL scripts.
Connect your local repository to GitHub and push your first commit.

Install and configure Git

Git tracks every change you make to your SQL files. It also lets you push your work to GitHub so anyone can review your analytics portfolio.

Check if Git is already installed by running this command in your terminal:

git --version

✔️ I see a version number

Git is ready to go. Skip ahead to the configuration step below.

ⓧ Command not found

You need to install Git. Follow the instructions for your operating system.

macOS

macOS includes Git as part of its command line tools. Running the version check may have triggered an install prompt automatically.

If you see an install dialog, click **Install** and wait for it to complete.
Once finished, run the version check again:

git --version

You should now see a version number like git version 2.x.x.

Windows

Download the Git installer from https://git-scm.com/downloads/win.
Run the downloaded installer.
Accept all the default options through each screen and click **Install**.
Close and reopen your terminal (PowerShell or Command Prompt) so it picks up the new installation.
Verify the installation by running:

git --version

You should see a version number like git version 2.x.x.

Now configure your identity so Git labels your commits correctly.

Set your name by running this command:

git config --global user.name "[[YOURNAME="Your Name"]]"

Set your email by running this command:

git config --global user.email "your-email@example.com"

Why set a name and email?

Every Git commit records who made the change. These values appear in your commit history on GitHub, so use the name and email associated with your GitHub account.

Confirm your configuration is saved by running:

git config --global --list

You should see your user.name and user.email in the output.

Git command still not found after installing?

Make sure you closed and reopened your terminal after installing. The terminal only reads new programs when it starts a fresh session.

On Windows, try opening a new PowerShell window instead of Command Prompt.

Help me troubleshoot my Git installation

Create your project folder and initialize Git

Your project needs a dedicated folder with a clear structure. Keeping SQL scripts in their own subdirectory makes the repository easy to navigate for anyone reviewing your work.

Move to your Desktop by running this command:

cd ~/Desktop

Create your project folder, move into it, and create the scripts subdirectory by running these commands:

mkdir retail-analytics
cd retail-analytics
mkdir scripts

Confirm the folder structure by running:

ls

You should see scripts listed in the output.

Initialize a Git repository in your project folder by running:

git init

You should see a message like Initialized empty Git repository in .../retail-analytics/.git/.

What does git init do?

This command creates a hidden .git folder inside your project. Git uses this folder to track every change you make to your files from this point forward.

Git does not track empty directories. Add a placeholder file inside scripts/ so Git includes it in your commits:

touch scripts/.gitkeep

Don't see the scripts folder in ls output?

Make sure you ran mkdir scripts from inside the retail-analytics folder. Run pwd to check your current location.

Help me find where my folder was created

Create a GitHub repository and push your first commit

With your local repo ready, you need a remote repository on GitHub to store your work online. This makes your portfolio publicly accessible.

Open your browser and go to github.com.
Sign in to your GitHub account. If you do not have one, click **Sign up** and create a free account.
Click the **+** icon in the top right corner of the GitHub page.
Select **New repository**.
Enter retail-analytics as the repository name.
Leave **Public** selected so hiring managers can view your work.
Do NOT check any of the initialization options (no README, no .gitignore, no license). You already have a local repo.
Click **Create repository**.

GitHub shows a quick setup page with your repository URL. Copy the HTTPS URL shown on this page.

Back in your terminal, connect your local repo to the remote by running:

git remote add origin [[YOUR_REPO_URL="https://github.com/YOUR_USERNAME/retail-analytics.git"]]

What does git remote add origin do?

This tells your local Git repository where to push code. The name origin is a standard alias for your primary remote repository on GitHub.

Stage all your files and make your first commit by running these commands:

git add .
git commit -m "Initial commit: project structure"

Push your commit to GitHub by running:

git push -u origin main

What if Git says the branch is called master?

Older Git versions default to master instead of main. If you see an error, rename your branch first:

git branch -M main
git push -u origin main

Verify the push by refreshing your GitHub repository page in the browser.

You should see your scripts/ directory listed on the repository page with the .gitkeep file inside it.

Push failed or asking for authentication?

GitHub no longer accepts passwords for command line pushes. If you see an authentication error, GitHub will prompt you to sign in through a browser popup or ask for a personal access token.

Make sure the URL you copied starts with https://. You can verify your remote is set correctly by running git remote -v.

Help me push my code to GitHub

Your project skeleton is live on GitHub. Next up, you will create the database and load over a million real e-commerce transactions into it.

Create the Database and Load the Dataset

Your project structure and Git repository are ready. Now it is time to bring in real data.

Every analytics project starts with loading raw data into a database where you can query it efficiently. In this step, you will download a real e-commerce dataset with over 1 million transactions, set up a PostgreSQL database, and load the data so it is ready for analysis.

In this step, get ready to:

Download the Online Retail II dataset from Kaggle.
Create the retail_analytics database and define the raw_retail table.
Load the CSV data and verify approximately 1 million rows are present.

Download the dataset from Kaggle

The Online Retail II dataset contains 1,067,371 real transactions from a UK-based online retailer, covering December 2009 to December 2011. It is freely available on Kaggle under a CC BY 4.0 license.

Open https://www.kaggle.com/datasets/mashlyn/online-retail-ii-uci in your browser.
Sign in to your Kaggle account (or create a free one).
Click the Download button to download the dataset as a CSV file.
Move the downloaded CSV file into your retail-analytics/ project folder on your Desktop.

What if I can only find an .xlsx file?

The UCI source (https://archive.ics.uci.edu/dataset/502/online+retail+ii) provides the dataset as .xlsx only. If you download from there, open the file in Excel or LibreOffice and save it as CSV (File > Save As > CSV UTF-8).

The Kaggle version is already CSV, so that is the easier path.

What columns does this dataset have?

The CSV contains 8 columns: Invoice, StockCode, Description, Quantity, InvoiceDate, Price, Customer ID, and Country. Some rows have missing Customer IDs and some invoices are cancellations (prefixed with 'C'). You will clean these up in the next step.

Create the database and write the table definition

Before you can load data, you need a dedicated database and a table whose columns match the CSV structure.

Open psql by pressing Cmd+Space (macOS) or the Windows key (Windows) to open your search bar, typing psql, and pressing Enter.
If prompted, enter your PostgreSQL password (the one you set during installation).
Create the database and connect to it by running these commands one at a time:

CREATE DATABASE retail_analytics;
\c retail_analytics

What do these commands do?

CREATE DATABASE retail_analytics; creates a new empty database on your PostgreSQL server.

\c retail_analytics switches your psql session to that database so all future commands run inside it.

You should see a message like You are now connected to database "retail_analytics".

Connection refused or password error?

Make sure the PostgreSQL service is running. On Windows, search for Services, find postgresql, and confirm its status is Running. On macOS, run brew services list to check.

If you forgot your password, you may need to reset it via pg_hba.conf. Help me connect to PostgreSQL on my machine

Now create the SQL script file that defines your table structure.

In VS Code, create a new file called 00_create_table.sql inside your scripts/ folder.
Paste the following table definition into the file:

-- 00_create_table.sql
-- Creates the database table and loads the Online Retail II dataset

-- Run this first: CREATE DATABASE retail_analytics;
-- Then connect to it: \c retail_analytics

DROP TABLE IF EXISTS raw_retail;

CREATE TABLE raw_retail (
    invoice_no      VARCHAR(10),
    stock_code      VARCHAR(20),
    description     TEXT,
    quantity        INTEGER,
    invoice_date    TIMESTAMP,
    unit_price      NUMERIC(10, 2),
    customer_id     INTEGER,
    country         VARCHAR(50)
);

-- Load data using psql \copy command (run from psql, not in a .sql file)
-- Adjust the file path to where you saved the CSV:
-- \copy raw_retail(invoice_no, stock_code, description, quantity, invoice_date, unit_price, customer_id, country) FROM 'C:/path/to/online_retail_II.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

-- Verify the load
SELECT COUNT(*) AS total_rows FROM raw_retail;
SELECT * FROM raw_retail LIMIT 10;

What does this code do?

DROP TABLE IF EXISTS raw_retail; removes any existing table with that name so you can re-run the script safely.

CREATE TABLE raw_retail (...) defines 8 columns that match the CSV file's structure. The column names use snake_case (like invoice_no) even though the CSV headers use different formatting. The \copy command loads data by column position, not by header name.

The commented \copy line is a reference for the next substep. You will run it directly in psql with your own file path.

Save scripts/00_create_table.sql.
Back in your psql session (still connected to retail_analytics), run the CREATE TABLE portion by executing:

DROP TABLE IF EXISTS raw_retail;

CREATE TABLE raw_retail (
    invoice_no      VARCHAR(10),
    stock_code      VARCHAR(20),
    description     TEXT,
    quantity        INTEGER,
    invoice_date    TIMESTAMP,
    unit_price      NUMERIC(10, 2),
    customer_id     INTEGER,
    country         VARCHAR(50)
);

You should see CREATE TABLE printed in your psql session, confirming the table exists.

Seeing a syntax error?

Make sure you include the semicolon at the end of the statement. If you accidentally hit Enter before the semicolon, psql waits for more input. Type ; on its own line and press Enter to complete the statement.

Help me fix a syntax error when creating my table in psql

Load the CSV data and verify the row count

The \copy command is a psql client-side command that loads data using your own file permissions. This is easier than the server-side COPY command, which requires the PostgreSQL service account to have access to the file path.

In your psql session, run the following command, replacing the file path with the actual location of your CSV file:

\copy raw_retail(invoice_no, stock_code, description, quantity, invoice_date, unit_price, customer_id, country) FROM [[CSV_PATH="C:/Users/YourName/Desktop/retail-analytics/online_retail_II.csv"]] WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

What does this command do?

\copy reads the CSV from your local filesystem and inserts each row into raw_retail. The columns in parentheses tell PostgreSQL which table column maps to each CSV column (by position, left to right).

HEADER true skips the first row of the CSV (the column headers). ENCODING 'UTF8' handles special characters in product descriptions.

This may take 30 to 60 seconds depending on your machine. Wait for the COPY 1067371 confirmation (or a number close to it).

Verify the data loaded correctly by running:

SELECT COUNT(*) AS total_rows FROM raw_retail;
SELECT * FROM raw_retail LIMIT 10;

You should see approximately 1,067,371 total rows and a sample of 10 rows showing invoice numbers, product descriptions, quantities, and prices.

Getting a 'file not found' error?

Double check the file path you entered. On Windows, use forward slashes (C:/Users/...) or escaped backslashes (C:\\Users\\...). On macOS, the path typically starts with /Users/yourname/Desktop/....

Make sure the CSV file is in the exact location you specified and that the filename matches (including the extension).

Help me fix the file path for my \copy command in psql

Use pgAdmin import wizard as an alternative

If you prefer a graphical approach, right-click the raw_retail table in pgAdmin, select Import/Export Data, toggle to Import, select your CSV file, set Header to Yes, and click OK.

Commit your script to Git by running these commands in your terminal (not psql):

cd ~/Desktop/retail-analytics
git add scripts/00_create_table.sql
git commit -m "Add table creation script for raw_retail"
git push origin main

Why commit the SQL file but not the CSV?

SQL scripts are lightweight and represent your analytical work. The CSV file is 100+ MB of raw data that anyone can re-download from Kaggle. Keeping large data files out of Git keeps your repository fast and clean.

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/00_create_table.sql and pushed your commit to GitHub.

ⓧ I'd like to double check the full code

Here is the complete scripts/00_create_table.sql file:

-- 00_create_table.sql
-- Creates the database table and loads the Online Retail II dataset

-- Run this first: CREATE DATABASE retail_analytics;
-- Then connect to it: \c retail_analytics

DROP TABLE IF EXISTS raw_retail;

CREATE TABLE raw_retail (
    invoice_no      VARCHAR(10),
    stock_code      VARCHAR(20),
    description     TEXT,
    quantity        INTEGER,
    invoice_date    TIMESTAMP,
    unit_price      NUMERIC(10, 2),
    customer_id     INTEGER,
    country         VARCHAR(50)
);

-- Load data using psql \copy command (run from psql, not in a .sql file)
-- Adjust the file path to where you saved the CSV:
-- \copy raw_retail(invoice_no, stock_code, description, quantity, invoice_date, unit_price, customer_id, country) FROM 'C:/path/to/online_retail_II.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

-- Verify the load
SELECT COUNT(*) AS total_rows FROM raw_retail;
SELECT * FROM raw_retail LIMIT 10;

Your database is loaded with over a million real transactions. Next up, you will clean this raw data using CTEs to remove cancellations, null customers, and invalid records.

Clean the Data with CTEs

Your database is loaded with over a million rows of real transaction data. Before you can analyze it, you need to deal with a common reality of raw data: it's messy.

The Online Retail II dataset includes cancelled orders, missing customer IDs, and negative quantities. In this step, you'll use a Common Table Expression (CTE) to filter out the noise and create a reusable VIEW that every future script will query against.

In this step, get ready to:

Diagnose data quality issues in the raw dataset.
Create a VIEW called clean_retail that filters out bad records using a CTE.
Verify the cleaned data with summary queries and commit to Git.

Preview the data quality issues

Before filtering anything out, you need to understand what's wrong with the data. A diagnostic query will show you exactly how many rows fall into each problem category.

Create a new file called 01_data_cleaning.sql inside your scripts/ folder.
Add the following query to preview the data issues:

-- 01_data_cleaning.sql
-- Cleans raw data by removing cancellations, nulls, and invalid records

-- Preview the data issues
SELECT
    COUNT(*) AS total_rows,
    COUNT(*) FILTER (WHERE invoice_no LIKE 'C%') AS cancellations,
    COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_customers,
    COUNT(*) FILTER (WHERE quantity <= 0) AS zero_or_negative_qty,
    COUNT(*) FILTER (WHERE unit_price <= 0) AS zero_or_negative_price
FROM raw_retail;

What does this query do?

The FILTER (WHERE ...) clause is a PostgreSQL feature that counts only rows matching a condition. It's cleaner than writing multiple subqueries.

This single query tells you how many cancellations (invoices starting with 'C'), null customer IDs, and invalid quantities or prices exist in your raw data.

Save 01_data_cleaning.sql.
Run this query in pgAdmin (paste it into the Query Tool and click the play button) or in psql by running:

\i scripts/01_data_cleaning.sql

You should see a table showing the total row count alongside counts for each data issue category. The cancellations and null customer columns will have significant numbers.

Seeing a 'relation raw_retail does not exist' error?

Make sure you're connected to the retail_analytics database. In psql, run \c retail_analytics to switch to it. In pgAdmin, make sure you opened the Query Tool from the retail_analytics database, not the default postgres database.

Help me fix this database connection error

Create the clean_retail VIEW with a CTE

Now that you know what's wrong, you'll build a permanent filter. A VIEW acts like a saved query that you can use like a table. Every future script will query clean_retail instead of raw_retail.

Add the following VIEW definition below your preview query in 01_data_cleaning.sql:

-- Create a reusable clean view using a CTE pattern
CREATE OR REPLACE VIEW clean_retail AS
WITH cleaned AS (
    SELECT
        invoice_no,
        stock_code,
        description,
        quantity,
        invoice_date,
        unit_price,
        customer_id,
        country,
        (quantity * unit_price) AS total_amount
    FROM raw_retail
    WHERE invoice_no NOT LIKE 'C%'
      AND customer_id IS NOT NULL
      AND quantity > 0
      AND unit_price > 0
)
SELECT * FROM cleaned;

What does this code do?

The WITH cleaned AS (...) block is a CTE. It defines a temporary named result set that the outer query can reference. Think of it as a named subquery that makes your SQL easier to read.

The WHERE clause removes four categories of bad data: cancellations (invoice starting with 'C'), missing customers, zero/negative quantities, and zero/negative prices.

The computed column (quantity * unit_price) AS total_amount saves you from recalculating this in every future query.

Why a VIEW instead of a new table?

A VIEW doesn't duplicate your data. It stores the query definition, and PostgreSQL runs the filters every time you query it. This means your clean data stays in sync if you ever reload the raw table.

CREATE OR REPLACE means you can re-run this script safely without dropping and recreating the view.

Save 01_data_cleaning.sql.
Run the CREATE VIEW statement in pgAdmin or psql.

You should see a CREATE VIEW success message. If you see Query returned successfully in pgAdmin, that means the same thing.

Getting a syntax error near WITH?

Make sure the entire statement from CREATE OR REPLACE VIEW down to the final semicolon is selected and run together. Running just the CTE portion without the CREATE VIEW wrapper will fail.

Help me debug this CREATE VIEW syntax error

Verify the cleaned data and commit to Git

Your VIEW exists, but you need to confirm it actually works. A verification query will show you the health of your cleaned dataset at a glance.

Add the following verification query at the bottom of 01_data_cleaning.sql:

-- Verify the cleaned data
SELECT
    COUNT(*) AS clean_rows,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT invoice_no) AS unique_invoices,
    MIN(invoice_date) AS first_order,
    MAX(invoice_date) AS last_order,
    ROUND(SUM(total_amount)::NUMERIC, 2) AS total_revenue
FROM clean_retail;

What does this query do?

This queries your new clean_retail VIEW to confirm the cleaning worked. You'll see the row count (significantly lower than 1 million), the number of unique customers, the date range of orders, and total revenue.

The ROUND(SUM(total_amount)::NUMERIC, 2) casts the result to a numeric type and rounds to 2 decimal places for a clean currency display.

Save 01_data_cleaning.sql and run the verification query.

You should see a single row with columns for clean_rows, unique_customers, unique_invoices, first_order, last_order, and total_revenue. The clean_rows count should be noticeably lower than the original 1,067,371.

Seeing 'relation clean_retail does not exist'?

This means the CREATE VIEW statement from the previous substep didn't run successfully. Go back and run the full CREATE OR REPLACE VIEW statement first, then retry the verification query.

Help me figure out why my clean_retail view doesn't exist

Commit this file to Git by running these commands in your terminal:

git add scripts/01_data_cleaning.sql
git commit -m "Add data cleaning script with CTE and clean_retail view"
git push origin main

You should see output confirming the commit and push to your remote repository.

✔️ Awesome, I've got everything!

Great. Double check you've saved 01_data_cleaning.sql and pushed it to GitHub.

ⓧ I'd like to double check the full code

Here's the complete scripts/01_data_cleaning.sql file:

-- 01_data_cleaning.sql
-- Cleans raw data by removing cancellations, nulls, and invalid records

-- Preview the data issues
SELECT
    COUNT(*) AS total_rows,
    COUNT(*) FILTER (WHERE invoice_no LIKE 'C%') AS cancellations,
    COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_customers,
    COUNT(*) FILTER (WHERE quantity <= 0) AS zero_or_negative_qty,
    COUNT(*) FILTER (WHERE unit_price <= 0) AS zero_or_negative_price
FROM raw_retail;

-- Create a reusable clean view using a CTE pattern
CREATE OR REPLACE VIEW clean_retail AS
WITH cleaned AS (
    SELECT
        invoice_no,
        stock_code,
        description,
        quantity,
        invoice_date,
        unit_price,
        customer_id,
        country,
        (quantity * unit_price) AS total_amount
    FROM raw_retail
    WHERE invoice_no NOT LIKE 'C%'
      AND customer_id IS NOT NULL
      AND quantity > 0
      AND unit_price > 0
)
SELECT * FROM cleaned;

-- Verify the cleaned data
SELECT
    COUNT(*) AS clean_rows,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT invoice_no) AS unique_invoices,
    MIN(invoice_date) AS first_order,
    MAX(invoice_date) AS last_order,
    ROUND(SUM(total_amount)::NUMERIC, 2) AS total_revenue
FROM clean_retail;

Your data is now clean and queryable through a single VIEW. Next up, you'll use window functions to uncover revenue trends hiding in this data.

Analyze Revenue Trends with Window Functions

Your clean_retail view gives you a solid foundation of reliable transaction data. Now it's time to answer the first real business question: how is revenue trending over time?

Window functions let you perform calculations across rows that are related to the current row, without collapsing your results into a single value like GROUP BY does. In this step, you'll use them to compute running totals, moving averages, and month-over-month growth.

In this step, get ready to:

Calculate running totals and a 3-month moving average of monthly revenue.
Compute month-over-month revenue growth using the LAG window function.
Analyze each country's percentage contribution to total revenue.

Write the monthly revenue query with running totals

A running total shows cumulative revenue from the start of your data up to each month. A moving average smooths out spikes by averaging the current month with the two months before it. Together, they reveal both the long-term trajectory and short-term momentum of revenue.

Create a new file called 02_time_series_analysis.sql inside your scripts folder.
Add the following query that aggregates monthly revenue in a CTE, then applies window functions for the running total and moving average:

-- 02_time_series_analysis.sql
-- Analyzes revenue trends using window functions

-- Monthly revenue with running total and 3-month moving average
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', invoice_date) AS month,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS revenue,
        COUNT(DISTINCT invoice_no) AS num_orders,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM clean_retail
    GROUP BY DATE_TRUNC('month', invoice_date)
)
SELECT
    month,
    revenue,
    num_orders,
    unique_customers,
    SUM(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3m
FROM monthly_revenue
ORDER BY month;

What does this code do?

The CTE (monthly_revenue) groups all transactions by month using DATE_TRUNC('month', invoice_date), which truncates each timestamp to the first day of its month.

SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds up all revenue from the very first month through the current row. This is your running total.

AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) averages the current month's revenue with the two months before it. This 3-month moving average smooths out seasonal noise so you can spot real growth trends.

Save 02_time_series_analysis.sql.
Run the query in pgAdmin or psql. You should see one row per month with columns for revenue, num_orders, unique_customers, a steadily increasing running_total, and a moving_avg_3m that changes more gradually than the raw revenue.

Getting a column not found error?

If you see an error referencing total_amount, make sure the clean_retail view was created successfully in Step 3. Run SELECT * FROM clean_retail LIMIT 5; to confirm the view exists and has a total_amount column.

Also confirm you're connected to the retail_analytics database, not the default postgres database.

Help me debug my running total query

Add month-over-month growth with LAG

Running totals show the big picture, but stakeholders often want to know: did we grow or shrink compared to last month? The LAG window function lets you peek at the previous row's value without a self-join.

In 02_time_series_analysis.sql, add the following query below the first one:

-- Month-over-month revenue growth percentage
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', invoice_date) AS month,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS revenue
    FROM clean_retail
    GROUP BY DATE_TRUNC('month', invoice_date)
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / LAG(revenue, 1) OVER (ORDER BY month) * 100,
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

What does LAG do here?

LAG(revenue, 1) OVER (ORDER BY month) returns the revenue value from 1 row before the current row when rows are sorted by month. The first row gets NULL because there is no previous month to compare against.

The growth formula subtracts last month's revenue from this month's, divides by last month's revenue, and multiplies by 100 to produce a percentage. A positive value means growth. A negative value means a decline.

Save 02_time_series_analysis.sql.
Run this second query in pgAdmin or psql. You should see a prev_month_revenue column showing last month's revenue alongside the current month, and mom_growth_pct showing the percentage change. The first row will have NULL for both since there is no prior month.

Seeing division by zero errors?

If any month has zero revenue (unlikely with this dataset), the division will fail. The first row returns NULL naturally because LAG returns NULL for the first row. If you see unexpected NULLs beyond the first row, check that your clean_retail view filters are correct.

Help me fix my LAG window function query

Calculate country revenue contribution and commit

The final query in this script answers a different kind of question: which countries drive the most revenue? You'll use SUM() OVER () with an empty OVER clause to compute the grand total across all rows, then divide each country's revenue by that total.

In 02_time_series_analysis.sql, add this final query below the month-over-month growth query:

-- Country contribution to total revenue (part-to-whole analysis)
WITH country_revenue AS (
    SELECT
        country,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS revenue
    FROM clean_retail
    GROUP BY country
)
SELECT
    country,
    revenue,
    SUM(revenue) OVER () AS total_revenue,
    ROUND(
        revenue / SUM(revenue) OVER () * 100,
        2
    ) AS pct_of_total
FROM country_revenue
ORDER BY revenue DESC
LIMIT 15;

What does SUM() OVER () do without a partition?

When you write SUM(revenue) OVER () with empty parentheses after OVER, PostgreSQL computes the sum across ALL rows in the result set and repeats that total on every row. This lets you divide each country's revenue by the grand total to get a percentage, all in a single query.

Save 02_time_series_analysis.sql.
Run this third query. You should see the United Kingdom at the top with the highest revenue and percentage, followed by countries like Netherlands, EIRE (Ireland), Germany, and France. The pct_of_total column should add up close to 100% across all countries.

Results don't add up to 100%?

The query uses LIMIT 15, so you're only seeing the top 15 countries. The percentages won't sum to 100% unless you remove the LIMIT. This is expected behavior.

Help me understand my country contribution query results

Now commit this script to Git and push it to GitHub.

Stage and commit your new file by running these commands in your terminal:

git add scripts/02_time_series_analysis.sql
git commit -m "Add time series analysis with window functions"
git push origin main

Verify the push succeeded by checking your GitHub repository in your browser. You should see 02_time_series_analysis.sql in the scripts folder.

Push rejected or authentication error?

If Git asks for credentials, you may need to set up a personal access token on GitHub. Go to Settings > Developer settings > Personal access tokens and generate a classic token with repo scope. Use this token as your password when prompted.

Help me push my SQL project to GitHub

✔️ Awesome, I've got everything!

Great. Double check you've saved 02_time_series_analysis.sql and that all three queries run without errors.

ⓧ I'd like to double check the full code

Here's the complete scripts/02_time_series_analysis.sql file for comparison:

-- 02_time_series_analysis.sql
-- Analyzes revenue trends using window functions

-- Monthly revenue with running total and 3-month moving average
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', invoice_date) AS month,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS revenue,
        COUNT(DISTINCT invoice_no) AS num_orders,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM clean_retail
    GROUP BY DATE_TRUNC('month', invoice_date)
)
SELECT
    month,
    revenue,
    num_orders,
    unique_customers,
    SUM(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3m
FROM monthly_revenue
ORDER BY month;

-- Month-over-month revenue growth percentage
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', invoice_date) AS month,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS revenue
    FROM clean_retail
    GROUP BY DATE_TRUNC('month', invoice_date)
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / LAG(revenue, 1) OVER (ORDER BY month) * 100,
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

-- Country contribution to total revenue (part-to-whole analysis)
WITH country_revenue AS (
    SELECT
        country,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS revenue
    FROM clean_retail
    GROUP BY country
)
SELECT
    country,
    revenue,
    SUM(revenue) OVER () AS total_revenue,
    ROUND(
        revenue / SUM(revenue) OVER () * 100,
        2
    ) AS pct_of_total
FROM country_revenue
ORDER BY revenue DESC
LIMIT 15;

You now have three powerful queries that transform raw transaction data into executive-level insights. Next up, you'll use ranking functions to identify your best products and most valuable customers.

Rank Products and Customers

Your time series analysis already reveals revenue trends over time. Now you need to answer a different kind of question: who and what are driving those numbers.

Ranking queries help you identify top performers. In this step, you'll use three different ranking window functions to find the best-selling products globally, the top products per country, and the highest-spending customers grouped into spending tiers.

In this step, get ready to:

Rank products by revenue using DENSE_RANK and ROW_NUMBER.
Segment customers into spending quartiles using RANK and NTILE.
Commit and push your ranking analysis script to GitHub.

Find the top 10 products by revenue

A stakeholder asking "What are our best sellers?" expects a ranked list. DENSE_RANK is the right window function here because it handles ties gracefully. If two products have the same revenue, they share the same rank and the next rank is not skipped.

Create a new file called scripts/03_ranking_analysis.sql in your project folder.
Add the following query that aggregates revenue per product, then ranks them:

-- 03_ranking_analysis.sql
-- Ranks products and customers using window functions

-- Top 10 products by total revenue
WITH product_revenue AS (
    SELECT
        stock_code,
        MIN(description) AS product_name,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS total_revenue,
        SUM(quantity) AS total_qty_sold
    FROM clean_retail
    GROUP BY stock_code
)
SELECT
    stock_code,
    product_name,
    total_revenue,
    total_qty_sold,
    DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM product_revenue
ORDER BY revenue_rank
LIMIT 10;

What does this code do?

The CTE product_revenue groups all transactions by stock_code and calculates each product's total revenue and quantity sold.

MIN(description) picks one product name per stock code (some products have slight description variations across rows). DENSE_RANK() OVER (ORDER BY total_revenue DESC) assigns rank 1 to the highest revenue product. If two products tie, both get the same rank and the next product gets rank 3 (not 4).

Save scripts/03_ranking_analysis.sql.
Run the query in psql or pgAdmin. You should see 10 rows with columns stock_code, product_name, total_revenue, total_qty_sold, and revenue_rank numbered 1 through 10.

Seeing zero results or an error?

If you see relation "clean_retail" does not exist, make sure you're connected to the retail_analytics database. Run \c retail_analytics in psql to switch databases.
If you see a syntax error near OVER, make sure the closing parenthesis after GROUP BY stock_code in the CTE is present before the outer SELECT.

Help me debug my DENSE_RANK query for top products

Why DENSE_RANK instead of RANK or ROW_NUMBER?

PostgreSQL offers three ranking functions that each handle ties differently.

RANK() leaves gaps after ties (1, 1, 3).
DENSE_RANK() never skips ranks (1, 1, 2).
ROW_NUMBER() forces unique numbers even for ties (1, 2, 3). The tie-breaking is arbitrary.

For a "top 10" leaderboard, DENSE_RANK ensures you always see exactly the top 10 distinct revenue levels, even when products tie.

Find the top 3 products per country

Global rankings are helpful, but a country manager wants to know the best sellers in their own market. PARTITION BY lets you restart the ranking within each country.

Add the following query below your first query in scripts/03_ranking_analysis.sql:

-- Top 3 products per country
WITH product_country_revenue AS (
    SELECT
        country,
        stock_code,
        MIN(description) AS product_name,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS total_revenue
    FROM clean_retail
    GROUP BY country, stock_code
),
ranked AS (
    SELECT
        country,
        stock_code,
        product_name,
        total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY country
            ORDER BY total_revenue DESC
        ) AS rn
    FROM product_country_revenue
)
SELECT country, stock_code, product_name, total_revenue
FROM ranked
WHERE rn <= 3
ORDER BY country, rn;

What does this code do?

product_country_revenue calculates revenue per product per country, giving each product a separate total within each market.
ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_revenue DESC) restarts numbering from 1 inside each country. The highest-revenue product in France gets 1, and so does the highest in Germany.
The outer query filters to WHERE rn <= 3. You cannot filter on a window function in the same SELECT, so the CTE-then-filter pattern is necessary.

Save scripts/03_ranking_analysis.sql.
Run this second query. You should see grouped results with 3 products per country, ordered alphabetically by country name.

Seeing more than 3 products per country?

Check that your WHERE rn <= 3 clause is in the outer SELECT (after the ranked CTE), not inside the CTE itself. Window functions execute before WHERE, so filtering must happen in a separate query layer.

Help me fix my ROW_NUMBER PARTITION BY query

Rank customers by spend and assign quartiles

Knowing your top spenders is useful, but grouping all customers into spending tiers gives you a complete picture. NTILE(4) divides customers into four equal-sized groups from lowest to highest spend.

Add this final query to scripts/03_ranking_analysis.sql:

-- Top 20 customers by spend with quartile grouping
WITH customer_spend AS (
    SELECT
        customer_id,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS total_spend,
        COUNT(DISTINCT invoice_no) AS num_orders,
        MIN(invoice_date) AS first_order,
        MAX(invoice_date) AS last_order
    FROM clean_retail
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spend,
    num_orders,
    RANK() OVER (ORDER BY total_spend DESC) AS spend_rank,
    NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY spend_rank
LIMIT 20;

What does this code do?

customer_spend aggregates each customer's total spend, order count, and first/last purchase dates.
RANK() OVER (ORDER BY total_spend DESC) assigns rank 1 to the highest spender. Unlike DENSE_RANK, RANK skips numbers after ties (1, 1, 3).
NTILE(4) OVER (ORDER BY total_spend) splits all customers into 4 roughly equal groups. Quartile 1 contains the lowest spenders. Quartile 4 contains the highest.

Notice that RANK and NTILE operate on different orderings in the same SELECT. Each window function has its own independent OVER clause.

Save scripts/03_ranking_analysis.sql.
Run the query. You should see 20 rows with customer IDs, their total spend, order count, a rank from 1 to 20, and a spend_quartile of 4 for all top spenders.

All quartile values showing as 4?

This is expected. You're viewing the top 20 out of thousands of customers. Since NTILE divides the entire customer base into quartiles, the top spenders all land in quartile 4. Run the query without LIMIT 20 and you'll see quartiles 1 through 4 distributed across all customers.

Help me understand NTILE quartile distribution

Extra for Experts: When would NTILE(4) give unequal groups?

NTILE distributes rows as evenly as possible. If you have 4,339 customers, each quartile gets 1,084 or 1,085 customers. The remainder rows go to the earlier groups. This makes NTILE ideal for percentile analysis because each bucket is the same size regardless of the underlying value distribution.

Commit and push your ranking analysis to GitHub by running these commands:

git add scripts/03_ranking_analysis.sql
git commit -m "Add product and customer ranking analysis"
git push origin main

You should see a confirmation message ending with the branch name main and a file count showing 1 file changed.

Push rejected or authentication error?

If Git rejects the push, make sure you already pushed at least once in a previous step (which sets up the remote tracking branch). If you see an authentication prompt, your GitHub credentials may have expired. Re-authenticate with your browser when prompted.

Help me fix my git push to GitHub

✔️ Awesome, I've got everything!

Great. Double check you've saved your file and your Git push succeeded.

ⓧ I'd like to double check the full code

Here is the complete scripts/03_ranking_analysis.sql file for comparison:

-- 03_ranking_analysis.sql
-- Ranks products and customers using window functions

-- Top 10 products by total revenue
WITH product_revenue AS (
    SELECT
        stock_code,
        MIN(description) AS product_name,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS total_revenue,
        SUM(quantity) AS total_qty_sold
    FROM clean_retail
    GROUP BY stock_code
)
SELECT
    stock_code,
    product_name,
    total_revenue,
    total_qty_sold,
    DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM product_revenue
ORDER BY revenue_rank
LIMIT 10;

-- Top 3 products per country
WITH product_country_revenue AS (
    SELECT
        country,
        stock_code,
        MIN(description) AS product_name,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS total_revenue
    FROM clean_retail
    GROUP BY country, stock_code
),
ranked AS (
    SELECT
        country,
        stock_code,
        product_name,
        total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY country
            ORDER BY total_revenue DESC
        ) AS rn
    FROM product_country_revenue
)
SELECT country, stock_code, product_name, total_revenue
FROM ranked
WHERE rn <= 3
ORDER BY country, rn;

-- Top 20 customers by spend with quartile grouping
WITH customer_spend AS (
    SELECT
        customer_id,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS total_spend,
        COUNT(DISTINCT invoice_no) AS num_orders,
        MIN(invoice_date) AS first_order,
        MAX(invoice_date) AS last_order
    FROM clean_retail
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spend,
    num_orders,
    RANK() OVER (ORDER BY total_spend DESC) AS spend_rank,
    NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY spend_rank
LIMIT 20;

You can now rank any dimension of your dataset. Next, you'll tackle one of the most powerful analytics techniques in SQL: cohort retention analysis, where you track how groups of customers behave over time.

Build a Customer Cohort Retention Analysis

In the previous step, you ranked products and customers to identify top performers. Now you are going to answer a deeper business question: do customers actually come back after their first purchase?

A cohort analysis groups customers by the month they first purchased, then tracks what percentage of each group returns in subsequent months. This is one of the most commonly requested analyses in data and product teams because it directly measures customer loyalty over time.

In this step, get ready to:

Assign each customer to a cohort based on their first purchase month.
Calculate cohort age in months using DATE_PART arithmetic.
Build a retention matrix that shows customer retention percentages over time.

Assign each customer to their cohort

The first piece of a cohort analysis is determining when each customer made their very first purchase. That first-purchase month becomes their "cohort month."

Create a new file called 04_cohort_analysis.sql inside your scripts/ folder.
Add the file header and the first CTE by copying this code:

-- 04_cohort_analysis.sql
-- Builds a monthly customer cohort retention analysis

-- Step 1: Assign each customer to their cohort (first purchase month)
WITH customer_cohort AS (
    SELECT
        customer_id,
        invoice_date,
        DATE_TRUNC('month', invoice_date) AS order_month,
        MIN(DATE_TRUNC('month', invoice_date)) OVER (
            PARTITION BY customer_id
        ) AS cohort_month
    FROM clean_retail
),

What does this code do?

This CTE selects every row from clean_retail and adds two computed columns:

order_month truncates the invoice date to the first of its month, giving you a clean monthly grouping.
cohort_month uses MIN(DATE_TRUNC('month', invoice_date)) OVER (PARTITION BY customer_id) to find the earliest purchase month for each customer. This is the customer's cohort assignment.

The window function with PARTITION BY calculates the minimum across all of a customer's rows without collapsing them. Every row for a given customer gets the same cohort_month value.

Save 04_cohort_analysis.sql.
Confirm the file exists by running:

ls scripts/04_cohort_analysis.sql

You should see the file path printed back to you.

File not found?

Make sure you created the file inside the scripts/ folder, not your project root. Also check for typos in the filename.

help me find where my SQL file was saved

Calculate cohort age in months

Now that each row knows its customer's cohort month, you need to calculate how many months have passed between the cohort month and each transaction. This "cohort age" is what creates the time axis of your retention matrix.

In 04_cohort_analysis.sql, add the following CTE directly below the trailing comma of the customer_cohort CTE:

-- Step 2: Calculate cohort age (months since first purchase)
cohort_aged AS (
    SELECT
        customer_id,
        cohort_month,
        order_month,
        (DATE_PART('year', order_month) - DATE_PART('year', cohort_month)) * 12
        + (DATE_PART('month', order_month) - DATE_PART('month', cohort_month))
        AS cohort_age
    FROM customer_cohort
),

What does this code do?

PostgreSQL does not have a native DATEDIFF function for calculating the number of months between two dates. Instead, this CTE uses DATE_PART arithmetic:

It subtracts the cohort year from the order year and multiplies by 12 to get full years as months.
It adds the difference in month numbers to account for partial years.
The result is an integer representing months since first purchase. A customer buying in their cohort month gets cohort_age = 0.

Save 04_cohort_analysis.sql.

You will not see a change yet. The next chunk completes the query so you can run it.

Build the retention percentage query

The final piece counts how many distinct customers from each cohort are active at each cohort age, then divides by the cohort's starting size to produce a retention percentage.

In 04_cohort_analysis.sql, add the following code directly below the trailing comma of the cohort_aged CTE:

-- Step 3: Count distinct customers per cohort per age
cohort_counts AS (
    SELECT
        cohort_month,
        cohort_age,
        COUNT(DISTINCT customer_id) AS num_customers
    FROM cohort_aged
    GROUP BY cohort_month, cohort_age
),
-- Step 4: Get cohort starting size (age = 0)
cohort_sizes AS (
    SELECT
        cohort_month,
        num_customers AS cohort_size
    FROM cohort_counts
    WHERE cohort_age = 0
)
-- Final: Calculate retention percentage
SELECT
    c.cohort_month,
    s.cohort_size,
    c.cohort_age,
    c.num_customers,
    ROUND(
        c.num_customers::NUMERIC / s.cohort_size * 100,
        2
    ) AS retention_pct
FROM cohort_counts c
JOIN cohort_sizes s ON c.cohort_month = s.cohort_month
WHERE c.cohort_age <= 12
ORDER BY c.cohort_month, c.cohort_age;

What does this code do?

This final section has three parts working together:

cohort_counts groups by cohort_month and cohort_age, counting distinct customers active at each time point.
cohort_sizes extracts just the starting size (cohort_age = 0) for each cohort. This is your denominator for retention.
The final SELECT joins these two CTEs and calculates retention_pct as the number of returning customers divided by the cohort's original size, multiplied by 100.
The WHERE c.cohort_age <= 12 filter keeps the output to the first 12 months, which is the standard retention window for annual analysis.

Save 04_cohort_analysis.sql.
Run the complete script in psql by executing:

\i scripts/04_cohort_analysis.sql

You should see a result set with columns cohort_month, cohort_size, cohort_age, num_customers, and retention_pct. Each row shows what percentage of a cohort came back at a given month after their first purchase.

Seeing a syntax error?

The most common issue is a missing or extra comma between CTEs. Each CTE must end with a comma except the last one (cohort_sizes). Also confirm that the final SELECT does not have a comma before it.

If you see ERROR: relation "clean_retail" does not exist, make sure you are connected to the retail_analytics database. Run \c retail_analytics to reconnect.

help me debug my cohort analysis SQL

Commit this file to Git and push to GitHub by running:

git add scripts/04_cohort_analysis.sql
git commit -m "Add cohort retention analysis"
git push origin main

What is cohort retention used for?

Product and marketing teams use cohort retention to measure whether changes to the product improve customer loyalty. If cohorts acquired after a feature launch retain better than earlier cohorts, the feature is working.

A typical e-commerce retailer sees 20-30% retention at month 1. If your data shows significantly higher or lower, that is an insight worth noting for your portfolio.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file and pushed to GitHub.

ⓧ I'd like to double check the full code

Here is the complete scripts/04_cohort_analysis.sql file:

-- 04_cohort_analysis.sql
-- Builds a monthly customer cohort retention analysis

-- Step 1: Assign each customer to their cohort (first purchase month)
WITH customer_cohort AS (
    SELECT
        customer_id,
        invoice_date,
        DATE_TRUNC('month', invoice_date) AS order_month,
        MIN(DATE_TRUNC('month', invoice_date)) OVER (
            PARTITION BY customer_id
        ) AS cohort_month
    FROM clean_retail
),
-- Step 2: Calculate cohort age (months since first purchase)
cohort_aged AS (
    SELECT
        customer_id,
        cohort_month,
        order_month,
        (DATE_PART('year', order_month) - DATE_PART('year', cohort_month)) * 12
        + (DATE_PART('month', order_month) - DATE_PART('month', cohort_month))
        AS cohort_age
    FROM customer_cohort
),
-- Step 3: Count distinct customers per cohort per age
cohort_counts AS (
    SELECT
        cohort_month,
        cohort_age,
        COUNT(DISTINCT customer_id) AS num_customers
    FROM cohort_aged
    GROUP BY cohort_month, cohort_age
),
-- Step 4: Get cohort starting size (age = 0)
cohort_sizes AS (
    SELECT
        cohort_month,
        num_customers AS cohort_size
    FROM cohort_counts
    WHERE cohort_age = 0
)
-- Final: Calculate retention percentage
SELECT
    c.cohort_month,
    s.cohort_size,
    c.cohort_age,
    c.num_customers,
    ROUND(
        c.num_customers::NUMERIC / s.cohort_size * 100,
        2
    ) AS retention_pct
FROM cohort_counts c
JOIN cohort_sizes s ON c.cohort_month = s.cohort_month
WHERE c.cohort_age <= 12
ORDER BY c.cohort_month, c.cohort_age;

Your cohort retention analysis is complete. Next up, you will segment customers into value tiers using RFM scoring to identify your champions, loyal customers, and those at risk of churning.

Segment Customers with RFM Scoring

Your cohort analysis reveals how groups of customers behave over time. But within each cohort, some customers are worth far more than others. The question now is: which individual customers deserve the most attention?

In this step, you'll build an RFM segmentation model that scores every customer on three dimensions: how recently they purchased, how often they purchase, and how much they spend. Using NTILE and a CASE expression, you'll classify each customer into actionable segments like "Champions" and "At Risk."

In this step, get ready to:

Calculate Recency, Frequency, and Monetary metrics for every customer.
Score each metric into quartiles using NTILE window functions.
Classify customers into named segments with a CASE expression and commit to Git.

Calculate RFM metrics per customer

RFM stands for Recency, Frequency, and Monetary. These three metrics together predict customer value better than any single measure alone.

Create a new file called 05_rfm_segmentation.sql inside your scripts folder.
Add the file header and the first CTE that calculates recency, frequency, and monetary value for each customer:

-- 05_rfm_segmentation.sql
-- Segments customers using Recency, Frequency, Monetary scoring

-- Step 1: Calculate RFM metrics per customer
WITH rfm_metrics AS (
    SELECT
        customer_id,
        (SELECT MAX(invoice_date) FROM clean_retail) - MAX(invoice_date) AS recency_interval,
        EXTRACT(DAY FROM
            (SELECT MAX(invoice_date) FROM clean_retail) - MAX(invoice_date)
        ) AS recency_days,
        COUNT(DISTINCT invoice_no) AS frequency,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS monetary
    FROM clean_retail
    GROUP BY customer_id
)

What does this code do?

This CTE calculates three metrics for every customer in your cleaned dataset:

recency_interval computes the time interval between the dataset's latest order date and each customer's last purchase.
recency_days extracts that interval as an integer number of days. Lower values mean the customer purchased more recently.
frequency counts the number of distinct invoices (orders) per customer.
monetary sums the total amount spent across all orders.

Save 05_rfm_segmentation.sql.

Why use a subquery for MAX(invoice_date)?

The subquery (SELECT MAX(invoice_date) FROM clean_retail) finds the absolute latest date in the dataset. This acts as a fixed reference point for all customers, so recency measures days from the same baseline.

Score each metric into quartiles

Raw metrics are hard to compare. A customer with 50 orders and $10,000 in spend needs a common scoring scale to rank alongside a customer with 2 orders and $30 in spend.

Add the second CTE directly after the closing parenthesis of rfm_metrics. This CTE uses NTILE(4) to assign quartile scores:

-- Step 2: Score each metric into quartiles (1-4, where 4 is best)
rfm_scores AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        NTILE(4) OVER (ORDER BY recency_days DESC) AS r_score,
        NTILE(4) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(4) OVER (ORDER BY monetary ASC) AS m_score
    FROM rfm_metrics
    WHERE recency_days IS NOT NULL
),

Why is recency scored DESC but frequency and monetary scored ASC?

For recency, a LOWER number of days is BETTER (more recent purchase). NTILE(4) OVER (ORDER BY recency_days DESC) places customers with the fewest days (most recent) in group 4, which is the highest score.

For frequency and monetary, HIGHER values are BETTER. ORDER BY ASC places the highest-value customers in group 4. This way, a score of 4 always means "best" across all three metrics.

Save 05_rfm_segmentation.sql.

Getting a syntax error near rfm_scores?

Make sure the rfm_metrics CTE ends with a closing parenthesis followed by a comma, then rfm_scores AS ( starts on the next line. Each CTE in a chain is separated by a comma.

Help me fix a CTE syntax error in my RFM query

Classify customers into segments and run the final queries

Scores alone are useful, but named segments make the analysis actionable for business teams. A marketing team can run a campaign for "At Risk" customers without needing to interpret score combinations themselves.

Add the third CTE and the final SELECT queries below rfm_scores. This uses a CASE expression to map score combinations to named segments:

-- Step 3: Assign customer segments based on combined scores
rfm_segments AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        r_score,
        f_score,
        m_score,
        (r_score + f_score + m_score) AS rfm_total,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
            WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Loyal Customers'
            WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
            WHEN r_score >= 3 AND f_score >= 2 AND m_score >= 2 THEN 'Potential Loyalists'
            WHEN r_score <= 2 AND f_score >= 3 AND m_score >= 3 THEN 'At Risk'
            WHEN r_score <= 2 AND f_score >= 4 THEN 'Cannot Lose Them'
            WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
            ELSE 'Others'
        END AS segment
    FROM rfm_scores
)
-- Final: Summary by segment
SELECT
    segment,
    COUNT(*) AS num_customers,
    ROUND(AVG(recency_days)::NUMERIC, 0) AS avg_recency_days,
    ROUND(AVG(frequency)::NUMERIC, 1) AS avg_frequency,
    ROUND(AVG(monetary)::NUMERIC, 2) AS avg_monetary,
    ROUND(SUM(monetary)::NUMERIC, 2) AS total_revenue
FROM rfm_segments
GROUP BY segment
ORDER BY total_revenue DESC;

-- Detail view: List all customers with their segment
SELECT
    customer_id,
    r_score,
    f_score,
    m_score,
    segment,
    monetary AS lifetime_value
FROM rfm_segments
ORDER BY monetary DESC
LIMIT 50;

What does the CASE expression do?

The CASE expression evaluates score combinations from top to bottom and assigns the first matching segment. Order matters here because conditions can overlap.

Champions score 4 across all three metrics. They bought recently, buy often, and spend heavily.
At Risk customers used to buy frequently and spend well (high F and M), but their recency score is low. They haven't purchased in a while.
Lost customers score low on both recency and frequency. They are long gone.

Save 05_rfm_segmentation.sql.
Run the full script in psql or pgAdmin against your retail_analytics database.

You should see a summary table showing each segment with its customer count, average recency, frequency, monetary value, and total revenue. The detail query below it lists the top 50 individual customers with their scores and segment labels.

Getting zero rows or NULL values?

If you see no results, check that the clean_retail view still exists. Run SELECT COUNT(*) FROM clean_retail; to confirm it returns rows.

If you see NULL values in the recency column, the WHERE recency_days IS NOT NULL filter in rfm_scores should handle that. Make sure it's included in your query.

Help me debug my RFM segmentation query

Now commit this script to your Git repository.

Stage and commit the file by running these commands in your terminal:

git add scripts/05_rfm_segmentation.sql
git commit -m "Add RFM customer segmentation analysis"
git push origin main

What did you just push?

Your GitHub repository now contains six SQL scripts covering data loading, cleaning, time series analysis, ranking, cohort retention, and customer segmentation. This progression shows a hiring manager that you can build a complete analytics pipeline from raw data to business insights.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file and that your Git push completed successfully.

ⓧ I'd like to double check the full code

Here is the complete 05_rfm_segmentation.sql file for comparison:

-- 05_rfm_segmentation.sql
-- Segments customers using Recency, Frequency, Monetary scoring

-- Step 1: Calculate RFM metrics per customer
WITH rfm_metrics AS (
    SELECT
        customer_id,
        (SELECT MAX(invoice_date) FROM clean_retail) - MAX(invoice_date) AS recency_interval,
        EXTRACT(DAY FROM
            (SELECT MAX(invoice_date) FROM clean_retail) - MAX(invoice_date)
        ) AS recency_days,
        COUNT(DISTINCT invoice_no) AS frequency,
        ROUND(SUM(total_amount)::NUMERIC, 2) AS monetary
    FROM clean_retail
    GROUP BY customer_id
),
-- Step 2: Score each metric into quartiles (1-4, where 4 is best)
rfm_scores AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        NTILE(4) OVER (ORDER BY recency_days DESC) AS r_score,
        NTILE(4) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(4) OVER (ORDER BY monetary ASC) AS m_score
    FROM rfm_metrics
    WHERE recency_days IS NOT NULL
),
-- Step 3: Assign customer segments based on combined scores
rfm_segments AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        r_score,
        f_score,
        m_score,
        (r_score + f_score + m_score) AS rfm_total,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
            WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Loyal Customers'
            WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
            WHEN r_score >= 3 AND f_score >= 2 AND m_score >= 2 THEN 'Potential Loyalists'
            WHEN r_score <= 2 AND f_score >= 3 AND m_score >= 3 THEN 'At Risk'
            WHEN r_score <= 2 AND f_score >= 4 THEN 'Cannot Lose Them'
            WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
            ELSE 'Others'
        END AS segment
    FROM rfm_scores
)
-- Final: Summary by segment
SELECT
    segment,
    COUNT(*) AS num_customers,
    ROUND(AVG(recency_days)::NUMERIC, 0) AS avg_recency_days,
    ROUND(AVG(frequency)::NUMERIC, 1) AS avg_frequency,
    ROUND(AVG(monetary)::NUMERIC, 2) AS avg_monetary,
    ROUND(SUM(monetary)::NUMERIC, 2) AS total_revenue
FROM rfm_segments
GROUP BY segment
ORDER BY total_revenue DESC;

-- Detail view: List all customers with their segment
SELECT
    customer_id,
    r_score,
    f_score,
    m_score,
    segment,
    monetary AS lifetime_value
FROM rfm_segments
ORDER BY monetary DESC
LIMIT 50;

You now have a complete customer segmentation model built entirely in SQL. In the Secret Mission, you'll combine all of these analytics into a single reusable VIEW and polish your repository with a professional README.

Secret mission

Build a Comprehensive Analytics View and Polish Your Repository

You've built individual analytics scripts that each answer specific business questions. But what if a stakeholder wants a single query that shows everything about a customer? In this mission, you'll combine your best work into a reusable VIEW and package your repository for the real world.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with a free GitHub account, so there are no ongoing costs.

Resources you used:

PostgreSQL database (retail_analytics).
SQL scripts folder (scripts/).
CSV data file (online_retail_II.csv).
GitHub repository.
Git configuration (global user name and email).

Keep everything running

No action needed. Choose this if you're still actively building or want to continue querying the dataset.

Your retail_analytics database stays available in PostgreSQL for further exploration.
Your SQL scripts remain in your local project folder, ready for editing.
Your GitHub repository stays published and visible to potential employers.

Pause - I'll come back to this later

Free up disk space by removing the large CSV file while keeping your scripts and database intact.

Delete the online_retail_II.csv file from your project folder. You can re-download it from Kaggle if you need it again.
Your PostgreSQL database and SQL scripts will remain untouched.
Your GitHub repository stays published online.

Delete - I don't want to use this again

Remove all project resources and start fresh.

Drop the database by opening psql and running DROP DATABASE retail_analytics;.
Delete your local project folder containing the SQL scripts and CSV file.
Delete the GitHub repository by going to your repository page, clicking Settings, scrolling to the bottom, and selecting Delete this repository.
Your Git global configuration (user name and email) can stay. It does not take up meaningful space.

That's a Wrap!

Nice work! You just built a professional SQL analytics project from scratch, analyzing over 1 million real e-commerce transactions using advanced techniques that hiring managers actively look for.

You've learned how to:

Cleaned 1M+ real transactions using CTEs and computed running totals, moving averages, and month-over-month growth with window functions.
Built a cohort retention analysis that tracks how customer groups behave over time using PARTITION BY and date arithmetic.
Segmented customers into value tiers using RFM scoring with NTILE, CASE expressions, and multiple CTEs.
Secret Mission: Created a reusable customer_360 view combining all your KPIs and wrote a professional README for your GitHub repository.

Ready to quiz yourself?