Forem: Gladwell Mugambi

Understanding the Similarities Between SQL Stored Procedures and Python Functions

Gladwell Mugambi — Wed, 03 Sep 2025 11:33:46 +0000

In the world of programming and data management, SQL and Python often serve different purposes. SQL is the go-to language for managing and querying data in relational databases, while Python is a powerful, general-purpose programming language used for everything from web development to data science.

Defining the Terms

Stored Procedure
A stored procedure is a compiled set of SQL statements stored within the database itself. It can be executed on demand and is typically used to perform operations like:Fetching or updating records, performing calculations, enforcing business rules, automating repetitive tasks.

Stored procedures help centralize logic in the database, which can improve performance, maintainability, and security.
Example:
CREATE PROCEDURE GetUserById (@UserId INT) AS BEGIN SELECT * FROM Users WHERE Id = @UserId; END

Python Function

A Python function is a block of reusable code that performs a specific task. It allows developers to write code once and use it multiple times without repetition.

Python functions are used for: Performing calculations, automating repetitive tasks, interacting with APIs or databases, organizing code into modular components.
Example:
def get_user_by_id(user_id): return db.query("SELECT * FROM Users WHERE Id = ?", (user_id,))

Simmilarities

Encapsulation of Logic
They both encapsulate a set of operations into a single, named unit. This abstraction hides the internal implementation details and simplifies the overall code structure. For example, a stored procedure GetCustomerOrders encapsulates all the necessary SQL joins and filtering logic, so a developer only needs to call the procedure by its name. Similarly, a Python function calculate_payroll hides the complexities of tax calculations and deductions.

Reusability

Stored Procedure: Can be called multiple times from different parts of a database or application.
Python Function: Can be called multiple times within a script or other functions.

Parameters

Both can accept input parameters to make them dynamic and flexible. A stored procedure can take a customer ID as a parameter to retrieve specific customer data, just as a Python function can take arguments like (length, width) to calculate the area of a rectangle.

Modularity and Maintainability

Both help in breaking down complex logic into manageable, modular units. Changes can be made in one place without affecting the rest of the code or system.

Execution Flow Control

Stored Procedure: Can use control-of-flow constructs like IF, WHILE, BEGIN...END.
Python Function: Uses if, while, for, etc.

Error Handling

Stored Procedure: Uses TRY...CATCH (in T-SQL, for example).
Python Function: Uses try...except.

Conclusion

While SQL stored procedures and Python functions operate in different ecosystems, they share common design goals: modularity, reusability, flexibility, and robustness. Recognizing these similarities allows developers to apply consistent best practices across different parts of a tech stack, ultimately leading to cleaner and more maintainable systems.

Choosing the Right SQL Tool: Comparing Sub-queries, CTEs, and Stored Procedures

Gladwell Mugambi — Tue, 02 Sep 2025 11:28:50 +0000

Developers often face a common challenge when writing SQL: choosing the right tool for the job. While the world of SQL offers many options, understanding the core differences between subqueries, Common Table Expressions (CTEs), and stored procedures is key to writing clean, efficient code.

Subqueries

A subquery is a query nested inside another query. They are executed first and their result is then used by the outer query. They are simple to use for one-off tasks but can become hard to read and manage in complex scenarios.

Scope: The subquery is only visible to the outer query that contains it. It cannot be referenced by other parts of the script.

Reusability: Very low. A subquery's logic must be rewritten every time it's needed.

Best Use Case: Simple, single-use queries where the result of an inner query is needed for a single value or list in the outer query.

Example; finding all employees whose salary is above the average.
SELECT first_name, last_name, salary FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

The inner query finds the average salary, and the main query uses that single value to find the right employees. While effective for simple tasks, if you start nesting them deeper, your code can become very difficult to read and manage.

Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined within a single SQL statement. They are introduced by the WITH keyword and help to break down complex queries into logical, readable parts.

Scope: A CTE is only accessible within the query that defines it.

Reusability: A single CTE can be referenced multiple times within the same query, but not by other queries.

Best Use Case: Improving the readability of complex, multi-step queries, or for recursive queries (where a query references itself). CTEs are often used as an alternative to complex subqueries or temporary tables

