SQL E-Commerce Sales Analysis

A PostgreSQL portfolio project built to practice and demonstrate SQL skills for data analysis using a four-table e-commerce dataset.

The project contains 174 progressively challenging SQL questions, starting with basic data retrieval and filtering and progressing to joins, aggregations, subqueries, CTEs, window functions, date and string functions, set operations, and views.

Project Objective

This project demonstrates how SQL can be used to:

• Retrieve and filter business data
• Analyze customers, products, and orders
• Calculate sales and revenue metrics
• Compare performance using averages and rankings
• Analyze customer and product behavior
• Work with relational data using joins
• Build reusable queries using CTEs and views
• Apply advanced analytical SQL techniques

Database Schema

The project uses four related tables:

customers ──< orders ──< orders_item >── products

Tables

Table	Description
customers	Customer information
products	Product and pricing information
orders	Order-level information
orders_item	Individual products included in each order

Key Relationships

• customers.customer_id → orders.customer_id
• orders.order_id → orders_item.order_id
• products.product_id → orders_item.product_id

SQL Concepts Covered

1. Basic SQL

• SELECT
• WHERE
• ORDER BY
• LIMIT
• DISTINCT
• LIKE
• BETWEEN
• IN
• AND / OR

2. Aggregate Functions

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()
• GROUP BY
• HAVING

3. Joins

• INNER JOIN
• Multi-table joins
• Relational data analysis across customers, orders, products, and order items

4. Conditional Logic

• CASE WHEN
• Customer segmentation
• Product price classification
• Revenue classification

5. String Functions

• UPPER()
• LOWER()
• LENGTH()
• CONCAT()
• SUBSTRING()
• REPLACE()
• TRIM()

6. Date & Time Functions

• CURRENT_DATE
• NOW()
• EXTRACT()
• AGE()
• TO_CHAR()
• Date filtering and calculations

7. Set Operations

• UNION
• UNION ALL

8. Subqueries

• Comparative analysis
• Average-based filtering
• Nested queries
• Existence checks

9. Common Table Expressions

• WITH
• Layered queries
• Customer revenue analysis
• High-value order analysis

10. Window Functions

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• Running totals
• Partitioned calculations
• Category-level rankings and averages

11. Views

• CREATE VIEW
• Reusable reporting queries
• Customer-order analysis

12. Database Constraints

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• CHECK
• DEFAULT

Sample Query

The following CTE calculates total revenue generated by each customer:

WITH customer_revenue AS (
    SELECT
        c.customer_name,
        SUM((oi.quantity * p.unit_price) - oi.discount) AS total_revenue
    FROM customers AS c
    INNER JOIN orders AS o
        ON c.customer_id = o.customer_id
    INNER JOIN orders_item AS oi
        ON o.order_id = oi.order_id
    INNER JOIN products AS p
        ON oi.product_id = p.product_id
    GROUP BY c.customer_name
)
SELECT *
FROM customer_revenue;

Tools & Technologies

• Database: PostgreSQL
• Language: SQL
• Data: Illustrative e-commerce dataset
• Tools: PostgreSQL / pgAdmin, Excel

Project Files

• SQL-Ecommerce-Sales-Analysis.sql — Complete SQL analysis and practice questions
• Excel data file — Source dataset used for the project

Project Status

Completed

This is a learning and portfolio project created to demonstrate practical SQL skills for Data Analyst roles.

The dataset is illustrative and does not contain confidential or production business data.

Author

Punit Godiyal

Aspiring Data Analyst SQL | Power BI | Excel