SQL Data Analysis for Portfolio

Build a portfolio-ready SQL analytics project using PostgreSQL.

Introduction

30 Second Summary

Every company has questions hiding in its data: is revenue growing, who are the best customers, and who is about to stop buying? Answering these questions with SQL is one of the most in-demand skills in tech.

In this project, you will build a complete SQL analytics project for ShopStream, a fictional e-commerce company. You will design a database, write advanced queries using window functions and CTEs, and produce business insights that prove to hiring managers you can turn raw data into decisions.

What You'll Build

You will open your GitHub repository and walk a hiring manager through queries that calculate month-over-month growth rates, rank customers by lifetime value, and identify who is about to churn.

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

  • A working PostgreSQL database you designed from scratch, where you can query customer behavior, product sales, and order patterns across a full year of e-commerce data.
  • Month-over-month growth analysis using window functions that reveals whether the business is accelerating or stalling, calculated in a single elegant query.
  • Customer Lifetime Value rankings and cohort retention rates that pinpoint exactly which customers have churned and how much revenue they represent.
  • Secret Mission: Build an RFM customer segmentation model that classifies every customer into actionable segments like Champion, Loyal, At Risk, and Lost.

Are there any prerequisites?

You should be comfortable with intermediate SQL concepts like SELECT, WHERE, JOINs, and GROUP BY. No tools need to be installed yet. You will set up everything from scratch in Step 1.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your career as a data analyst.

Set Up PostgreSQL and DBeaver

To analyze data with SQL, you need a database running on your machine. Without one, there is nothing to query. You also need a way to write queries and see results clearly.

In this step, you will install PostgreSQL as your local database server and DBeaver as your graphical SQL editor. Together, they give you a professional analytics environment right on your Windows PC.

In this step, get ready to:
  • Install PostgreSQL 17 and verify the command-line client works.
  • Create the project database using psql.
  • Install DBeaver Community Edition and connect it to your database.
Download and install PostgreSQL 17

PostgreSQL is an open-source relational database. The EDB interactive installer bundles the database server, pgAdmin, and the psql command-line tool together in one download.

  • Press the Windows key to open your search bar.
  • Type Command Prompt and press Enter to open it.
  • Check whether PostgreSQL is already on your machine by running this command:
psql --version

Look at the output and pick the tab that matches what you see.

✔️ I see version 17 or higher

PostgreSQL 17 is already installed on your machine. You are ready to move on to the next substep.

ⓧ I see a version lower than 17

You have an older PostgreSQL version installed. You need version 17 for this project to ensure all window function features work correctly.

  • Open your browser and go to the PostgreSQL Windows download page:
  • https://www.postgresql.org/download/windows/
  • Click the link to the EDB interactive installer.
  • On the EDB downloads page, select the installer for PostgreSQL 17 (Windows x86-64).
  • Run the downloaded installer. Accept the defaults for installation directory and components. Make sure all components are checked (PostgreSQL Server, pgAdmin 4, Stack Builder, Command Line Tools).
  • When the installer asks for a password for the postgres superuser, choose a password you will remember. You will need it shortly.
  • Keep the default port 5432 and default locale.
  • Finish the installation. Uncheck Launch Stack Builder at the end. You do not need it.
  • Close and reopen Command Prompt so the updated PATH takes effect.
  • Run psql --version again. You should now see a version 17 number.

Side-by-side versions

The new installer places PostgreSQL 17 in its own directory. Your old version's data remains untouched, but the new psql will be the one on your PATH after reopening Command Prompt.

ⓧ Command not found

PostgreSQL is not installed yet. Follow these steps to install it.

  • Open your browser and go to the PostgreSQL Windows download page:
  • https://www.postgresql.org/download/windows/
  • Click the link to the EDB interactive installer.
  • On the EDB downloads page, select the installer for PostgreSQL 17 (Windows x86-64).
  • Run the downloaded installer. Accept the defaults for installation directory and components. Make sure all components are checked (PostgreSQL Server, pgAdmin 4, Stack Builder, Command Line Tools).
  • When the installer asks for a password for the postgres superuser, choose a password you will remember. You will need it shortly.
  • Keep the default port 5432 and default locale.
  • Finish the installation. Uncheck Launch Stack Builder at the end. You do not need it.
  • Close and reopen Command Prompt so the new PATH takes effect.

Verify the install by running this command in the reopened Command Prompt:

psql --version

You should see output containing psql (PostgreSQL) 17 followed by the patch number.

Still seeing command not found?

The installer may not have added PostgreSQL to your PATH. Try running the full path directly:

"C:\Program Files\PostgreSQL\17\bin\psql" --version

If that works, add C:\Program Files\PostgreSQL\17\bin to your system PATH. Search Edit the system environment variables in the Windows search bar, click Environment Variables, select Path under System variables, click Edit, and add the path above. Then reopen Command Prompt.

help me fix psql not being recognized in my Command Prompt after installing PostgreSQL 17

What did the installer set up?

The EDB installer installed the PostgreSQL 17 database server (running as a Windows service), the psql command-line client, and pgAdmin (a web-based admin tool). The server starts automatically on boot.

You will use psql for quick database creation and DBeaver for writing longer analysis queries.

Create the project database

PostgreSQL can host many databases on a single server. You will create a dedicated database for this analytics project so your work stays organized.

  • Connect to PostgreSQL as the superuser by running this command:
psql -U postgres

What does this command do?

The -U postgres flag tells psql to connect as the postgres superuser. It will prompt you for the password you set during installation.

  • Enter the password you chose during installation when prompted.

You should now see a postgres=# prompt. This means you are inside the PostgreSQL interactive terminal.

  • Create your project database by running this SQL command:
CREATE DATABASE ecommerce_analytics;
  • Confirm the database was created by listing all databases:
\l

You should see ecommerce_analytics in the list alongside the default databases (postgres, template0, template1).

  • Exit psql by running:
\q

Getting a password authentication failed error?

Double-check that you are typing the exact password you set during the PostgreSQL installation wizard. The password is case-sensitive.

If you have forgotten it, you can reset it by editing the pg_hba.conf file to temporarily allow trust authentication, then running ALTER USER postgres PASSWORD 'newpassword'; inside psql.

