SQL Lab — Sujit Murari | Business Analytics Portfolio

• JOIN OPERATIONS — Multi-table relationship queries

INNER JOIN — Order + Customer + Product

3 TABLES

Full order details — customer name, product bought, quantity and total amount. Three-table JOIN from the e-commerce database.

SELECT o.order_id,
       c.full_name,
       p.product_name,
       oi.quantity,
       (oi.quantity * oi.unit_price) AS amount
FROM orders o
JOIN customers c  ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id  = oi.order_id
JOIN products p   ON oi.product_id = p.product_id;

LEFT JOIN — Customers with NO Orders

NULL FILTER

Identify customers who exist in the system but have never placed an order — useful for re-engagement campaigns.

SELECT c.*
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

JOIN — Order Status vs Payment Status

RECONCILIATION

Cross-check order fulfillment against payment status — spots mismatches like delivered orders with pending payments.

SELECT o.order_id,
       o.status       AS order_status,
       p.status       AS payment_status
FROM orders o
LEFT JOIN payments p
  ON o.order_id = p.order_id;

INNER JOIN — Student × Teacher (Specialization Match)

DOMAIN MATCH

Match students to teachers whose subject aligns with the student's specialization — from the University database.

SELECT s.full_name  AS Student,
       t.full_name  AS Teacher,
       s.specialization
FROM students s
INNER JOIN teachers t
  ON s.specialization = t.subject
ORDER BY s.specialization;

LEFT JOIN — All Staff Right Join Products by Category

RIGHT JOIN

Show all products and any staff whose department matches the product category — ensures products without matching dept still appear.

SELECT p.product_name,
       p.category,
       s.name,
       s.department
FROM staff s
RIGHT JOIN products p
  ON s.department = p.category;

UNION — All Emails (Students + Teachers + Staff)

UNION

Merge email lists from three separate tables into a single deduplicated contact list using UNION.

SELECT email FROM students
UNION
SELECT email FROM teachers
UNION
SELECT email FROM staff;

• WINDOW FUNCTIONS — ROW_NUMBER • RANK • DENSE_RANK • PARTITION BY

RANK vs DENSE_RANK vs ROW_NUMBER

COMPARISON

Side-by-side comparison of all three ranking functions on the same salary dataset — shows exactly how they handle ties differently.

SELECT employee_name,
       department,
       salary,
       RANK()       OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

PARTITION BY — Rank Within Department

PARTITION BY

Rank employees by salary independently within each department — resets the rank counter for every department group.

SELECT employee_name,
       department,
       salary,
       ROW_NUMBER() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS dept_row_num
FROM employees;

Subquery — Get Employees at Rank #1 per Dept

TOP N

Wrap a window function in a subquery to filter — returns the highest-paid employee in each department using DENSE_RANK.

SELECT *
FROM (
  SELECT employee_name,
         department,
         salary,
         DENSE_RANK() OVER (
           PARTITION BY department
           ORDER BY salary DESC
         ) AS dept_rank
  FROM employees
) AS ranked
WHERE dept_rank = 1;

Running Total — Stock by Category

RUNNING TOTAL

Cumulative running total of product stock within each category — useful for inventory analysis and reorder planning.

SELECT product_name,
       category,
       stock,
       SUM(stock) OVER (
         PARTITION BY category
         ORDER BY product_id
       ) AS running_stock
FROM products;

ROW_NUMBER() — Sales Ranking with DENSE_RANK

SALES RANK

Rank products by sales amount using DENSE_RANK — tied sales amounts receive the same rank with no gaps in numbering.

SELECT ProductID,
       ProductName,
       Category,
       SalesAmount,
       DENSE_RANK() OVER (
         ORDER BY SalesAmount DESC
       ) AS DenseRankNum
FROM Products_rank;

Staff Salary Rank within Department

HR ANALYTICS

Rank staff by salary within their department — practical for compensation benchmarking and identifying outliers per team.

SELECT name,
       department,
       salary,
       RANK() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS rank_no
FROM staff;

• SUBQUERIES — Nested logic for comparative analysis

Products Above Average Price

AVG BENCHMARK

Filter products priced above the platform average — the subquery dynamically computes the average so the threshold updates automatically.

SELECT *
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

Highest-Paid Staff per Department

CORRELATED

Correlated subquery — for each row, checks if the salary equals the max salary in the same department. Returns one top earner per dept.

SELECT *
FROM staff s
WHERE salary = (
  SELECT MAX(salary)
  FROM staff
  WHERE department = s.department
);

Products Below Average Stock

INVENTORY ALERT

Flag products with below-average stock — real inventory management use case for automated reorder triggers.

SELECT product_name, stock
FROM products
WHERE stock < (
  SELECT AVG(stock)
  FROM products
);

Staff Earning Above Chennai HR Salary

COMPARATIVE

Use a specific row as a salary benchmark — anyone earning more than the Chennai HR employee, across all departments.

SELECT *
FROM staff
WHERE salary > (
  SELECT salary
  FROM staff
  WHERE department = 'HR'
    AND city = 'Chennai'
);

Products More Expensive than Furniture Average

CATEGORY BENCH

Category-specific benchmark — compares each product's price against the average price within the Furniture category specifically.

SELECT *
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category = 'Furniture'
);

Products Never Ordered (NOT IN Subquery)

DEAD STOCK

Identify products that have never appeared in any order — useful for detecting dead inventory or catalog cleanup.