Example: The same query, rewritten with a CTE.
Here, we define a CTE named AvgSalary to hold the average salary.
WITH AvgSalary AS ( SELECT AVG(salary) AS avg_sal FROM Employees ) SELECT e.first_name, e.last_name, e.salary FROM Employees e, AvgSalary a WHERE e.salary > a.avg_sal;

When to use it: When you have a complex query that is difficult to read. CTEs are excellent for improving readability and are a go-to for recursive queries.

The downside: CTEs are temporary and are scoped to a single query. You cannot reference them from a different query or script.

Stored Procedures

A stored procedure is a pre-compiled set of one or more SQL statements stored on the database server. They are like functions in a programming language, accepting parameters and executing complex business logic.

Scope: Stored procedures are objects in the database schema and can be called from any application or query that has permission.

Reusability: High. They can be executed multiple times from anywhere in the database or an application, reducing code duplication.

Best Use Case: Encapsulating complex business logic, performing repetitive tasks, or improving performance by reducing network traffic and using pre-compiled execution plans.

Example: Creating a stored procedure to get employee details by department.

First, you create the procedure.

CREATE PROCEDURE GetEmployeesByDepartment @DepartmentName VARCHAR(50) AS BEGIN SELECT first_name, last_name, salary FROM Employees WHERE department = @DepartmentName; END;

Then, you can execute it with a simple command, eliminating the need to write the query every time.

EXEC GetEmployeesByDepartment 'Sales';

When to use it: For any task that needs to be performed over and over again, for business logic that should be centralized, or for improving performance and security.

The upside: Stored procedures are highly reusable, reduce network traffic (only the EXEC command is sent), and can improve performance because the database creates an optimized execution plan the first time it runs.

Conclusion

The best choice depends on the job at hand. For a quick, simple solution within a single query, a subquery is a good choice. If your query is getting complicated and you want to improve its readability, use a CTE to break it down. For any task that needs to be repeated, is critical to your business logic, or requires top performance, a stored procedure is the ideal solution. Mastering all 3 helps in writing better SQL codes.

Kenya Crops Data: A Dashboard-Driven Analysis of Agricultural Performance

Gladwell Mugambi — Thu, 28 Aug 2025 05:10:46 +0000

As a cornerstone of Kenya’s economy, agriculture plays a crucial role in ensuring food security, supporting livelihoods, and contributing to revenue. The Kenya Crops Data Dashboard aggregates information on cultivated land, yields, revenue, and profitability across counties, offering an evidence-based view of the sector's performance.

These metrics highlight the sector’s scale and efficiency:

Total Land Area Cultivated: 4.93K acres

Total Yield: 1.23M units

Total Revenue: KSh 1.19 billion

Total Profit: KSh 1.10 billion

The figures show that agriculture is not only a large-scale activity but also very profitable, with most revenue translating directly into profit.

Land and Yields by County

This chart compares the land size (acres) to the crops yield.

Highest Planted Areas- Nairobi, Nyeri, and Kisumu counties show the largest cultivated acreage, all above 500 acres.
Nairobi and Nyeri show both high acreage and relatively high yields.
Kisumu and Kericho, while having large planted areas, record lower yields per acre, suggesting inefficiencies.
Counties like Machakos and Nakuru show moderate acreage but competitive yields, implying higher productivity per unit area. According to this data, high acreage does not guarantee high yields.

Revenue by County

Revenue distribution is heavily concentrated in a few counties.
Nyeri leads with KSh 162.05M, followed by Nairobi KSh 132.89M and Nakuru KSh 112.18M.
Mid-tier performers include Eldoret, Kisumu, and Meru, while others lag.

Such disparities suggest that high-performing counties benefit from better infrastructure, market access, and higher-value crops.

Farmer Distribution by Crop Type

The farmer count by crop type shows a balanced distribution:

Coffee and Potatoes have the largest farmer participation leading by 11.36% each.
Other crops such as; Tea, Cassava, Rice, Tomatoes, and Wheat range between 8–11%.

No single crop dominates farmer participation, indicating a diversified farming base that spreads risk but may limit economies of scale.

Revenue and Profit by Crop Type

Across all crops, profits closely match revenues, showing very high efficiency.

