Online Bookstore SQL Analysis | PostgreSQL -
Date
Service
Data Analysis
Client
Personal Project
Project Overview
This project demonstrates how SQL can be used to analyze business data from an online bookstore. Using PostgreSQL, I designed a relational database, imported transactional data, and answered real business questions related to sales, customers, inventory, and revenue.
The goal of this project was not only to practice SQL syntax but also to understand how SQL helps businesses make informed decisions from their data.
Business Problem
An online bookstore stores information about books, customers, and orders every day. As the amount of data grows, it becomes difficult to answer important business questions manually, such as:
Which books generate the highest revenue?
Which customers spend the most?
Which genres are performing well?
How are sales changing over time?
Which books need inventory attention?
This project demonstrates how SQL can answer these questions efficiently using structured queries.
Dataset
The project consists of three related tables.
Books : Stores information about each book.
- Book ID - Title - Author - Genre - Published Year - Price - Stock
Customers : Stores customer details.
- Customer ID - Customer Name - Email - Phone Number - City - Country
Orders : Stores every purchase made by customers.
- Order ID - Customer ID - Book ID - Order Date - Quantity - Total Amount
Tools Used
PostgreSQL
pgAdmin
CSV Data Import
Project Methodology
The project was completed using the following workflow:
Designed the relational database.
Created tables with Primary Keys and Foreign Keys.
Imported data from CSV files.
Validated the imported data.
Wrote SQL queries to solve business problems.
Analyzed query results to generate business insights.
Dashboard Overview
Database Design
Data Import
Data Validation
SQL Query Development
Business Analysis
Insights & Recommendations.
Key Highlights
Insights
The SQL analysis helped uncover several useful business insights:
Sales performance varies across different book genres.
A small group of customers contributes significantly to total revenue.
Some books consistently outperform others in sales.
Revenue trends can be monitored over time to identify seasonal patterns.
SQL window functions make it easier to analyze rankings and cumulative sales without writing complex procedural code.
Recommendations
Based on the analysis:
Maintain sufficient stock for high-selling books.
Promote underperforming categories through marketing campaigns.
Reward loyal customers with targeted offers.
Monitor monthly revenue trends for better inventory planning.
Focus future purchasing decisions on genres with strong sales performance.