help me reset my postgres password on Windows after forgetting it during installation

Install and connect DBeaver

DBeaver Community Edition is a free, open-source database management tool with a full SQL editor, syntax highlighting, and result tabs. It makes writing multi-line analysis queries much easier than the basic psql terminal.

  • Open your browser and go to the DBeaver download page:
  • https://dbeaver.io/download/
  • Download the Windows installer (the .exe file under the Windows section).
  • Run the installer and accept all defaults. DBeaver bundles its own Java runtime, so no extra installs are needed.
  • Once installed, press the Windows key, type DBeaver, and press Enter to open it.

Now connect DBeaver to your new database.

  • In the DBeaver menu bar, click Database, then click New Database Connection.
  • In the connection type list, select PostgreSQL and click Next.
  • Fill in the connection settings with these values:
  • Host: localhost
  • Port: 5432
  • Database: ecommerce_analytics
  • Username: postgres
  • Password: the password you set during PostgreSQL installation.

Why localhost and 5432?

PostgreSQL is running on your own machine, which is always reachable at localhost. Port 5432 is the default port PostgreSQL listens on. These values match the defaults from your installation.

  • Click Test Connection at the bottom of the dialog.

Before you click, what do you expect to see? If your database is running and your credentials are correct, the test should succeed.

DBeaver may ask to download a driver

If this is your first time connecting to PostgreSQL in DBeaver, it will prompt you to download the PostgreSQL JDBC driver. Click Download to allow it. This is a one-time setup.

You should see a Connected confirmation dialog. This proves DBeaver can talk to your PostgreSQL server and the ecommerce_analytics database exists.

  • Click Finish to save the connection.

Your new connection appears in the Database Navigator panel on the left side of DBeaver.

Connection test failed?

Check these common causes:

  • Make sure you typed ecommerce_analytics (with an underscore) in the Database field. A typo here means DBeaver tries to connect to a database that does not exist.
  • Confirm the PostgreSQL service is running. Open Windows Services (search for services.msc in the Start menu) and check that postgresql-x64-17 shows a status of Running.
  • Verify the password matches what you entered during PostgreSQL installation.

help me troubleshoot why DBeaver cannot connect to my local PostgreSQL database

Your local analytics environment is ready. You have PostgreSQL 17 running, a dedicated ecommerce_analytics database created, and DBeaver connected to it. Next up, you will design and populate your e-commerce database schema.

Build Your E-Commerce Database

Your database environment is ready. Now you need data to analyze.

Before writing a single query, you need a well-structured dataset to work with. In this step, you'll design a 4-table relational schema and populate it with realistic e-commerce data. Designing the schema yourself demonstrates relational modeling skills that hiring managers value.

In this step, get ready to:
  • Create a 4-table schema with primary keys and foreign key relationships.
  • Populate the database with 15 customers, 10 products, and 35 orders.
  • Verify your data loaded correctly with exploratory queries.
Create the database schema

A relational schema defines your tables, columns, and the relationships between them. Your ShopStream database needs four tables: customers, products, orders, and order items.

  • In DBeaver, make sure you are connected to the ecommerce_analytics database.
  • Create a new SQL script by pressing Ctrl+] or clicking SQL Editor in the top menu and selecting New SQL Script.
  • Paste the following schema code into the editor:
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    country VARCHAR(50) NOT NULL,
    signup_date DATE NOT NULL
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'completed'
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL
);

What does this schema do?

  • SERIAL PRIMARY KEY creates an auto-incrementing ID for each row.
  • REFERENCES creates a foreign key relationship. orders links to customers, and order_items links to both orders and products.
  • NOT NULL prevents missing data in critical columns.
  • The table order matters. customers and products must exist before orders and order_items can reference them.
  • Execute the entire script by pressing Alt+X (this runs all statements in the editor sequentially).
  • In the Database Navigator panel on the left, right-click your ecommerce_analytics connection and click Refresh.
  • Expand Schemas then public then Tables. You should see all four tables: customers, order_items, orders, and products.

Tables not appearing in the navigator?

  • Make sure you used Alt+X (Execute Script), not Ctrl+Enter (Execute Statement). Ctrl+Enter only runs the single statement under your cursor, so only one table would be created.
  • Check the results panel at the bottom for any error messages. A red error usually means a typo in a column type or a missing comma.
  • If you see a permission error, confirm your connection uses the postgres user and is pointed at the ecommerce_analytics database.

Why aren't my tables showing after I executed the schema?

Populate the database with seed data

Empty tables do not give you much to analyze. You need realistic data with intentional patterns: loyal repeat buyers, one-time purchasers, and fluctuating monthly revenue.

  • Open a new SQL script tab by pressing Ctrl+] again.
  • Paste the following customers and products data:
-- Customers (15 customers, signed up between Jan 2024 and May 2025)
INSERT INTO customers (first_name, last_name, email, country, signup_date) VALUES
('Emma', 'Johnson', 'emma.johnson@email.com', 'United States', '2024-01-15'),
('Liam', 'Smith', 'liam.smith@email.com', 'United Kingdom', '2024-02-20'),
('Sophia', 'Garcia', 'sophia.garcia@email.com', 'Spain', '2024-03-10'),
('Noah', 'Williams', 'noah.williams@email.com', 'United States', '2024-03-22'),
('Olivia', 'Brown', 'olivia.brown@email.com', 'Canada', '2024-04-05'),
('James', 'Davis', 'james.davis@email.com', 'United States', '2024-05-18'),
('Ava', 'Martinez', 'ava.martinez@email.com', 'Mexico', '2024-06-01'),
('William', 'Anderson', 'william.anderson@email.com', 'United Kingdom', '2024-07-14'),
('Isabella', 'Thomas', 'isabella.thomas@email.com', 'United States', '2024-08-30'),
('Benjamin', 'Taylor', 'benjamin.taylor@email.com', 'Australia', '2024-09-12'),
('Mia', 'Jackson', 'mia.jackson@email.com', 'Canada', '2024-10-25'),
('Lucas', 'White', 'lucas.white@email.com', 'United States', '2024-11-08'),
('Charlotte', 'Harris', 'charlotte.harris@email.com', 'United Kingdom', '2025-01-14'),
('Henry', 'Clark', 'henry.clark@email.com', 'United States', '2025-03-20'),
('Amelia', 'Lewis', 'amelia.lewis@email.com', 'Australia', '2025-05-02');

