Write advanced queries in PostgreSQL (multi-table joins, subqueries, CTEs, and window rankings) using the retail schema from Setup Project 2, and run query optimization plans.
Domain / Environment
Retail DB / Postgres VM / DBeaver
Difficulty
Intermediate (3/5)
Course Module
SQL for Data Science
Deliverables
SQL Query Script & Optimization EXPLAIN logs
1. System Architecture & Relational Schema (ERD)
The diagram below displays the Entity Relationship Diagram (ERD) for the retail database. Orders map to customers via customer foreign keys, and are composed of multiple order item records linked to specific product categories.
2. Part 1: Step-by-Step Action Items & Key Execution Steps
STEP 1
Verify PostgreSQL Service Status
Open terminal inside the VM and ensure the PostgreSQL background service daemon is running.
$ sudo systemctl status postgresql
This checks the database server process. If inactive, start it using `sudo systemctl start postgresql`.
STEP 2
Launch DBeaver Database Client GUI
Open the DBeaver client through your virtual desktop applications dashboard dashboard.
Click Applications menu (grid icon on bottom left) -> Type: DBeaver -> Click DBeaver icon
This boots the SQL editor workspace window. Connect to the local `retail_db` using credentials created in Setup Project 2.
STEP 3
Create Relational Database Tables
Open an SQL editor panel in DBeaver and run queries to build the retail schema.
Right-click "retail_db" -> Click "SQL Editor" -> Click "New SQL Script" -> Paste Table DDL from Part 2 -> Press Ctrl + Enter
This creates the relational tables (customers, products, orders, order_items) in PostgreSQL.
STEP 4
Seed Mock Data Records
Insert sample values into the tables to populate the schema.
In the SQL Editor window -> Paste the INSERT SQL statements from Part 2 -> Click the Orange Execute Triangle icon on left side
This populates the tables with customer profiles and order histories for testing.
STEP 5
Run Window Ranking Function Queries
Execute queries with window functions to rank category sales and analyze customer purchase frequencies.
In the SQL Editor window -> Paste window query logic -> Click Execute to view results in bottom panel grids
This runs analytical window function queries, ranking customer and product revenue directly on the database engine.
STEP 6
Inspect Query Performance using EXPLAIN ANALYZE
Run query plan analysis to identify bottlenecks in data retrieval.
In DBeaver Editor -> Prefix your query with: EXPLAIN ANALYZE -> Execute query -> Click "Text" tab in results grid
This outputs the execution plan, showing scan methods (e.g. Sequential Scan vs Index Scan) and cost calculations.
STEP 7
Create Search Indexes for Optimization
Add B-Tree indexes to columns used in filters or joins to optimize search performance.
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_date ON orders(order_date);
This builds search indexes on date and ID fields, changing costly sequential scans into index scans.
3. Operational Pipeline Architecture
The flowchart below outlines the SQL query compilation pipeline. It shows the processing order from raw table scans, join merges, and filter steps to executing window rankings and exporting results.
4. Part 2: Complete Deliverable Assets & Production Templates
To run the SQL analysis, we need the table definition queries and advanced query files. Below is a line-by-line explanation of the code, followed by the combined script.
Step-by-Step Code Construction
Lines 1 - 25
Define SQL Tables DDL
Write DDL tables (customers, products, orders, order_items) with foreign keys.
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(50),
join_date DATE
);
-- orders table with foreign key constraint
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
total_amount DECIMAL(10,2)
);
This DDL registers relational tables in PostgreSQL, defining primary key indexes and foreign key references.
Lines 26 - 45
Multi-Table Relational JOINs
Write a query using INNER JOINs to link customer names, order dates, products, and order item details.
SELECT c.name, o.order_date, p.product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;
This query links orders, customers, and order items to return customer purchase summaries.
Lines 46 - 58
CTE (Common Table Expression) Sales Summary
Create a temporary CTE to find monthly sales totals that exceed the average monthly sales.
WITH monthly_totals AS (
SELECT DATE_TRUNC('month', order_date) as month_val, SUM(total_amount) as revenue
FROM orders
GROUP BY 1
)
SELECT month_val, revenue
FROM monthly_totals
WHERE revenue > (SELECT AVG(revenue) FROM monthly_totals);
This CTE groups order values by month and filters for months that brought in above-average revenue.
Lines 59 - 70
Window Function Ranking Query
Use `DENSE_RANK()` to rank products by sales revenue within each category.
SELECT category, product_name, SUM(oi.quantity * oi.unit_price) as revenue,
DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) as sales_rank
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY category, product_name;
This partitions products by category and ranks them based on sales revenue.
Production templates
SQL Analysis Script (Save as ~/Projects/retail_analysis.sql):
-- retail_analysis.sql - Relational Schema and Advanced Analytics Queries-- 1. Relational Table Schema DDL CreationCREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50),
join_date DATE DEFAULT CURRENT_DATE
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10, 2)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
order_date DATE NOT NULL,
total_amount DECIMAL(10, 2)
);
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
product_id INT REFERENCES products(product_id),
quantity INT CHECK (quantity > 0),
unit_price DECIMAL(10, 2)
);
-- 2. Seed Mock Database ValuesINSERT INTO customers (name, city, join_date) VALUES
('Alice Johnson', 'New York', '2025-01-10'),
('Bob Smith', 'Boston', '2025-03-15'),
('Charlie Brown', 'New York', '2025-04-20'),
('Diana Prince', 'Chicago', '2025-06-05');
INSERT INTO products (product_name, category, price) VALUES
('Notebook Computer', 'Electronics', 1200.00),
('Ergonomic Chair', 'Furniture', 250.00),
('Desk Organizer', 'Office Supplies', 15.50),
('Wireless Mouse', 'Electronics', 25.00),
('Standing Desk', 'Furniture', 650.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2026-08-01', 1225.00),
(2, '2026-08-01', 250.00),
(3, '2026-08-02', 15.50),
(4, '2026-08-03', 1225.00),
(1, '2026-08-04', 25.00);
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 1200.00),
(1, 4, 1, 25.00),
(2, 2, 1, 250.00),
(3, 3, 1, 15.50),
(4, 1, 1, 1200.00),
(4, 4, 1, 25.00),
(5, 4, 1, 25.00);
-- 3. Business Query 1: Join Query (Customer purchase details)SELECT c.name, o.order_date, p.product_name, oi.quantity, (oi.quantity * oi.unit_price) as item_revenue
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_date DESC;
-- 4. Business Query 2: Subquery / CTE (Above Average monthly sales)WITH monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) as sale_month, SUM(total_amount) as total_revenue
FROM orders
GROUP BY 1
)
SELECT sale_month, total_revenue
FROM monthly_sales
WHERE total_revenue >= (SELECT AVG(total_revenue) FROM monthly_sales);
-- 5. Business Query 3: Window Functions (Rank category products)SELECT p.category, p.product_name, SUM(oi.quantity * oi.unit_price) as total_sales,
DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) as sales_rank
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category, p.product_name;
-- 6. Query Optimization with EXPLAIN ANALYZEEXPLAIN ANALYZESELECT customer_id, SUM(total_amount)
FROM orders
WHERE order_date >= '2026-08-01'GROUP BY customer_id;
-- Create B-Tree Index to optimize date scansCREATE INDEX idx_orders_order_date ON orders(order_date);
-- Verify performance boostEXPLAIN ANALYZESELECT customer_id, SUM(total_amount)
FROM orders
WHERE order_date >= '2026-08-01'GROUP BY customer_id;
5. Deliverables Summary
Verify that the following configurations and outputs exist inside your project workspace.
Created Files / Templates
~/Projects/retail_analysis.sql - Schema and query scripting file.
Verification Artifacts / Execution Proof
Correct DDL execution logs in the database.
Joined result rows linking Alice and Bob to their respective item orders.
DENSE_RANK rankings ranking 'Notebook Computer' first in 'Electronics'.
Performance improvement logs visible in EXPLAIN ANALYZE execution text.
6. Closing Explanation: Why We Did This & What It Accomplishes
Architectural Intent & Operational Impact
Why We Did This
CTEs separate complex queries into readable steps, making SQL code easier to maintain.
Window functions calculate metrics across partitions without collapsing rows, simplifying ranking analysis.
B-Tree indexes speed up row filtering, preventing slow table scans as datasets grow.
What This Accomplishes
Initializes and seeds relational schemas in the database.
Runs joins, subqueries, and window partition functions.
Profiles query execution plans and applies index optimization sweeps.