Rice is the most profitable with KSH 135M, followed by Sorghum and Potatoes.
Cassava, Tea, Coffee, and Wheat form a strong middle group, each near KES 100M, while Beans, Maize, and Tomatoes lag behind with less than KES 80M
Tomatoes perform the weakest, which is likely due to perishability and unstable prices.

Conclusion

The Kenya Crops Data dashboard offers valuable insights into how agriculture is performing across different counties and crop types. The findings show that productivity depends more on efficiency than on the size of land cultivated. It also reveals that regional differences call for focused support and interventions. In addition, staple food crops are proving more profitable than traditional cash crops, showing a shift in agricultural priorities influenced by rising domestic demand.

Evaluating Excel for Predictive Analytics and Data-Driven Business Decision Making

Gladwell Mugambi — Tue, 12 Aug 2025 03:16:13 +0000

Excel is one of the most widely used tools in business analytics, including predictive analysis and data-driven decision-making. Organizations rely heavily on excel analytical tools to forecast trends, evaluate performance, and make informed decisions. However, while it offers some significant strengths, it also has limitations.

Excel Strengths in Predictive Analysis

Strong Visualization Capabilities
Excel makes it simple to create charts, graphs, and dashboards to represent data trends and forecasts. Tools like PivotTables and slicers offer dynamic ways to explore data. They are excellent for visualizing trends, forecasts, and the results of a predictive model.

Data Organization and Cleaning
It's an effective tool for organizing and cleaning small- to medium-sized datasets before they are used in a predictive model. Its features for sorting, filtering, and removing duplicates are invaluable for data preparation.

User-Friendly and Widely Adopted
Excel is commonly used across various industries and is easy to learn, even for users without technical backgrounds. It doesn’t require coding knowledge for basic predictive tasks.

Built-In Analytical Tools
Excel has a variety of built-in functions that support predictive analysis. For example, the FORECAST.ETS function can be used for exponential smoothing. TREND and GROWTH are useful for linear and exponential trend forecasting. The Solver add-in can be used for optimization problems.

Integration Capabilities
Excel integrates with Power Query, Power Pivot, and external databases.
It can connect with tools like Python, R, and Power BI for advanced analytics.

Limitations of Excel in Predictive Analysis

Scalability Issues
Excel struggles with vast datasets, such as millions of rows, and becomes slow or crashes. It is therefore not ideal for enterprise-level or big data analytics.

Data Accuracy and Risk of Errors
Manual data entry and formula use can introduce mistakes. Formulas can also be complex and difficult to audit. A single error in a formula can invalidate the entire model without a clear warning.

Limited Statistical Capabilities
Excel's statistical functions are not as extensive as those found in statistical programming languages like Python or software like SPSS or SAS. It lacks the advanced algorithms needed for complex tasks like machine learning, neural networks, or deep learning.

Lack of Automation and Reproducibility Predictive models built in Excel are often manual and difficult to automate. This makes it challenging to reproduce results or update the model with new data without significant manual effort.

The Role of Excel in Data-Driven Business Decisions

Excel plays a critical role in making data-driven business decisions, often serving as the first step in the analytics process. For many small- and medium-sized businesses, it is the primary tool for data analysis and visualization.

Financial Forecasting and Budgeting
Businesses commonly use Excel to create budgets, forecast sales, and model different financial scenarios. This allows them to make informed decisions about resource allocation and strategic planning.

Scenario and "What-If" Analysis
The spreadsheet format is ideal for running "what-if" scenarios. By changing a few variables in a model, a business can see the potential impact on outcomes like revenue or profit. This helps in risk assessment and strategic decision-making.

Performance Monitoring
Excel spreadsheets are often used to track key performance indicators (KPIs) and monitor business performance over time. By visualizing trends and comparing actual performance against targets, managers can identify issues and opportunities.

Data Preparation for Advanced Analysis
Excel is a valuable tool for preparing data before importing it into more advanced analytical tools. It's used for initial data exploration, cleaning, and transformation, bridging the gap between raw data and predictive models.

Conclusion

Excel is excellent for basic to intermediate predictive analysis and decision-making. However, as the complexity, size, and scope of analysis grow, organizations often need to supplement Excel with more powerful tools such as Python, R, SQL, or business intelligence platforms like Power BI or Tableau.