-- Products (10 products across 3 categories)
INSERT INTO products (product_name, category, price) VALUES
('Wireless Headphones', 'Electronics', 79.99),
('Laptop Stand', 'Electronics', 45.99),
('USB-C Hub', 'Electronics', 34.99),
('Mechanical Keyboard', 'Electronics', 129.99),
('Running Shoes', 'Clothing', 89.99),
('Cotton T-Shirt', 'Clothing', 24.99),
('Winter Jacket', 'Clothing', 149.99),
('Yoga Mat', 'Home & Kitchen', 29.99),
('Coffee Maker', 'Home & Kitchen', 69.99),
('Desk Lamp', 'Home & Kitchen', 39.99);
  • Execute this script by pressing Alt+X.
  • Verify the inserts worked by running this query (place your cursor on it and press Ctrl+Enter):
SELECT COUNT(*) FROM customers;

You should see 15 in the results panel. This confirms your customers and products loaded.

  • Now paste the orders data below your existing code in the same script tab:
-- Orders (35 orders spanning Jan-Dec 2025)
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2025-01-08', 'completed'),(3, '2025-01-15', 'completed'),(6, '2025-01-22', 'completed'),
(2, '2025-02-05', 'completed'),(4, '2025-02-18', 'completed'),(6, '2025-02-25', 'completed'),
(1, '2025-03-03', 'completed'),(5, '2025-03-12', 'completed'),(7, '2025-03-20', 'completed'),
(4, '2025-04-02', 'completed'),(6, '2025-04-15', 'completed'),(10, '2025-04-28', 'completed'),
(1, '2025-05-05', 'completed'),(9, '2025-05-14', 'completed'),(11, '2025-05-22', 'completed'),
(6, '2025-06-08', 'completed'),(8, '2025-06-18', 'completed'),(12, '2025-06-25', 'completed'),
(1, '2025-07-04', 'completed'),(4, '2025-07-15', 'completed'),(13, '2025-07-22', 'completed'),
(6, '2025-08-03', 'completed'),(9, '2025-08-12', 'completed'),(12, '2025-08-20', 'completed'),
(1, '2025-09-02', 'completed'),(2, '2025-09-15', 'completed'),(13, '2025-09-22', 'completed'),
(14, '2025-09-28', 'completed'),(6, '2025-10-05', 'completed'),(9, '2025-10-18', 'completed'),
(15, '2025-10-25', 'completed'),(7, '2025-11-02', 'completed'),(13, '2025-11-10', 'completed'),
(6, '2025-11-18', 'completed'),(14, '2025-12-05', 'completed');
  • Paste the order items data below the orders INSERT:
-- Order Items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 79.99),(1, 3, 1, 34.99),(2, 6, 2, 24.99),(3, 4, 1, 129.99),
(4, 2, 1, 45.99),(4, 8, 1, 29.99),(5, 5, 1, 89.99),(6, 1, 1, 79.99),(6, 10, 1, 39.99),
(7, 9, 1, 69.99),(7, 6, 1, 24.99),(8, 7, 1, 149.99),(9, 5, 1, 89.99),(9, 6, 1, 24.99),
(10, 4, 1, 129.99),(10, 3, 1, 34.99),(11, 1, 2, 79.99),(12, 8, 1, 29.99),(12, 10, 1, 39.99),
(13, 2, 1, 45.99),(13, 3, 1, 34.99),(14, 9, 1, 69.99),(15, 6, 3, 24.99),
(16, 4, 1, 129.99),(16, 8, 1, 29.99),(17, 1, 1, 79.99),(18, 5, 1, 89.99),(18, 10, 1, 39.99),
(19, 7, 1, 149.99),(19, 6, 2, 24.99),(20, 9, 1, 69.99),(21, 2, 1, 45.99),(21, 3, 1, 34.99),
(22, 1, 1, 79.99),(22, 2, 1, 45.99),(23, 4, 1, 129.99),(24, 10, 2, 39.99),
(25, 9, 1, 69.99),(25, 8, 1, 29.99),(26, 7, 1, 149.99),(27, 1, 1, 79.99),(27, 6, 1, 24.99),(28, 5, 1, 89.99),
(29, 4, 1, 129.99),(29, 10, 1, 39.99),(30, 2, 1, 45.99),(30, 8, 1, 29.99),(31, 3, 1, 34.99),(31, 6, 1, 24.99),
(32, 7, 1, 149.99),(32, 5, 1, 89.99),(33, 9, 1, 69.99),(33, 3, 1, 34.99),(34, 1, 1, 79.99),(34, 4, 1, 129.99),
(35, 2, 1, 45.99),(35, 9, 1, 69.99);

Why design data with intentional patterns?

This seed data is not random. Some customers (like Emma and James) are repeat high-value buyers. Others (like Sophia and Olivia) purchased once and disappeared. Revenue fluctuates month to month.

These patterns create meaningful results later when you run cohort analysis and churn detection. Real e-commerce data has similar patterns, so your portfolio queries produce realistic insights.

  • Select only the new orders and order_items INSERT statements you just pasted (highlight them), then press Ctrl+Enter to execute just the selected text.
  • Verify orders loaded by placing your cursor on this query and pressing Ctrl+Enter:
SELECT COUNT(*) FROM orders;

You should see 35 in the results panel.

Seeing a foreign key violation error?

If you get an error mentioning a foreign key constraint, you likely executed the orders or order_items INSERT before the customers and products data was committed. Run the full script from the top using Alt+X so all statements execute in order.

If you already ran the customers/products successfully and are re-running, you may get a duplicate key error. Drop all tables first and start over with the schema script, then re-run seed data.

Help me fix a foreign key violation in my seed data script.

Run exploratory queries to verify your data

Your tables are populated. Before moving on, confirm everything loaded correctly and get a first look at the data you will analyze.

  • Open a new SQL script tab by pressing Ctrl+]. This will be your analysis_queries.sql file.
  • Run this query to see a sample of your customer data:
SELECT * FROM customers LIMIT 5;

You should see five rows with names, emails, countries, and signup dates displayed in the results panel.

  • Before you run the next query, predict: how many orders do you expect to see in the count?
  • Run this final verification query:
SELECT COUNT(*) FROM orders;

The result is 35. This confirms all your data loaded correctly across all four tables.

  • Save your schema script as schema.sql by pressing Ctrl+S in the schema tab and choosing a location on your Desktop.
  • Save your seed data script as seed_data.sql in the same location.
  • Save the new exploratory script as analysis_queries.sql. You will add all your analysis queries to this file in the next steps.

Why save SQL files separately?

These three files form the core of your portfolio project. Anyone who clones your repository can run schema.sql then seed_data.sql to recreate your exact database, then explore your analysis queries. That reproducibility is what makes this portfolio-ready.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files: schema.sql, seed_data.sql, and analysis_queries.sql.

ⓧ I'd like to double check the full code

Scroll up and confirm each of these sections is present in your scripts, in order:

  • schema.sql: Four CREATE TABLE statements (customers, products, orders, order_items) with all columns, constraints, and foreign keys.
  • seed_data.sql: Four INSERT INTO statements (customers with 15 rows, products with 10 rows, orders with 35 rows, order_items with all item-level data).
  • analysis_queries.sql: The two exploratory queries (SELECT * FROM customers LIMIT 5 and SELECT COUNT(*) FROM orders).

You now have a fully populated e-commerce database. Next up, you will write your first business queries to calculate revenue, find top customers, and spot monthly trends.

Analyze Revenue and Customer Behavior

Your database is loaded with a full year of e-commerce data. Now it is time to start asking questions of it.

Leadership at ShopStream wants answers to the basics first: How much revenue did we make? Who are our best customers? What products drive the most sales? These are the first queries any data analyst writes when joining a new company, and they form the foundation of your portfolio project.

In this step, get ready to:
  • Calculate total revenue, order count, and average order value using JOINs and aggregate functions.
  • Identify your top customers and products with GROUP BY, ORDER BY, and LIMIT.
  • Build a monthly revenue trend using DATE_TRUNC to spot patterns over time.
Calculate total revenue and average order value

The first question any executive asks is: how much money did we make? To answer it, you need to combine the orders table with order_items, because the item-level prices and quantities live in order_items.

  • In DBeaver, open the SQL script tab where you have analysis_queries.sql (connected to ecommerce_analytics).
  • Add the file header and your first query by typing the following:
-- ============================================================
-- ShopStream E-Commerce Analytics
-- Step 3: Revenue and Customer Behavior Analysis
-- ============================================================

-- Total Revenue, Total Orders, Average Order Value
SELECT
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

What does this query do?

The JOIN connects each order to its line items so you can access the price and quantity columns.

COUNT(DISTINCT o.order_id) counts unique orders (not line items). SUM(oi.quantity * oi.unit_price) multiplies each item's quantity by its price and totals everything. ROUND(..., 2) keeps the average to two decimal places.

  • Highlight the SELECT query (from SELECT down through the semicolon) and press Ctrl+Enter to execute it.

You should see a single row in the results panel showing 35 total orders, a total revenue figure, and an average order value.

Seeing zero rows or an error?

  • Check that DBeaver is connected to ecommerce_analytics (the active database is shown in the toolbar dropdown). If it says postgres, switch to ecommerce_analytics.
  • Make sure you ran the seed data script in Step 2. Run SELECT COUNT(*) FROM orders; to confirm 35 rows exist.
  • If you see a syntax error, make sure you highlighted only the SELECT statement and its semicolon, not the comment lines above it.

Help me debug my revenue query.

Identify your top customers and products

Now you know the totals. The next question is: who is driving that revenue? Identifying top customers and products requires GROUP BY to aggregate per entity, combined with multi-table JOINs.

  • Add the top 5 customers query below your first query in analysis_queries.sql:
-- Top 5 Customers by Total Spending
SELECT
    c.first_name || ' ' || c.last_name AS customer_name,
    c.country,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS total_spending
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.country
ORDER BY total_spending DESC
LIMIT 5;

What does this query do?

This joins three tables: customers to orders to order_items. The GROUP BY clause collapses all of a customer's orders into one row so SUM can total their spending. ORDER BY ... DESC LIMIT 5 returns only the highest spenders.

The || operator in PostgreSQL concatenates strings, combining the first and last name into a single column.

  • Highlight the new query and press Ctrl+Enter to execute it.

You should see 5 rows. Notice which customer names keep appearing at the top. These repeat buyers are your most valuable customers.

  • Next, add the top 5 products query below:
-- Top 5 Products by Revenue
SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC
LIMIT 5;
  • Highlight and execute with Ctrl+Enter.

You should see 5 product rows ranked by revenue. The results show which products generate the most income for ShopStream.

  • Add one more query to see revenue broken down by category:
