A SQL and Power BI portfolio project demonstrating analytical SQL depth (CTEs, window functions, multi table joins, ranking, running totals) on a relational bookstore dataset, with a business stakeholder ready Power BI dashboard as the final deliverable.
This project simulates a mid size online bookstore and answers the operational and strategic questions a sales or operations manager would actually ask: is revenue growing, who are our best customers, which books and genres perform best, which authors and publishers drive the most sales, and where do orders stall in fulfillment. The SQL layer answers each question directly, and the Power BI dashboard turns those answers into something a non technical stakeholder can explore on their own.
Base schema and initial seed data sourced from NMsby/BookstoreDB (MIT licensed), a 15 table normalized MySQL schema covering books, authors, customers, orders, and shipping.
Modifications made for this project:
- Adapted schema and sample data from MySQL syntax to DuckDB
- Added a
genretable andbook.genre_idforeign key, not present in the original schema - Generated synthetic data on top of the real seed rows to reach realistic analytical volume. The real seed data alone was only 144 rows across all tables, too small for meaningful ranking or window function analysis
- Regenerated book titles for the synthetic books to be readable and portfolio appropriate
Final scale: 16 tables, 400 customers, 200 books, 1,804 orders, 4,203 order line items, spanning October 2023 to July 2026 (analytical queries filtered to September 2024 onward, see limitations below).
- Database engine: DuckDB
- Data generation: Python (Faker, seeded for reproducibility, seed 42)
- Analysis: SQL (CTEs, window functions, joins, ranking, running totals)
- Dashboard: Power BI Desktop, connected directly to the normalized tables with DAX measures rather than flattened query outputs, so every visual stays cross filterable
| # | Question | SQL technique |
|---|---|---|
| 1 | What is cumulative revenue over time? | Running total (window function) |
| 2 | What is the underlying revenue trend, smoothed? | Moving average (window frame) |
| 3 | Is the store growing month over month? | LAG (window function) |
| 4 | What are the top selling books within each genre? | RANK / DENSE_RANK |
| 5 | Who are our most valuable customers? | RFM segmentation (CTE) |
| 6 | Where is demand concentrated by genre and country? | CTE + multi table join |
| 7 | Which authors and publishers perform best? | Multi table join + aggregation |
| 8 | Which books are frequently bought together? | Self join |
| 9 | Where do orders stall in fulfillment? | Order status funnel |
All queries live in queries/, each with a comment header stating the business question it answers.
bookstore-sql-analytics/
├── README.md
├── screenshots/ Dashboard page screenshots
├── queries/ The 9 analytical SQL queries
├── duckdb/ Schema, data generation, and loading scripts
├── docs/ Schema reference
├── powerbi/ Power BI report (.pbip, .pbix, semantic model)
└── requirements.txt
The DuckDB database file is not included in this repo, since it is a generated artifact and fully reproducible from the scripts below.
git clone https://github.com/RaghadAlamoudi/bookstore-sql-analytics.git
cd bookstore-sql-analytics
pip install -r requirements.txt
python duckdb/load.py # loads schema and real seed data
python duckdb/03_generate_synthetic.py # generates synthetic data, seed 42Run these from the project root, not from inside duckdb/ — that folder shares its name with the duckdb pip package, so running Python from within it makes import duckdb resolve to the folder instead of the library.
Then open powerbi/bookstore-analytics.pbip in Power BI Desktop and point the data source to your local bookstore.duckdb.
KPI cards for total revenue, orders, customers, and average order value, alongside a monthly revenue trend, a cumulative running total, a 3 month moving average, and the latest month over month change.
Customers segmented by RFM score (Champions, Loyal Customers, Needs Attention, At Risk, No Purchases), with revenue contribution per segment and a full scored customer detail table.
Top books ranked within each genre, a genre by country revenue matrix, and the top frequently bought together book pairs.
Top authors and top publishers ranked by realized revenue, with nationality and unit sold detail.
An order status funnel from Delivered down through Cancelled, Returned, Shipped, Processing, Refunded, Pending, and On Hold, alongside a table of in progress orders exceeding expected time in their current stage.
Revenue: $65,718.89 total, 1,804 orders, $41.28 average order value. The latest month is up 18.5 percent over the prior month. The cumulative running total and the 3 month moving average both track upward from roughly $1,000 in September 2024 to over $6,000 by mid 2026, so the month over month gain is part of a sustained trend, not a single good month.
RFM segmentation: 115 of 400 customers (29 percent) account for $46,558.10 (71 percent of revenue). These are the Champions segment, scored highest on recency, frequency, and monetary value. Loyal Customers, the next segment down, add $11,049.80. Needs Attention, At Risk, and No Purchases together make up the remaining 198 customers and roughly 12 percent of revenue. Revenue is concentrated in a minority of customers, not spread evenly across the base.
Country: US is the top market at $18,790.92 from 117 customers, 29 percent of revenue. No other country individually exceeds that, and the next four (UK, Canada, France, Germany) combine for a comparable share. Revenue by customer home country is distributed across ten countries rather than concentrated in one.
Fulfillment: 71.83 percent of orders reach Delivered, 8.07 percent Cancelled, 5.23 percent Returned. The remaining orders are split across Shipped, Processing, Refunded, Pending, and On Hold. The stalled orders table lists individual orders that have exceeded typical time in their current stage, which is the actionable output, not the aggregate percentages on their own.
Top performers: Charles Ballard is the top author by realized revenue, "A Memoir of Grit" is the top book in Biography and Memoir, and "The Silver Storm" plus "The Art of Focus" is the most frequent co-purchase pair at 20 occurrences. These are the specific names and counts behind the ranking and self-join queries, not just the technique being demonstrated.
- This dataset is a hybrid of real seed data (8 books and 8 orders from the original source repository) and synthetic data generated to reach analytical scale. It is not real transactional data, and specific figures should be read as illustrative of technique rather than as claims about an actual business.
- A small number of original seed orders (order_id 4, 6, and 8) have a minor mismatch between
cust_order.total_amountand the actual sum of their line items, a pre existing inconsistency in the source repository's own sample data, not introduced during this project. All analysis in this project usesorder_linederived revenue rather thantotal_amountfor this reason. - The real seed data's order dates (October to December 2023) are followed by a data gap before the synthetic window begins in September 2024. Time series queries are filtered to 2024-09-01 onward to avoid a distorted month over month spike caused by this gap.
- Genre by country revenue is calculated using each customer's home address (default address, falling back to any address on file if no default is set), matching the logic in
queries/06_revenue_by_genre_country.sql. This is a deliberate choice: 76 of the 400 customers have addresses on file in more than one country, and attributing revenue to every address a customer holds would inflate total revenue by roughly 21 percent through double counting. Attributing revenue to a single home country per customer avoids this.
MIT, see LICENSE. Base schema credit: NMsby/BookstoreDB (MIT).