SELECT p.*
FROM products p
LEFT JOIN order_items oi
  ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

• AGGREGATION — GROUP BY • HAVING • COUNT • AVG • SUM

Department Average Salary — HAVING Filter

GROUP + HAVING

Group by department and filter only those where average salary exceeds 50,000 — HAVING operates post-aggregation unlike WHERE.

SELECT department,
       AVG(salary) AS avg_salary
FROM staff
GROUP BY department
HAVING AVG(salary) > 50000;

Daily Revenue + Order Count

TIME SERIES

Aggregate daily order volume and revenue — the foundation for time-series trend analysis and Tableau dashboard data feeds.

SELECT DATE(o.order_date)        AS day,
       COUNT(DISTINCT o.order_id) AS total_orders,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
GROUP BY DATE(o.order_date);

Orders by Status Count

STATUS DIST

Distribution of order statuses — Delivered, Shipped, Pending, Cancelled. Useful KPI card data for operations dashboards.

SELECT status,
       COUNT(*) AS count_orders
FROM orders
GROUP BY status;

Teachers: Min / Max / Avg Experience

DESCRIPTIVE STATS

Single-row descriptive statistics across the teacher table — min, max, and average years of experience in one query.

SELECT MIN(years_of_experience) AS min_exp,
       MAX(years_of_experience) AS max_exp,
       AVG(years_of_experience) AS avg_exp
FROM university_teacher;

Category Stock — HAVING Total > 200

INVENTORY

Find product categories with combined stock over 200 units — filters post-aggregation using HAVING to focus on well-stocked categories.

SELECT category,
       SUM(stock) AS total_stock
FROM products
GROUP BY category
HAVING SUM(stock) > 200;

Salary Gap — MAX minus MIN

COMPENSATION

Calculates the pay disparity within the teacher pool — the difference between highest and lowest salary in a single expression.

SELECT MAX(salary) - MIN(salary)
       AS salary_gap
FROM university_teacher;

• REVENUE ANALYTICS — Business-grade queries from the E-Commerce database

Top 3 Revenue Products

HIGH VALUE

Revenue per product — sorted descending, limited to Top 3. Direct input for "Top Performing SKUs" KPI card in a dashboard.

SELECT p.product_name,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
  ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 3;

Top Spending Customer

LTV

Identify highest-value customer by total spend — three-table JOIN aggregated and ordered to return a single top spender.

SELECT c.full_name,
       SUM(oi.quantity * oi.unit_price) AS total_spent
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.full_name
ORDER BY total_spent DESC
LIMIT 1;

Revenue by Product Category

CATEGORY P&L

Total revenue grouped by category — the backbone of a category performance chart in a retail intelligence dashboard.

SELECT p.category,
       SUM(oi.quantity * oi.unit_price)
         AS category_revenue
FROM products p
JOIN order_items oi
  ON p.product_id = oi.product_id
GROUP BY p.category;

Total Successful Payment Revenue

FINANCE KPI

Sum of all confirmed (Success status) payments — used as the primary revenue KPI card value in finance dashboards.

SELECT SUM(amount) AS total_success_amount
FROM payments
WHERE status = 'Success';

Average Order Value (AOV)

E-COMMERCE KPI

Average transaction value for successful payments — AOV is a core e-commerce metric for pricing and promotional strategy.

SELECT AVG(amount) AS avg_order_value
FROM payments
WHERE status = 'Success';

Orders by City (Geographic Distribution)

GEO ANALYTICS

Order volume by shipping city — geographic distribution data for a map chart or heat map in a logistics dashboard.

SELECT shipping_city,
       COUNT(*) AS total_orders
FROM orders
GROUP BY shipping_city
ORDER BY total_orders DESC;

• SCHEMA DESIGN — Databases designed, built from scratch, and published on Kaggle ↗

DATABASE 01

E-Commerce Database — ecommerce_db

6-table relational schema with FK constraints: customers → orders → order_items → products, payments

📦 orders

order_id PKINT

customer_id FKINT

order_dateDATETIME

statusVARCHAR

shipping_cityVARCHAR

🛒 order_items

order_item_id PKINT

order_id FKINT

product_id FKINT

quantityINT

unit_priceDECIMAL

💳 payments

payment_id PKINT

order_id FKINT

amountDECIMAL

methodVARCHAR

statusVARCHAR

🏷️ products

product_id PKINT

product_nameVARCHAR

categoryVARCHAR

unit_priceDECIMAL

stock_quantityINT

DATABASE 02

University Database — devbhoomi_uttarakhand_university

20-field tables modelling real university operations — students, teachers, staff with 20 queries each

🎓 university_student

student_id PKINT

programVARCHAR

departmentVARCHAR

cgpaDECIMAL(3,2)

enrollment_statusVARCHAR

👨‍🏫 university_teacher

teacher_id PKINT

designationVARCHAR

specializationVARCHAR

salaryDECIMAL(12,2)

years_of_experienceINT

👥 university_staff

staff_id PKINT

positionVARCHAR

salaryDECIMAL(12,2)

shiftVARCHAR

statusVARCHAR

• ALL DATABASES BUILT

ecommerce_db

6 tables · 30 queries

devbhoomi_university

3 tables · 20 queries

SkyMartDB

2 tables · 27 queries

salariesdb

1 table · Window Fns

SalesDB

1 table · RANK focus

SQL QUERY LAB

E-Commerce Database — ecommerce_db

University Database — devbhoomi_uttarakhand_university

All SQL Files — Open & Verified