Understanding the Differences Between Subqueries, CTEs, and Stored Procedures

Patrick Kinoti — Mon, 08 Sep 2025 13:22:13 +0000

Introduction

Subqueries, CTEs, and stored procedures are three powerful tools that shape how we write and optimize SQL. At first glance they may seem alike, but each serves a unique purpose, and understanding their distinctions can help you choose the right approach for your needs, i.e Subqueries help nest logic, CTEs improve clarity and organization, while stored procedures package logic for reuse and efficiency.

Subqueries

A subquery is a query nested inside another SQL query. It is enclosed in parentheses and provides a result that the main query can use—either as a value, a set of rows, or as part of a condition.

SELECT first_name, last_name
FROM employees e
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id)

Outer query: selects first_name and last_name from employees table (e).
Condition: keeps only employees whose salary is greater than the result of the subquery.
Subquery: calculates the average salary of employees in the same department (department_id = e.department_id).
Correlation: the subquery depends on the outer query (e.department_id).

CTE (Common Table Expression)

A CTE is a temporary, named result set—like an inline view—that exists only during query execution, defined using the WITH clause. They enhance readability by breaking up complex SQL, eliminate repetitive subqueries, and support recursive data retrieval. Used for Structuring complex queries, handling hierarchical or recurring logic, and avoiding duplication

with ranking_customers as (
    select orders.customer_id,SUM(orders.quantity) as total_quantity,
    row_number()over(order by sum(orders.quantity)desc  ) as customer_rank
    from orders 
    group by orders.customer_id)
select customers.first_name,customers.last_name,ranking_customers.total_quantity, ranking_customers.customer_rank
from customers 
join ranking_customers on customers.customer_id=ranking_customers.customer_id   
order by ranking_customers.customer_rank;

CTE (ranking_customers)

Groups orders by customer_id. -_ Calculates SUM(quantity) = total items ordered per customer._
Assigns rank using ROW_NUMBER() (highest quantity = rank 1).

Main query

Joins customers table with the CTE on customer_id.
Selects customer’s first & last name, total quantity, and rank.
Orders the result by customer_rank.

Stored Procedure

A stored procedure is a precompiled set of SQL statements stored on the database server, executed by calling its name. It can include logic, parameters, and multiple queries, offering reusability and performance benefits for repeated tasks, but it’s more complex to maintain.

CREATE PROCEDURE GetCropYieldByCounty(IN crop_name VARCHAR(50))
BEGIN
    SELECT county, SUM(yield_tons) AS total_yield
    FROM crops
    WHERE crop = crop_name
    GROUP BY county
    ORDER BY total_yield DESC;
ENd;

Procedure name: GetCropYieldByCounty
Input: crop_name (the crop to check)
Action: Selects each county and calculates SUM(yield_tons) for that crop
Groups results by county and sorts them in descending order of yield
Example: CALL GetCropYieldByCounty('Maize'); → shows maize yields per county

Comparison of Subqueries, CTEs, and Stored Procedures

Subquery	CTE	Stored Procedure
Inline query inside another query, used once for filtering or aggregation.	Temporary named query using `WITH`, simplifies and can be reused within one query.	Saved SQL program stored in the DB, reusable, handles complex logic.
Example: `SELECT * FROM crops WHERE yield_tons > (SELECT AVG(yield_tons) FROM crops);`	Example: `WITH cte AS (SELECT * FROM crops) SELECT * FROM cte;`	Example: `CALL GetCropYieldByCounty('Maize');`

EXCEL'S ROLE IN PREDICTIVE ANALYSIS AND BUSINESS DECISIONS.

Patrick Kinoti — Mon, 11 Aug 2025 18:01:45 +0000

INTRODUCTION

In today’s data-driven world, making informed business decisions is essential for growth and sustainability. While many advanced analytics platforms exist, Microsoft Excel remains one of the most widely used tools for predictive analysis and decision-making — thanks to its accessibility, flexibility, and built-in analytical capabilities.

WHAT IS PREDICTIVE ANALYSIS?

Predictive analysis is the use of historical data, statistical algorithms, and machine learning techniques to forecast future outcomes. It enables businesses to:

Anticipate market trends
Understand customer behavior
Optimize operations
Reduce risks

WHY IS EXCEL STILL RELEVANT?

Despite the rise of complex data tools like Python, R, and Power BI, Excel plays a foundational role in many organizations due to:

Ease of use – No coding required
Familiar interface – Widely adopted by professionals
Built-in functions – For regression, forecasting, and trend analysis
Compatibility – Integrates easily with other software and data sources

EXCEL TOOL FOR PREDICTING ANALYSIS

Forecast Sheet - Predicts future values based on past data.
Trendlines - Shows data trends and future projections in charts.
Regression (Data Analysis ToolPak) - Analyzes relationships between variables.
What-If Analysis - Tests different scenarios and outcomes.
Goal Seek - Finds the input needed to reach a target result.
Solver - Optimizes results based on conditions (e.g., maximize profit).
PivotTables - Summarizes and analyzes large data sets.

REAL-WORLD BUSINESS APLICATION

Use Case Excel Predictive Feature Used

Sales Forecasting - Forecast Sheet, Trendlines
Budget Planning - What-If Analysis, Goal Seek
Customer Churn Prediction - Regression Analysis, PivotTables
Inventory Management - Data Tables, Forecasting
Pricing Strategy - Solver, Scenario Manager

ANDVANTAGES OF USING EXCEL FOR PREDICTIVE ANALYSIS

Low barrier to entry (no specialized software needed)
Widely available across organizations
Great for prototyping models before moving to more complex tools
Easy to visualize data and share insights

LIMITATION

While Excel is powerful, it's not a substitute for full-scale data science tools in complex scenarios. Its limitations include:

Limited processing power for big data
No built-in machine learning models
Risk of manual errors

FINAL THOUGHT

Excel remains an essential tool in the business analytics toolkit — especially for small to mid-sized businesses and early-stage analysts. With the right use of its predictive and analytical features, Excel can significantly aid in data-driven decision-making.

🌟 Excel may not be a crystal ball, but in the hands of a skilled user, it's close.

DEV Community: Patrick Kinoti