-- Revenue by Category
SELECT
    p.category,
    COUNT(DISTINCT oi.order_id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    ROUND(SUM(oi.quantity * oi.unit_price) * 100.0 /
        (SELECT SUM(quantity * unit_price) FROM order_items), 1) AS revenue_percentage
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;

What does the subquery do?

The (SELECT SUM(quantity * unit_price) FROM order_items) calculates overall revenue as a single number. Dividing each category's revenue by that total gives you each category's percentage share. This is a common pattern for calculating proportions without needing a separate query.

  • Highlight and execute the category query with Ctrl+Enter.

You should see 3 rows (one per category: Electronics, Clothing, Home & Kitchen) with each category's order count, revenue total, and percentage of overall revenue.

Getting a division by zero error?

This means your order_items table is empty. Re-run your seed_data.sql script from Step 2 to populate the data.

Why is my category revenue query failing?

Calculate monthly revenue trends

Individual totals tell you where you are today. But leadership also asks: are we growing? To answer that, you need to see revenue over time.

  • Add the monthly revenue trend query to the bottom of analysis_queries.sql:
-- Monthly Revenue Trend
SELECT
    DATE_TRUNC('month', o.order_date)::DATE AS month,
    COUNT(DISTINCT o.order_id) AS orders_count,
    SUM(oi.quantity * oi.unit_price) AS monthly_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

What is DATE_TRUNC?

DATE_TRUNC('month', o.order_date) truncates each date to the first day of its month. So 2025-03-15 becomes 2025-03-01. This lets GROUP BY collapse all orders in the same month into a single row.

The ::DATE cast removes the time portion so the output reads cleanly as a date.

  • Save your file with Ctrl+S.

Before you run this, take a guess: do you think ShopStream's revenue goes consistently up each month, or will it jump around?

  • Highlight the monthly revenue query and press Ctrl+Enter to execute it.

You should see 12 rows, one for each month of 2025. The revenue fluctuates from month to month. Some months are high, some dip noticeably.

Scan the numbers. You can see revenue going up and down, but can you tell at a glance whether the business is actually growing? Calculating the percentage change between months is not straightforward with GROUP BY alone. You would need a way to access the previous row's value from within the current row.

Why can't GROUP BY calculate growth?

GROUP BY collapses rows into groups and runs aggregate functions within each group independently. It has no concept of "the row before me" or "the row after me." Each group is an island. To compare one month's revenue against the previous month, you need a different tool.

Not seeing 12 rows?

  • If you see fewer than 12 rows, your seed data may not have loaded completely. Run SELECT COUNT(*) FROM orders; to confirm 35 orders exist.
  • If you see a syntax error near ::DATE, make sure you are connected to PostgreSQL (not another database type). The :: cast syntax is PostgreSQL-specific.

Help me fix my monthly revenue query.

✔️ Awesome, I've got everything!

Great. Make sure you have saved analysis_queries.sql with all five queries.

ⓧ I'd like to double check the full code

Your analysis_queries.sql should contain these queries in this order. Scroll through your file and confirm each one is present:

  • File header comments (ShopStream E-Commerce Analytics, Step 3).
  • Total Revenue, Total Orders, Average Order Value query (JOIN orders to order_items, uses COUNT DISTINCT, SUM, ROUND).
  • Top 5 Customers by Total Spending query (three-table JOIN, GROUP BY customer, ORDER BY DESC LIMIT 5).
  • Top 5 Products by Revenue query (JOIN products to order_items, GROUP BY product, ORDER BY DESC LIMIT 5).
  • Revenue by Category query (includes a subquery for revenue_percentage, GROUP BY category).
  • Monthly Revenue Trend query (uses DATE_TRUNC, GROUP BY month, ORDER BY month).

You now have a solid set of business queries that answer the basics: total revenue, top customers, top products, category breakdown, and monthly trends. But you hit a wall. You can see the numbers fluctuate month to month, yet calculating the actual growth rate requires comparing each row to the row before it. That is exactly the problem window functions solve. Next up, you will use LAG, RANK, and NTILE to unlock growth patterns hiding in this data.

Uncover Growth Patterns with Window Functions

Your monthly revenue query from Step 3 shows revenue fluctuating across 12 months. But looking at raw numbers does not answer the question leadership actually cares about: is ShopStream growing or shrinking?

Calculating the growth rate between consecutive months with basic GROUP BY requires joining a table to itself. Window functions solve this problem in a single pass and are the number one skill that separates intermediate SQL candidates from beginners in job interviews.

In this step, get ready to:
  • Calculate month-over-month revenue growth using both self-joins and window functions.
  • Rank customers by spending and classify them into quartiles.
  • Calculate running totals and moving averages to reveal growth trajectories.
Calculate month-over-month growth

You ended Step 3 with monthly revenue totals that go up and down. To calculate the actual percentage change between months, you need to compare each month's revenue to the previous month's revenue within the same query.

The straightforward approach is to self-join the monthly revenue results: join the current month row to the previous month row. Let's try it.

  • In DBeaver, scroll to the bottom of your analysis_queries.sql script and add the Step 4 section header and self-join query by pasting this code:
-- ============================================================
-- Step 4: Window Functions - Growth Patterns
-- ============================================================

-- Month-over-Month Growth (Self-Join Approach - Clunky)
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.order_date)::DATE AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
    curr.month,
    curr.revenue AS current_revenue,
    prev.revenue AS previous_revenue,
    ROUND((curr.revenue - prev.revenue) / prev.revenue * 100, 1) AS growth_pct
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
    ON curr.month = prev.month + INTERVAL '1 month'
ORDER BY curr.month;

What does this query do?

The WITH clause creates a CTE (Common Table Expression) called monthly_revenue that calculates revenue per month.

The main SELECT then joins this CTE to itself: curr is the current month and prev is the previous month (matched by adding 1 month interval). The growth percentage divides the difference by the previous month's revenue.

  • Highlight just the new query (from WITH monthly_revenue to the final semicolon) and press Ctrl+Enter to execute it.

You should see 12 rows. Each row shows a month, its revenue, the previous month's revenue, and the growth percentage. January shows NULL for previous revenue because there is no December 2024 data to compare against.

The query works, but look at what it took: you had to reference the same CTE twice, alias it as curr and prev, and manually specify the join condition with an interval calculation. If you ever needed to look back 2 or 3 months, the joins would multiply.

What is a window function?

A window function performs a calculation across a set of rows that are related to the current row. Unlike GROUP BY, which collapses rows into groups, a window function keeps every row intact and adds the calculation as a new column.

The LAG() window function reaches back to a previous row and grabs its value. Combined with OVER (ORDER BY month), it knows which row is "previous" because the rows are ordered by month.

  • Add the window function version below your self-join query by pasting this code:
-- Month-over-Month Growth (Window Function Approach - Elegant)
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.order_date)::DATE AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
    month,
    revenue AS current_revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100,
        1
    ) AS growth_pct
FROM monthly_revenue
ORDER BY month;

What changed here?

The self-join is gone. Instead, LAG(revenue) OVER (ORDER BY month) reaches back one row (the previous month) and returns its revenue value. No aliases, no join condition, no interval arithmetic.

The OVER (ORDER BY month) clause defines the window: rows ordered by month. LAG() then knows exactly which row is "previous" in that order.

Before you run this, do you expect the results to match the self-join version, or differ? Think about what each approach calculates.

  • Highlight the new query and press Ctrl+Enter to execute it.

