Learning Objectives
By the end of this lesson, learners should be able to:
- Explain the purpose of SQL joins.
- Use inner, left, right and full joins appropriately.
- Distinguish between filtering before and after aggregation.
- Use aggregate functions effectively.
- Apply GROUP BY and HAVING.
- Construct subqueries for analytical problems.
- Use Common Table Expressions where supported.
- Work with conditional logic in SQL.
- Identify and prevent duplicate counting.
- Evaluate complex SQL results in a business context.
1. Introduction to Advanced SQL
Basic SQL allows analysts to retrieve individual records. However, business analysis frequently requires more complex operations.
For example:
Which customer segment generated the highest revenue in the last financial year?
Answering such a question may require:
- Multiple tables.
- Joins.
- Filtering.
- Calculations.
- Aggregation.
- Grouping.
- Ranking or comparison.
Advanced SQL provides the tools necessary for these tasks.
2. SQL Joins
A join combines information from two or more tables using related fields.
Suppose we have:
Customers
|
Customer_ID |
Customer_Name |
|
C001 |
Amina |
|
C002 |
Brian |
Orders
|
Order_ID |
Customer_ID |
Amount |
|
O001 |
C001 |
25,000 |
|
O002 |
C002 |
18,000 |
A join can associate each order with its customer.
3. INNER JOIN
An INNER JOIN returns records for which matching values exist in both tables.
SELECT c.Customer_Name, o.Order_ID, o.Amount
FROM Customers c
INNER JOIN Orders o
ON c.Customer_ID = o.Customer_ID;
Customers without matching orders will not appear.
4. LEFT JOIN
A LEFT JOIN returns all records from the left table and matching records from the right table.
SELECT c.Customer_Name, o.Order_ID
FROM Customers c
LEFT JOIN Orders o
ON c.Customer_ID = o.Customer_ID;
This is particularly useful when the business question concerns all customers, including those who have not placed orders.
5. RIGHT JOIN
A RIGHT JOIN returns all records from the right table and matching records from the left table.
It can be useful, although many analysts prefer restructuring the query and using a LEFT JOIN for readability.
6. FULL OUTER JOIN
A FULL OUTER JOIN returns matching and non-matching records from both tables where supported by the database system.
This can be useful when reconciling two datasets.
For example:
Compare customers appearing in the CRM system with customers appearing in the billing system.
7. Join Cardinality
Join cardinality determines how many rows may result from combining tables.
A one-to-many relationship can cause one row from one table to match multiple rows in another.
This is extremely important in financial and operational reporting.
Example
One customer:
C001
has five orders.
Joining the tables produces five rows associated with that customer.
Counting joined rows as customers would therefore overstate the customer count.
8. Aggregate Functions
Aggregate functions summarize multiple rows.
Common functions include:
- COUNT()
- SUM()
- AVG()
- MIN()
- MAX()
Example:
SELECT SUM(Amount) AS Total_Sales
FROM Orders;
This produces total sales.
9. COUNT
COUNT() can be used to count records or non-null values depending on how it is applied.
SELECT COUNT(*) AS Number_of_Orders
FROM Orders;
This counts rows.
By contrast:
COUNT(Customer_ID)
counts non-null Customer_ID values.
10. SUM
SUM() calculates the total of a numerical expression.
SELECT SUM(Amount) AS Total_Revenue
FROM Orders;
This is commonly used for:
- Revenue.
- Costs.
- Units sold.
- Discounts.
- Payments.
11. AVG
AVG() calculates an arithmetic average.
SELECT AVG(Amount) AS Average_Order_Value
FROM Orders;
The analyst should consider how NULL values and unusual outliers affect the resulting average.
12. MIN and MAX
These functions identify the smallest and largest values.
SELECT MIN(Amount), MAX(Amount)
FROM Orders;
They can help identify:
- Lowest transaction.
- Highest transaction.
- Earliest date.
- Latest date.
13. GROUP BY
GROUP BY divides records into groups before aggregate calculations are performed.
Example:
SELECT Region, SUM(Amount) AS Total_Sales
FROM Orders
GROUP BY Region;
This calculates sales for each region.
14. GROUP BY Multiple Fields
Grouping can use multiple dimensions.
SELECT Region, Customer_Type, SUM(Amount) AS Total_Sales
FROM Orders
GROUP BY Region, Customer_Type;
This can produce regional sales by customer segment.
15. HAVING
HAVING filters groups after aggregation.
Example:
SELECT Customer_ID, SUM(Amount) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
HAVING SUM(Amount) > 100000;
This identifies customers whose total purchases exceed KSh 100,000.
16. WHERE Versus HAVING
This distinction is essential.
WHERE filters individual records before grouping.
HAVING filters groups after aggregation.
For example:
WHERE Order_Date >= ‘2026-01-01’
filters transactions.
While:
HAVING SUM(Amount) > 100000
filters aggregated customer or regional totals.
17. Conditional Logic
SQL can apply conditional logic using CASE.
Example:
SELECT Customer_ID,
CASE
WHEN Total_Sales >= 500000 THEN ‘High Value’
WHEN Total_Sales >= 100000 THEN ‘Medium Value’
ELSE ‘Low Value’
END AS Customer_Segment
FROM Customer_Summary;
This allows analysts to convert numerical measures into business categories.
18. Subqueries
A subquery is a query embedded inside another query.
Example:
SELECT Customer_ID, Amount
FROM Orders
WHERE Amount > (
SELECT AVG(Amount)
FROM Orders
);
This identifies orders whose value is greater than the overall average.
19. Correlated Subqueries
A correlated subquery references values from the outer query.
These can solve sophisticated analytical problems but may be more difficult to understand and potentially less efficient than alternative approaches.
20. EXISTS
EXISTS checks whether a related record exists.
Example:
SELECT Customer_ID
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.Customer_ID = c.Customer_ID
);
This identifies customers who have at least one order.
21. Common Table Expressions
A Common Table Expression (CTE) can make complex queries easier to structure.
Example:
WITH CustomerSales AS (
SELECT Customer_ID,
SUM(Amount) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
)
SELECT *
FROM CustomerSales
WHERE Total_Sales > 100000;
CTEs can improve:
- Readability.
- Query organization.
- Reusability within a statement.
- Analytical reasoning.
22. Window Functions
Window functions perform calculations across related rows while preserving individual rows.
Examples include:
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- LAG()
- LEAD()
For example:
SELECT Customer_ID,
Order_Date,
Amount,
RANK() OVER (
PARTITION BY Customer_ID
ORDER BY Amount DESC
) AS Order_Rank
FROM Orders;
This ranks each customer’s orders by value.
23. Aggregation Versus Window Functions
A normal GROUP BY reduces multiple records into fewer summary rows.
A window function can calculate a summary or ranking while retaining individual records.
This distinction is extremely useful for business analytics.
24. Duplicate Counting
One of the most important risks in SQL analysis is accidental duplication.
Suppose:
- One customer has 20 orders.
- Each order has 4 order-detail records.
Joining Customers → Orders → Order_Details can generate many rows for a single customer.
A simple COUNT(*) may therefore produce a number unrelated to the intended business metric.
The analyst must understand the grain of each table.
25. Grain of Data
The grain describes what one row represents.
For example:
- One row = one customer.
- One row = one order.
- One row = one order line.
- One row = one monthly customer summary.
Before aggregating data, an analyst should establish the grain.
26. Query Performance
Complex SQL queries can become expensive when working with large datasets.
Performance can be influenced by:
- Number of rows.
- Number of joins.
- Filtering.
- Indexes.
- Query structure.
- Functions applied to columns.
- Database architecture.
A business analyst should balance analytical requirements with performance considerations.
27. Indexes
An index can help a database locate records more efficiently.
Indexes may improve query performance, especially for frequently searched or joined fields.
However, indexes can also:
- Consume storage.
- Increase maintenance overhead.
- Slow some write operations.
Therefore, indexing involves trade-offs.
28. Practical Business Example
Management wants to identify customers who generated more than KSh 250,000 in sales during the year.
A suitable analytical structure could be:
SELECT Customer_ID,
SUM(Amount) AS Total_Sales
FROM Orders
WHERE Order_Date >= ‘2026-01-01’
AND Order_Date < ‘2027-01-01’
GROUP BY Customer_ID
HAVING SUM(Amount) > 250000;
The important sequence is:
- Filter the relevant transactions.
- Group them by customer.
- Calculate total sales.
- Filter customers based on their aggregated totals.
Lesson Summary
Advanced SQL allows analysts to work with complex business questions through:
- Joins.
- Aggregation.
- GROUP BY.
- HAVING.
- Subqueries.
- CTEs.
- Conditional expressions.
- Window functions.
- Careful handling of data grain.
The most important principle is:
Understand what each row represents before joining, aggregating or counting data.