Learning Objectives
By the end of this lesson, learners should be able to:
- Apply SQL to real-world business questions.
- Transform raw transactional data into analytical information.
- Build business metrics using SQL.
- Analyze sales, customers, products and financial performance.
- Perform time-based analysis.
- Identify trends and anomalies.
- Prepare SQL outputs for dashboards and management reports.
- Validate analytical results.
- Recognize limitations in SQL-based reporting.
- Communicate SQL findings effectively to decision-makers.
1. Introduction
SQL becomes particularly valuable when it is used to answer practical business questions.
A business analyst may be asked:
- Which products are most profitable?
- Which customers are declining?
- Which branches are underperforming?
- What is the monthly revenue trend?
- Which customers have become inactive?
- Which products are frequently purchased together?
SQL provides the analytical foundation for answering these questions.
2. From Data to Business Insight
A useful analytical process can be represented as:
Raw Data → SQL Transformation → Metric → Analysis → Insight → Decision
For example:
Transactions → SQL aggregation → Monthly revenue → Trend analysis → Revenue decline identified → Management investigates causes.
The SQL query is therefore only one part of the analytical process.
3. Business Metrics
SQL can be used to calculate metrics such as:
- Total revenue.
- Average order value.
- Number of customers.
- Customer retention.
- Sales growth.
- Units sold.
- Gross margin.
- Conversion rate.
- Customer purchase frequency.
The metric must first have a clearly defined business meaning.
4. Revenue Analysis
A basic revenue calculation might be:
SELECT SUM(Sales_Amount) AS Total_Revenue
FROM Sales;
However, analysts should confirm:
- Whether cancelled orders are included.
- Whether refunds are deducted.
- Whether taxes are included.
- Whether discounts are already reflected.
- What period is being measured.
A technically correct calculation can still represent the wrong business definition.
5. Monthly Revenue Analysis
An analyst may group transactions by month to identify trends.
Conceptually:
SELECT
YEAR(Order_Date) AS Sales_Year,
MONTH(Order_Date) AS Sales_Month,
SUM(Amount) AS Revenue
FROM Orders
GROUP BY YEAR(Order_Date), MONTH(Order_Date);
The exact date syntax may vary by database system.
6. Growth Analysis
Revenue growth can be calculated by comparing one period with another.
For example:
Current Revenue = KSh 12 million
Previous Revenue = KSh 10 million
Growth:
Growth Rate=Previous RevenueCurrent Revenue−Previous Revenue×100
This produces a 20% increase.
SQL can prepare the values, while the analyst interprets their business significance.
7. Customer Analysis
SQL can identify:
- High-value customers.
- Inactive customers.
- New customers.
- Repeat customers.
- Customers by region.
- Customers by product category.
For example:
SELECT Customer_ID,
COUNT(Order_ID) AS Number_of_Orders,
SUM(Amount) AS Total_Spend
FROM Orders
GROUP BY Customer_ID;
This creates a basic customer activity profile.
8. Customer Segmentation
SQL can classify customers using business rules.
For example:
CASE
WHEN Total_Spend >= 500000 THEN ‘Premium’
WHEN Total_Spend >= 100000 THEN ‘Standard’
ELSE ‘Low Value’
END
Such classifications can support:
- Marketing.
- Customer retention.
- Credit management.
- Sales prioritization.
9. Recency, Frequency and Monetary Analysis
A common customer analytics approach examines:
Recency
How recently did the customer purchase?
Frequency
How often does the customer purchase?
Monetary Value
How much does the customer spend?
SQL can be used to calculate these dimensions and prepare customers for segmentation.
10. Product Analysis
SQL can help determine:
- Best-selling products.
- Highest-revenue products.
- Slow-moving products.
- Product category performance.
- Average selling price.
- Product contribution to total sales.
Example:
SELECT Product_ID,
SUM(Quantity) AS Units_Sold,
SUM(Sales_Amount) AS Revenue
FROM Sales
GROUP BY Product_ID;
11. Inventory Analytics
When inventory data is available, SQL can help identify:
- Stock levels.
- Stock turnover.
- Slow-moving products.
- Stock-out events.
- Reorder requirements.
The analysis can support supply-chain decisions.
12. Profitability Analysis
Revenue alone does not measure profitability.
A product generating high revenue may have:
- High production costs.
- High distribution costs.
- Large discounts.
- High return rates.
A simplified gross profit calculation may be:
SELECT Product_ID,
SUM(Sales_Amount – Cost_Amount) AS Gross_Profit
FROM Sales
GROUP BY Product_ID;
This allows analysts to distinguish sales performance from financial contribution.
13. Regional Analysis
SQL can compare performance across geographic areas.
SELECT Region,
SUM(Sales_Amount) AS Revenue
FROM Sales
GROUP BY Region
ORDER BY Revenue DESC;
Management could use this to identify high-performing and underperforming regions.
However, revenue differences should be interpreted in context.
A region with more branches or customers may naturally generate more revenue.
14. Performance per Unit
Absolute totals can sometimes produce misleading comparisons.
For example:
|
Region |
Revenue |
Branches |
|
Nairobi |
20M |
20 |
|
Kisumu |
10M |
5 |
Nairobi has higher total revenue, but Kisumu generates more revenue per branch.
Therefore, SQL can be used to calculate normalized performance measures.
15. Time-Based Analysis
Time is one of the most important dimensions in business analytics.
Analysts may examine:
- Daily performance.
- Weekly performance.
- Monthly performance.
- Quarterly performance.
- Annual performance.
- Year-over-year changes.
The choice depends on the business question.
16. Year-over-Year Analysis
Year-over-year analysis compares the same period across different years.
For example:
January 2026 versus January 2025.
This can help control for seasonal effects better than simply comparing adjacent months.
17. Seasonal Analysis
Some businesses experience predictable seasonal patterns.
Examples:
- Retail sales increasing during holidays.
- Tourism demand changing by season.
- School-related businesses following academic calendars.
An apparent increase in sales may therefore reflect seasonality rather than permanent growth.
18. Anomaly Detection
SQL can help identify unusual observations.
Examples:
- Unusually large transactions.
- Sudden revenue declines.
- Repeated failed payments.
- Unusual refund activity.
- Customers with abnormal transaction frequency.
SQL may identify candidates for investigation, but additional analytical methods may be required to establish whether an anomaly is genuinely problematic.
19. Data Quality Checks
SQL can also be used to identify data problems.
Examples include:
Missing Values
SELECT *
FROM Customers
WHERE Customer_Email IS NULL;
Duplicate Identifiers
SELECT Customer_ID, COUNT(*)
FROM Customers
GROUP BY Customer_ID
HAVING COUNT(*) > 1;
Invalid Values
Analysts can search for:
- Negative quantities where they should be impossible.
- Future transaction dates.
- Invalid categories.
- Unexpected currency values.
20. SQL and Executive Reporting
SQL often acts as the data preparation layer behind management reports.
A typical process might be:
Database → SQL Query → Analytical Dataset → Dashboard → Executive Decision
Tools such as business intelligence platforms may then visualize the resulting dataset.
21. KPI Reporting
A Key Performance Indicator (KPI) measures performance against an important business objective.
Examples:
- Revenue growth.
- Gross margin.
- Customer retention.
- Average order value.
- Customer acquisition cost.
SQL can calculate the underlying values, while dashboards communicate them.
22. Dashboard Design
A dashboard should not simply display every available metric.
Effective dashboards prioritize:
- Relevant KPIs.
- Trends.
- Comparisons.
- Exceptions.
- Targets.
- Actionable insights.
A dashboard containing dozens of unrelated metrics may provide less value than a smaller set of carefully selected indicators.
23. SQL Views
A view is a virtual representation of a query result.
For example, an organization might create a view containing standardized sales information for analysts.
Views can:
- Simplify recurring queries.
- Improve consistency.
- Restrict exposure to unnecessary columns.
- Provide a reusable analytical interface.
24. SQL for Data Preparation
SQL can prepare datasets through:
- Filtering.
- Joining.
- Aggregating.
- Calculating fields.
- Categorizing records.
- Handling missing information.
This can reduce the amount of manual spreadsheet manipulation required.
25. SQL and Business Definitions
Different departments may define the same metric differently.
For example:
Finance revenue
may differ from:
Sales revenue
because of:
- Returns.
- Taxes.
- Discounts.
- Recognition rules.
- Timing differences.
Therefore, SQL should implement an agreed business definition rather than allowing every analyst to independently define a KPI.
26. Data Validation
Before publishing a report, analysts should validate:
- Record counts.
- Totals.
- Date ranges.
- Duplicate records.
- Missing values.
- Business rules.
Where possible, SQL outputs should be reconciled against trusted financial or operational totals.
27. SQL Reporting and Automation
Recurring reports can often be automated.
For example:
Every Monday, retrieve the previous week’s sales, calculate KPIs and refresh a dashboard.
Automation reduces:
- Manual effort.
- Repetitive work.
- Human error.
However, automated reports still require monitoring because source systems and business rules can change.
28. Limitations of SQL
SQL is powerful, but it does not replace all analytical methods.
SQL is particularly strong for:
- Data retrieval.
- Transformation.
- Aggregation.
- Filtering.
- Structured-data analysis.
Other tools may be better suited for:
- Advanced statistical modeling.
- Machine learning.
- Complex visualization.
- Natural-language analysis.
- Unstructured data analysis.
A strong business analyst knows when SQL is sufficient and when another analytical tool is needed.
29. Case Study: Retail Performance
A retailer wants to answer five questions:
- Which regions generate the most revenue?
- Which products contribute the most profit?
- Which customers are high value?
- Which months show declining sales?
- Which transactions appear unusual?
SQL can produce the datasets required for each question.
The analyst then interprets the findings in the context of:
- Market conditions.
- Pricing.
- Customer behavior.
- Operational constraints.
- Management objectives.
30. From SQL Results to Decisions
Suppose SQL shows:
Revenue declined by 15% in one region.
The analyst should not immediately conclude:
“The regional manager is underperforming.”
Possible explanations include:
- Stock shortages.
- Reduced customer traffic.
- Competitor activity.
- Pricing changes.
- Seasonal effects.
- Data-quality problems.
SQL identifies the pattern; business analysis investigates the cause.
Lesson Summary
SQL-based business analytics involves more than writing queries.
The analyst must:
- Understand the business question.
- Understand the data structure.
- Define the metric.
- Write appropriate SQL.
- Validate the result.
- Interpret the finding.
- Communicate the insight.
- Support a business decision.
SQL is therefore a bridge between organizational data and evidence-based decision-making.