You should see the same 12 rows with identical values: each month's revenue, the previous month's revenue, and the growth percentage. The results are the same, but the query is half the size and reads like plain English.

Why this approach matters for interviews

Window functions appear in 48% of entry-level data analyst job descriptions. When an interviewer asks you to calculate month-over-month growth, reaching for LAG() immediately signals you are beyond beginner-level SQL.

Seeing NULL for every growth_pct value?

Make sure you included the WITH monthly_revenue AS (...) CTE at the top of the query. Without it, the main SELECT has no data to operate on.

If only January shows NULL, that is correct. There is no previous month to compare against for the first row.

Still stuck? Help me debug my LAG() window function query that returns all NULLs for growth_pct.

Rank customers by total spending

Now that you can see growth trends, leadership wants to know which customers are the most valuable. Specifically, they want customers ranked by spending and classified into quartiles so the sales team knows who to prioritize.

  • Add the customer ranking query below your growth queries by pasting this code:
-- Customer Ranking by Total Spending
WITH customer_spending AS (
    SELECT
        c.customer_id,
        c.first_name || ' ' || c.last_name AS customer_name,
        SUM(oi.quantity * oi.unit_price) AS total_spending
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT
    customer_name,
    total_spending,
    RANK() OVER (ORDER BY total_spending DESC) AS spending_rank,
    NTILE(4) OVER (ORDER BY total_spending DESC) AS spending_quartile
FROM customer_spending
ORDER BY spending_rank;

What do RANK and NTILE do?

RANK() OVER (ORDER BY total_spending DESC) assigns a rank to each customer based on their spending. If two customers tie, they share the same rank and the next rank is skipped.

NTILE(4) OVER (ORDER BY total_spending DESC) divides customers into 4 equal groups (quartiles). Quartile 1 is the top 25% of spenders. This is a direct input for deciding who gets VIP treatment.

  • Highlight the customer ranking query and press Ctrl+Enter to execute it.

You should see all customers who have placed orders, ranked from highest to lowest spender. Each row shows their spending rank and which quartile (1-4) they fall into.

Query returns an error about customer_spending?

Make sure you highlighted the entire query including the WITH customer_spending AS line at the top. If you only highlight the SELECT portion, DBeaver does not know what customer_spending refers to.

Need help? Help me fix my customer ranking query that throws an error about an undefined CTE.

Calculate running totals and moving averages

Individual monthly numbers can be noisy. A running total shows cumulative revenue growth over the year. A 3-month moving average smooths out short-term spikes so you can see the underlying trend.

  • Add the running total and moving average query by pasting this code below your customer ranking query:
-- Running Revenue Total and 3-Month Moving Average
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.order_date)::DATE AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total,
    ROUND(
        AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
        2
    ) AS moving_avg_3mo
FROM monthly_revenue
ORDER BY month;

What does ROWS BETWEEN mean?

SUM(revenue) OVER (ORDER BY month) calculates a running total. Each row's value includes all previous rows plus itself. By December, the running total equals total annual revenue.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a sliding window of 3 rows: the current month plus the two months before it. The AVG of that window gives a 3-month moving average that smooths out month-to-month noise.

  • Highlight the running total query and press Ctrl+Enter to execute it.

You should see 12 rows with each month's revenue, a steadily increasing running total, and a moving average that smooths out the fluctuations you noticed in Step 3.

  • Save analysis_queries.sql by pressing Ctrl+S.

Moving average looks wrong for January and February?

For January, the moving average equals January's revenue (only 1 row in the window). For February, it averages January and February (only 2 rows available). The full 3-month window only applies from March onward. This is expected behavior.

Need help? Help me understand why my moving average values seem off for the first few months.

✔️ Awesome, I've got everything!

Great. Make sure you have saved analysis_queries.sql with all four new window function queries.

ⓧ I'd like to double check the full code

Scroll through your analysis_queries.sql file and confirm these sections appear in order:

  • Step 3 header and all five Step 3 queries (total revenue, top customers, top products, revenue by category, monthly revenue trend).
  • Step 4 header: -- Step 4: Window Functions - Growth Patterns.
  • Month-over-Month Growth (Self-Join Approach - Clunky) query using a CTE self-joined with INTERVAL '1 month'.
  • Month-over-Month Growth (Window Function Approach - Elegant) query using LAG(revenue) OVER (ORDER BY month).
  • Customer Ranking by Total Spending query using RANK() and NTILE(4).
  • Running Revenue Total and 3-Month Moving Average query using SUM() OVER and AVG() OVER (... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

You now have four window function queries that reveal growth patterns no basic GROUP BY could produce. Next up, you will use CTEs to build cohort retention analysis and Customer Lifetime Value calculations that hiring managers specifically look for on resumes.

Cohort Analysis and Customer Lifetime Value

In Step 4, you used window functions to calculate growth rates, rank customers, and smooth revenue trends. You can now see how fast the business is growing.

But leadership has a harder question: which customers come back, and which ones disappear? Knowing total revenue does not tell you whether your best buyers are still active or silently churning. Cohort analysis and Customer Lifetime Value are the analyses hiring managers specifically look for on resumes because they prove you can think beyond aggregates.

In this step, get ready to:
  • Calculate Customer Lifetime Value for every buyer in the database.
  • Build a cohort retention matrix showing which signup groups keep coming back.
  • Identify churned and at-risk customers with revenue impact figures, then push your project to GitHub.
Build the Customer Lifetime Value query

Customer Lifetime Value (CLV) answers the question: how much total revenue has each customer generated across their entire relationship with ShopStream? A single CTE calculates every metric you need per customer.

  • In DBeaver, switch to your analysis_queries.sql script tab.
  • Scroll to the end of your existing queries and add a section header to mark Step 5 by pasting this comment block:
-- ============================================================
-- Step 5: Cohort Analysis and Customer Lifetime Value
-- ============================================================
  • Below the header, paste the Customer Lifetime Value query:
-- Customer Lifetime Value
WITH customer_metrics AS (
    SELECT
        c.customer_id,
        c.first_name || ' ' || c.last_name AS customer_name,
        c.country,
        c.signup_date,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(oi.quantity * oi.unit_price) AS total_spending,
        ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value,
        MIN(o.order_date) AS first_order_date,
        MAX(o.order_date) AS last_order_date,
        MAX(o.order_date) - MIN(o.order_date) AS customer_lifespan_days
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.country, c.signup_date
)
SELECT
    customer_name,
    country,
    total_orders,
    total_spending AS lifetime_value,
    avg_order_value,
    customer_lifespan_days,
    first_order_date,
    last_order_date
FROM customer_metrics
ORDER BY lifetime_value DESC;

What does this query do?

The CTE customer_metrics calculates six dimensions per customer: total orders, total spending, average order value, first order date, last order date, and lifespan in days.

The outer SELECT renames total_spending as lifetime_value and orders the results so your most valuable customers appear at the top.

  • Highlight the CLV query (from the -- Customer Lifetime Value comment down to the semicolon) and click the orange execute button (or press Ctrl+Enter) to run it.
  • You should see a results table with 13 rows. The top customer has the highest lifetime_value and you can see how their customer_lifespan_days reflects how long they have been active.

Seeing 0 for customer_lifespan_days?

A lifespan of 0 days means that customer only ordered once (their first and last order are the same date). This is correct behavior, not an error. Customers with only one order will show 0 days of lifespan.

If you see an error instead, check that you included all the GROUP BY columns and that the CTE closing parenthesis ) appears before the outer SELECT.

Need help debugging?

Construct a cohort retention analysis

CLV tells you who your best customers are today. Cohort retention tells you whether customers who signed up in the same month keep coming back or drop off over time. This requires chaining three CTEs together.

  • Below your CLV query, paste the cohort retention analysis:
-- Cohort Retention Analysis
WITH customer_first_order AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date))::DATE AS cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_activity AS (
    SELECT
        cfo.cohort_month,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.order_date), cfo.cohort_month))::INTEGER AS months_since_first,
        COUNT(DISTINCT o.customer_id) AS active_customers
    FROM orders o
    JOIN customer_first_order cfo ON o.customer_id = cfo.customer_id
    GROUP BY cfo.cohort_month, EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.order_date), cfo.cohort_month))
),
cohort_size AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT customer_id) AS total_customers
    FROM customer_first_order
    GROUP BY cohort_month
)
SELECT
    ca.cohort_month,
    cs.total_customers AS cohort_size,
    ca.months_since_first,
    ca.active_customers,
    ROUND(ca.active_customers * 100.0 / cs.total_customers, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_size cs ON ca.cohort_month = cs.cohort_month
ORDER BY ca.cohort_month, ca.months_since_first;

What does this query do?

Three CTEs work together like a pipeline:

  • customer_first_order finds the month each customer placed their first order. This is their "cohort."
  • cohort_activity calculates how many months passed between each customer's cohort month and every subsequent order they placed.
  • cohort_size counts how many customers belong to each cohort so you can calculate percentages.

The final SELECT joins these together and produces retention_pct: the percentage of each cohort that was still active N months after their first order.

Before you run this query, think about what you expect to see. Do you think most cohorts retain 100% of customers at month 1, or will there be significant drop-off?

  • Highlight the cohort retention query and press Ctrl+Enter to execute it.
  • You should see a matrix-like result set showing each cohort_month, the months_since_first value, and the retention_pct for that combination. Cohorts at month 0 show 100% (everyone is active in their first month). Subsequent months reveal which cohorts retain customers and which do not.

What is EXTRACT(MONTH FROM AGE(...))?

This is PostgreSQL-specific syntax that calculates the number of whole months between two dates. AGE(date1, date2) returns an interval, and EXTRACT(MONTH FROM ...) pulls just the month component. It handles varying month lengths correctly.

Query returning an error about data types?

Make sure you included the ::INTEGER cast after the EXTRACT expression. Without it, PostgreSQL may treat the result as a numeric type that does not group cleanly.

Also check that each CTE is separated by a comma (after the closing parenthesis) and that there is no comma before the final SELECT.

Still stuck?

Identify churned and at-risk customers

Retention patterns are useful, but leadership wants names. Which specific customers have stopped ordering, and how much revenue do they represent? This turns data into a concrete re-engagement recommendation.

  • Below the cohort retention query, paste the churned and at-risk customer query:
-- Churned and At-Risk Customers
WITH customer_last_order AS (
    SELECT
        c.customer_id,
        c.first_name || ' ' || c.last_name AS customer_name,
        MAX(o.order_date) AS last_order_date,
        (SELECT MAX(order_date) FROM orders) - MAX(o.order_date) AS days_since_last_order,
        SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT
    customer_name,
    last_order_date,
    days_since_last_order,
    total_revenue,
    CASE
        WHEN days_since_last_order > 90 THEN 'Churned'
        WHEN days_since_last_order > 60 THEN 'At Risk'
        ELSE 'Active'
    END AS customer_status
FROM customer_last_order
ORDER BY days_since_last_order DESC;

Why use (SELECT MAX(order_date) FROM orders) instead of CURRENT_DATE?

Using the dataset's own maximum order date makes results deterministic. No matter when you run this query in the future, you get the same results. This is important for a portfolio project because a hiring manager can clone your repo, run the queries, and see exactly what you describe.

  • Highlight and run the churned customers query.
  • You should see customers sorted by days_since_last_order descending. Each row shows a customer_status of Churned, At Risk, or Active based on the CASE thresholds.

Individual customer names are useful, but leadership wants the big picture. How many customers fall into each category, and what is the total revenue at stake?

  • Add the churn summary query below the previous one:
-- Churn Summary
WITH customer_last_order AS (
    SELECT
        c.customer_id,
        MAX(o.order_date) AS last_order_date,
        (SELECT MAX(order_date) FROM orders) - MAX(o.order_date) AS days_since_last_order,
        SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id
)
SELECT
    CASE
        WHEN days_since_last_order > 90 THEN 'Churned'
        WHEN days_since_last_order > 60 THEN 'At Risk'
        ELSE 'Active'
    END AS customer_status,
    COUNT(*) AS customer_count,
    ROUND(SUM(total_revenue), 2) AS total_revenue_at_stake
FROM customer_last_order
GROUP BY
    CASE
        WHEN days_since_last_order > 90 THEN 'Churned'
        WHEN days_since_last_order > 60 THEN 'At Risk'
        ELSE 'Active'
    END
ORDER BY customer_count DESC;

What does this tell leadership?

This summary converts individual customer data into an executive-ready insight. It answers: "How many customers have we lost, and what is the dollar value of that loss?" The total_revenue_at_stake column makes the business case for a re-engagement campaign.

  • Highlight and run the churn summary query.
  • You should see 3 rows: one for each status (Active, At Risk, Churned) with the count of customers and total revenue per group.
  • Save your analysis_queries.sql file (Ctrl+S).

CASE expression appearing in results instead of a label?

If you see raw CASE syntax in the output column header, make sure the CASE expression in the GROUP BY matches the one in the SELECT exactly. PostgreSQL requires the full CASE expression to be repeated in GROUP BY (you cannot reference the alias customer_status in the GROUP BY clause).

Help me fix my churn summary query.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis_queries.sql with all four Step 5 queries.

ⓧ I'd like to double check the full code

Your analysis_queries.sql file is too long to show in one block. Scroll through and confirm these sections exist in order:

  • Step 3 header and queries (Total Revenue, Top 5 Customers, Top 5 Products, Revenue by Category, Monthly Revenue Trend).
  • Step 4 header and queries (Self-Join Growth, Window Function Growth, Customer Ranking, Running Total and Moving Average).
  • Step 5 header and queries (Customer Lifetime Value, Cohort Retention Analysis, Churned and At-Risk Customers, Churn Summary).

If any section is missing, scroll up to the relevant substep and paste the code again.

Push your project to GitHub

Your analytics project is complete. Pushing it to GitHub makes it portfolio-ready so hiring managers can review your SQL skills directly from a link on your resume.

  • Open your browser and go to github.com. Sign in to your account.
  • In the upper-right corner, click the + menu and select New repository.
  • In the Repository name field, type shopstream-sql-analytics.
  • In the Description field, type E-commerce analytics project demonstrating window functions, CTEs, cohort analysis, and CLV calculations.
  • Select Public visibility so hiring managers can view it.
  • Check the box for Add a README file.
  • Click Create repository.

Your repository is created with a README. Now upload your SQL files.

  • On the repository page, click the Add file dropdown and select Upload files.
  • Open the folder on your computer where DBeaver saved your SQL scripts.
  • Drag and drop schema.sql, seed_data.sql, and analysis_queries.sql into the browser upload area.
  • In the commit message field, type Add SQL analytics project files.
  • Ensure Commit directly to the main branch is selected.
  • Click Commit changes.

Where did DBeaver save my files?

By default, DBeaver saves SQL scripts in a workspace folder. To find them, right-click on a script tab in DBeaver and select Show in file system. This opens the folder containing your files in File Explorer.

  • Verify your upload by checking the repository page. You should see all three .sql files listed alongside the README.md.

Files not appearing after upload?

Refresh the repository page in your browser. If files still do not appear, click Add file > Upload files again and re-select them. Make sure you clicked Commit changes at the bottom of the upload page.

Help me upload files to my GitHub repository.

You now have a complete analytics project: CLV rankings, cohort retention, and churn analysis, all documented in a public GitHub repository. Next up, take on the Secret Mission and build an RFM segmentation model that classifies customers into actionable marketing segments.

Secret mission

RFM Customer Segmentation

Marketing teams do not treat all customers the same. Can you build an RFM segmentation model that automatically classifies each ShopStream customer into actionable segments based on their purchase behavior? This technique is used by real e-commerce companies to decide who gets a loyalty reward, who gets a win-back campaign, and who needs attention before they disappear.

Clean Up Your Resources

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 using free, open-source tools, so there are no ongoing costs.

Resources you used:

  • PostgreSQL server (running as a background Windows service).
  • DBeaver Community Edition (installed desktop application).
  • The ecommerce_analytics database (stored within PostgreSQL).

Keep everything running

No action needed. Choose this if you are still actively building or want to practice more SQL queries.

  • PostgreSQL runs as a background service on your machine using minimal resources.
  • Your ecommerce_analytics database persists between restarts. You can reopen DBeaver any time to run queries.
  • Your GitHub repository is live and portfolio-ready for hiring managers to review.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory while keeping all your data intact. Start it again when you want to practice.

  • Press the Windows key, type services.msc, and press Enter to open Windows Services.
  • Scroll down to find the service named postgresql (it may include the version number in the name).
  • Right-click the service and select Stop.
  • To start it again later, right-click the same service and select Start.

Delete - I don't want to use this again

Remove all project resources from your machine. This cannot be undone.

  • Uninstall PostgreSQL: open Settings, select Apps, then Installed apps. Find PostgreSQL in the list, click the three-dot menu, and select Uninstall. This removes the server and all databases including ecommerce_analytics.
  • Uninstall DBeaver: in the same Installed apps list, find DBeaver Community, click the three-dot menu, and select Uninstall.
  • Your GitHub repository remains online unless you explicitly delete it. To keep it as a portfolio piece, leave it as-is. To delete it, go to your repository on GitHub, click Settings, scroll to the bottom to the Danger Zone section, and click Delete this repository.

Nice Work!

Nice Work!

You just built a complete SQL analytics project from the ground up. You designed a relational database, wrote advanced queries that answer real business questions, and published a portfolio-ready repository that demonstrates the skills hiring managers look for.

You learned how to:

  • Design a relational database schema and write business-focused queries using JOINs, aggregations, and GROUP BY to calculate revenue metrics, identify top customers, and analyze product performance.
  • Use window functions (LAG, RANK, NTILE, running totals, and moving averages) to calculate month-over-month growth rates, customer rankings, and revenue trajectories.
  • Build cohort retention analysis and Customer Lifetime Value calculations using Common Table Expressions, and identify churned and at-risk customers with concrete revenue impact figures.
  • Secret Mission: Created an RFM segmentation model that classifies customers into actionable marketing segments (Champion, Loyal, At Risk, Lost) with summary statistics per segment.

Ready to quiz yourself?