Forem: Nicholus Gathirwa

Parametric vs Non-Parametric Tests: A Beginner's Guide

Nicholus Gathirwa — Sun, 09 Nov 2025 10:37:56 +0000

The Big Picture: What Are We Even Testing?

When you're analyzing data and want to test hypotheses, you need to choose the right statistical test. But here's the thing: not all tests are created equal, and not all data fits the same mold.

This is where the distinction between parametric and non-parametric tests becomes crucial. Think of it as choosing between two different toolkits ie one requires your data to meet certain conditions, the other is more flexible.

Keep in mind these central questions:

"Does my data follow a specific distribution?" ie This determines which approach you can use.
"What assumptions am I willing to make about my data?" ie This is the heart of the parametric vs non-parametric choice.

Okay we'll go through parametric tests, then non-parametric then we'll compare the two. We'll also go through when to use which which also includes common misconceptions, real-world application tips and finally wrap things up. Here we go!!!

Parametric Tests: The Assumption-Heavy Powerhouses

Parametric tests are the "traditional" statistical tests you'll encounter first in most statistics courses. They're called "parametric" because they make assumptions about the parameters of the population distribution.

What Makes a Test Parametric?

Parametric tests assume your data follows a specific probability distribution—usually the normal distribution (bell curve). They also make assumptions about population parameters like mean and variance.

Key Assumptions

Parametric tests typically require:

Normality: Data follows a normal distribution (or approximately normal)
Homogeneity of variance: Groups being compared have similar variances
Independence: Observations are independent of each other
Interval or ratio data: Data measured on continuous scales

Common Parametric Tests

T-test: Compares means between two groups
ANOVA (Analysis of Variance): Compares means across three or more groups
Pearson correlation: Measures linear relationship between two continuous variables
Linear regression: Models relationships between variables

Strengths of Parametric Tests

More powerful: When assumptions are met, they're more likely to detect real effects
More precise: Provide narrower confidence intervals
Well-established: Widely understood and accepted
Use all information: Make full use of the actual data values

Limitations of Parametric Tests

When assumptions are violated, parametric tests can give one misleading results. If one's data isn't normally distributed or has outliers, these tests might tell one there's no effect when there really is one (or vice versa).

Non-Parametric Tests: The Distribution-Free Alternatives

Non-parametric tests are sometimes called "distribution-free" tests because they don't assume your data follows any particular distribution. They're the flexible, robust alternatives.

What Makes a Test Non-Parametric?

Non-parametric tests make fewer and weaker assumptions about the data. Instead of working with actual values, they often work with ranks or signs of the data.

Key Characteristics

Non-parametric tests:

No distribution assumptions: Don't require normal distribution
Robust to outliers: Extreme values have less influence
Work with ranks: Often convert data to ranks, losing some information
Flexible data types: Can handle ordinal, interval, and ratio data

Common Non-Parametric Tests (and Their Parametric Equivalents)

Mann-Whitney U test (Wilcoxon rank-sum test): Non-parametric version of independent t-test
Wilcoxon signed-rank test: Non-parametric version of paired t-test
Kruskal-Wallis test: Non-parametric version of one-way ANOVA
Spearman's rank correlation: Non-parametric version of Pearson correlation
Friedman test: Non-parametric version of repeated measures ANOVA
Chi-square test: Tests relationships between categorical variables

Strengths of Non-Parametric Tests

Fewer assumptions: Work even when data isn't normally distributed
Robust: Less affected by outliers and skewed distributions
Versatile: Can handle ordinal data and small sample sizes
Safer choice: When in doubt, they provide valid results

Limitations of Non-Parametric Tests

Less powerful: When parametric assumptions ARE met, non-parametric tests are less likely to detect real effects
Less precise: Generally produce wider confidence intervals
Information loss: Converting to ranks throws away some information about actual values
Interpretation: Sometimes harder to interpret (medians vs means)

Let's compare the two, parametic and non-parametric tests.

1. Assumptions

Parametric: Assumes specific distribution (usually normal), equal variances
Non-parametric: Minimal assumptions about distribution

2. Data Requirements

Parametric: Continuous data (interval or ratio scales)
Non-parametric: Ordinal, interval, or ratio data

3. What They Analyze

Parametric: Work with actual data values (means, variances)
Non-parametric: Often work with ranks or medians

4. Sample Size

Parametric: Generally need larger samples for normality assumption (though Central Limit Theorem helps)
Non-parametric: Better for small sample sizes

5. Power

Parametric: More powerful when assumptions are met (95-100% efficiency)
Non-parametric: Less powerful but more robust (typically 95% efficiency of parametric equivalent)

6. Outliers

Parametric: Sensitive to outliers and extreme values
Non-parametric: Resistant to outliers

The "When in Doubt" Rule

If you're unsure whether parametric assumptions are met, here's a practical approach:

Check your assumptions (normality tests, plots, variance tests)
If in doubt, use non-parametric ie you'll lose a bit of power but won't get invalid results
If sample size is large (n>100), parametric tests are often robust even with mild violations
Consider running both and see if conclusions differ (if they agree, you're on solid ground)

Common Misconceptions

"Non-parametric tests are always safer"

Not quite. If your data meets parametric assumptions, parametric tests are better—they're more powerful and precise.

"You should always check normality before choosing a test"

Yes and no. With large samples (n>100), parametric tests are robust to normality violations due to the Central Limit Theorem. Focus more on outliers and extreme skewness.

"Non-parametric tests don't have assumptions"

Wrong! They have fewer assumptions, but they still assume independence and, for some tests, similar distributions across groups.

"Parametric tests require perfect normality"

False. They require approximate normality, and they're quite forgiving, especially with larger samples.

Real-World Application Tips

1. Check Your Data First

Always visualize your data with histograms, boxplots, and Q-Q plots before choosing a test.

2. Sample Size Matters

Small samples (n<30): Lean toward non-parametric unless you're confident about normality
Large samples (n>100): Parametric tests are usually fine even with mild violations

3. Subject Matter Context

Sometimes the nature of your variable tells you what to expect. Height, weight, and many biological measurements tend toward normality. Reaction times, income, and count data often don't.

4. Report What You Did

Always report which test you used and why. If you chose non-parametric due to assumption violations, mention it.

5. Transformation Option

Sometimes you can transform skewed data (log transformation, square root) to make it more normal, allowing use of parametric tests. But be careful about interpretation!

The Bottom Line

The choice between parametric and non-parametric tests isn't about one being "better" than the other—it's about matching the right tool to your data.

Parametric tests are like precision instruments: powerful and efficient when conditions are right, but give misleading results when assumptions are violated.

Non-parametric tests are like all-weather tools: they work in almost any condition, but they're slightly less efficient when conditions are ideal.

Hope you've understood dear reader and leave a comment if you have any concern.

Bayesians vs Frequentists: A Beginner's Guide

Nicholus Gathirwa — Fri, 07 Nov 2025 23:36:45 +0000

At their core, both approaches want to help us understand uncertainty and make decisions based on data. But they go about it in fundamentally different ways.

Both want to answer the question, "What does probability mean, and how do we use data to make inferences?"

Keep in mind these central questions:

"What does probability represent?" Bayesians and Frequentists answer this differently.
"How do we update our knowledge with new data?" This is where the approaches diverge most clearly.

Frequentist Statistics: The Long-Run Frequency Approach

Frequentist statistics is the traditional approach you'll encounter in most introductory statistics courses.

What Probability Means to Frequentists

To a Frequentist, probability is about long-run frequency. If you flip a coin infinitely many times, the proportion of heads approaches 0.5. That's probability ie what happens in the long run if you repeat an experiment over and over.

Core Principle

Frequentists treat parameters as fixed but unknown. For example, a population mean exists as one true value. We just don't know it. The job is to use sample data to estimate it.

How They Work with Data

No prior beliefs: Frequentists don't incorporate prior knowledge or beliefs into their analysis. The data speaks for itself.
P-values and confidence intervals: These are core in Frequentist inference. A 95% confidence interval means "if we repeated this sampling process infinitely, 95% of such intervals would contain the true parameter."
Hypothesis testing: You set up a null hypothesis and calculate the probability of seeing your data (or more extreme data) if the null is true. That's your p-value.

What Frequentists CANNOT Say

Here's the tricky part: Frequentists cannot make probability statements about parameters. They can't say "there's a 95% probability the true mean is between 10 and 20." Why? Because to them, the parameter is fixed ie it either is or isn't in that interval. The randomness is in the sampling process, not the parameter.

Bayesian Statistics: The Belief Update Approach

What Probability Means to Bayesians

To a Bayesian, probability represents degree of belief or uncertainty. It's subjective and personal. Probability quantifies what we believe about something, given all available information.

Core Principle

Bayesians treat parameters as random variables with probability distributions. There's no single "true" value we're trying to estimate instead, we describe our uncertainty about the parameter with a distribution.

How bayesians Work with Data

Prior beliefs: Bayesians start with a prior distribution that represents what they believe before seeing the data. This could be based on previous studies, expert opinion, or even complete ignorance.
Likelihood: This is the probability of observing the data given different parameter values (both approaches use this).
Posterior distribution: Using Bayes' Theorem, they combine the prior and likelihood to get a posterior distribution—what they believe after seeing the data.

The fundamental equation:
Posterior is directly proportional to Prior x Likelihood

What Bayesians CAN Say

Bayesians can make probability statements about parameters! They can say "there's a 95% probability the true mean is between 10 and 20" because they treat the parameter as having a probability distribution.

refer back to what frequentists cannot say

The Key Differences at a Glance

1. Interpretation of Probability

Frequentist: Long-run frequency (objective)
Bayesian: Degree of belief (subjective)

2. Treatment of Parameters

Frequentist: Fixed but unknown values
Bayesian: Random variables with distributions

3. Use of Prior Information

Frequentist: Not incorporated (only data matters)
Bayesian: Explicitly incorporated through prior distributions

4. Type of Results

Frequentist: Point estimates, confidence intervals, p-values
Bayesian: Probability distributions (posterior distributions), credible intervals

5. Interpretation of Intervals

Frequentist: "95% of such intervals would contain the true parameter if we repeated sampling infinitely"
Bayesian: "There's a 95% probability the parameter lies in this interval"

6. Flexibility

Frequentist: Simpler for standard problems, well-established methods
Bayesian: More flexible for complex models, but computationally intensive

A Practical Example: Coin Flipping

Imagine you flip a coin 10 times and get 7 heads. You want to know if the coin is fair.

Frequentist Approach:

Null hypothesis: The coin is fair (p = 0.5)
Calculate: What's the probability of getting 7 or more heads if the coin is actually fair?
P-value = 0.34 (not unusual)
Conclusion: "We fail to reject the null hypothesis. The data is consistent with a fair coin."
Note: They cannot say "there's a 34% chance the coin is fair"

Bayesian Approach:

Prior: Maybe you believe the coin is probably fair, but you're open to it being biased
Likelihood: The probability of 7 heads in 10 flips for different values of p
Posterior: Combine prior and likelihood using Bayes' Theorem
Conclusion: "Given the data and my prior beliefs, I now believe there's an 85% probability that p is between 0.45 and 0.85"
They can make probability statements about the parameter

Note that most of "basic" statistics we do, we use frequentists approaches. By basic I mean this is the one mostly used in introductory statistics courses and right to do so as this is the traditional approach and has been the dominant paradigm.

Strengths and weaknesses.

Frequentist Strengths

Objective doesn't depend on subjective priors
Well-established with clear procedures
Computationally simpler for many problems
Widely accepted in scientific publications

Frequentist weaknesses

Confidence intervals are often misinterpreted as Bayesian credible intervals
P-values are frequently misused and misunderstood
Ignores prior knowledge that might be valuable
Long-run frequency interpretation can be unintuitive

Bayesian Strengths

Intuitive interpretation (directly answers "what do we believe?")
Naturally incorporates prior information
Excellent for complex models and hierarchical structures
Provides full probability distributions, not just point estimates
Updates naturally as new data arrives

Bayesian weaknesses

Results depend on choice of prior (though this can also be a strength)
Computationally intensive (requires specialized software and techniques)
Less standardized ie many choices to make
"Subjective" nature makes some scientists uncomfortable

When to Use Which?

Use Frequentist methods when:

You want objective, widely-accepted standard procedures
You have no reliable prior information
You're working with simple, standard statistical tests
You need computational efficiency
You're publishing in fields that require Frequentist approaches

Use Bayesian methods when:

You have valuable prior information to incorporate
You want intuitive probability statements about parameters
You're working with complex hierarchical models
You want to update beliefs as new data arrives
You need to make decisions under uncertainty

The Bottom Line

The difference between Bayesian and Frequentist statistics boils down to fundamentally different answers to "What is probability?"

Frequentists say: "Probability is long-run frequency. Parameters are fixed. Data is random."
Bayesians say: "Probability is degree of belief. Parameters are uncertain. Everything has a distribution."

Neither is inherently "better" as they are different tools for different situations

The important thing is understanding what each approach assumes and what it can (and cannot) tell you. That way, you can interpret results correctly and choose the right tool for your problem.

Hope you've understood dear reader and leave a comment if you have any concern.

Correlation vs Regression Coefficients: A Beginner's Guide.

Nicholus Gathirwa — Thu, 06 Nov 2025 15:59:51 +0000

The Big Picture: What Are We Even Measuring?

Just keep in mind the following questions:

"Are these two things related?" – This is where correlation comes in.
"If I change one thing, how much does the other thing change?" – This is where regression comes in.

Correlation Coefficient: The Relationship Detective

The correlation coefficient (usually represented as 'r') tells us whether two variables move together and how strongly they're associated.

What It Measures

Correlation measures the strength and direction of a linear relationship between two variables. It answers: "Do these variables tend to increase or decrease together?"

The Scale

The value always falls between -1 and +1:

+1: Perfect positive correlation (as one goes up, the other goes up perfectly)
0: No linear relationship (they're doing their own thing)
-1: Perfect negative correlation (as one goes up, the other goes down perfectly)

What correlation does not tell.

And here's the crucial part: correlation doesn't tell you how much one variable changes when the other changes. It just says they move together.

It's not about the units or the actual change it's about the pattern.

Regression Coefficient: The Prediction Machine

Regression coefficients (often represented as 'b' ie beta) is a different thing entirely. Here we're talking about prediction and quantifying change.

What It Measures

A regression coefficient tells you how much the dependent variable (the outcome) changes when the independent variable (the predictor) changes by one unit, while holding everything else constant.

The Scale

Regression coefficients are NOT standardized. They're in the units of your variables!

This means they can be any value: 0.5, 50, -200, whatever makes sense for your data.

What It DOES Tell You

Regression coefficients give you a formula to predict outcomes. If you have the equation:

y = mx + c

You can plug in any x and estimate y.

The Key Differences at a Glance

1. Purpose

Correlation: Describes the strength of association
Regression: Predicts and quantifies change

2. Units

Correlation: No units! It's standardized (always between -1 and +1)
Regression: Has units! Depends on what you're measuring

3. Symmetry

Correlation: Symmetric (correlation between X and Y equals correlation between Y and X)
Regression: Asymmetric (regressing Y on X is different from regressing X on Y)

4. What They Tell You

Correlation: "These variables move together with this strength"
Regression: "When X increases by 1, Y changes by this amount"

When to Use Which?

Use correlation when:

You want to know if two things are related
You're exploring data and looking for patterns
You want to compare relationship strengths across different variable pairs
You don't have a clear cause-effect hypothesis

Use regression when:

You want to predict outcomes
You need to know the magnitude of change
You're testing specific hypotheses about how variables affect each other
You want to control for multiple variables at once

The Connection Between Them

Note that in simple linear regression (with just one predictor), correlation and regression are mathematically related! The standardized regression coefficient (called beta in some contexts) actually equals the correlation coefficient. But in their raw forms, they're giving you different information.

Hope you've understood dear reader and leave a comment if you have any concern

Understanding Skewness and Kurtosis in Statistics

Nicholus Gathirwa — Sat, 27 Sep 2025 09:17:37 +0000

Understanding Skewness and Kurtosis in Statistics

When analyzing data distributions, two crucial statistical measures help us understand the shape and characteristics of our data: skewness and kurtosis. These measures go beyond simple central tendency (mean, median, mode) and dispersion (variance, standard deviation) to provide deeper insights into how our data is distributed.

What is Skewness?

Skewness measures the asymmetry of a probability distribution around its mean. It tells us whether the data is symmetrically distributed or if it has a longer tail on one side.

Types of Skewness

1. Symmetric Distribution (Zero Skewness)

Skewness ≈ 0
The distribution is perfectly balanced around the mean
Mean = Median = Mode
Both tails are equal in length

2. Right Skewed (Positive Skewness)

Skewness > 0
The right tail is longer than the left tail
Mean > Median > Mode
Most data points are concentrated on the left side
Also called "positively skewed"

3. Left Skewed (Negative Skewness)

Skewness < 0
The left tail is longer than the right tail
Mode > Median > Mean
Most data points are concentrated on the right side
Also called "negatively skewed"

Calculating Skewness

The formula for sample skewness is:

Skewness = (n / ((n-1)(n-2))) × Σ((xi - x̄) / s)³

Where:

n = sample size
xi = each data point
x̄ = sample mean
s = sample standard deviation

Interpreting Skewness Values

-0.5 to 0.5: Approximately symmetric
-1 to -0.5 or 0.5 to 1: Moderately skewed
< -1 or > 1: Highly skewed

What is Kurtosis?

Kurtosis measures the "tailedness" of a distribution - how heavy or light the tails are compared to a normal distribution. It also indicates how peaked or flat the distribution is around the mean.

Types of Kurtosis

1. Mesokurtic (Normal Kurtosis)

Kurtosis = 3 (or excess kurtosis = 0)
Similar to a normal distribution
Moderate tail thickness and peak height

2. Leptokurtic (High Kurtosis)

Kurtosis > 3 (or excess kurtosis > 0)
Heavy tails and sharp peak
More data concentrated around the mean
Higher probability of extreme values

3. Platykurtic (Low Kurtosis)

Kurtosis < 3 (or excess kurtosis < 0)
Light tails and flat peak
Data is more spread out
Lower probability of extreme values

Calculating Kurtosis

The formula for sample kurtosis is:

Kurtosis = (n(n+1) / ((n-1)(n-2)(n-3))) × 
Σ((xi - x̄) / s)⁴ - (3(n-1)² / ((n-2)(n-3)))

This formula gives excess kurtosis (kurtosis - 3).

Interpreting Kurtosis Values

Kurtosis = 0: Normal distribution
Kurtosis > 0: Heavier tails than normal
Kurtosis < 0: Lighter tails than normal

Why Skewness and Kurtosis Matter

1. Data Quality Assessment

Understanding these measures helps identify outliers and data quality issues.

2. Statistical Test Selection

Many statistical tests assume normality. Skewness and kurtosis help determine if transformations are needed.

3. Risk Assessment

In finance, high kurtosis indicates higher probability of extreme events (fat tails).

4. Model Selection

Different distributions may be more appropriate based on skewness and kurtosis values.

Real-World Examples

Income Distribution (Right Skewed)

Most people earn moderate incomes
Few people earn extremely high incomes
Results in a long right tail

Test Scores (Left Skewed)

Most students score well on an easy test
Few students score poorly
Results in a long left tail

Stock Returns (High Kurtosis)

Most days show small price changes
Occasional days show extreme changes (crashes or rallies)
Results in heavy tails

Computing Skewness and Kurtosis

Python Example

import numpy as np
import pandas as pd
from scipy import stats
import matplotlib.pyplot as plt

# Generate sample data
np.random.seed(42)
normal_data = np.random.normal(0, 1, 1000)
skewed_data = np.random.exponential(2, 1000)

# Calculate skewness and kurtosis
print("Normal Data:")
print(f"Skewness: {stats.skew(normal_data):.3f}")
print(f"Kurtosis: {stats.kurtosis(normal_data):.3f}")

print("\nSkewed Data:")
print(f"Skewness: {stats.skew(skewed_data):.3f}")
print(f"Kurtosis: {stats.kurtosis(skewed_data):.3f}")

Common Misconceptions

Skewness doesn't always indicate outliers - it measures asymmetry, not necessarily extreme values
High kurtosis doesn't mean bimodality - it refers to tail behavior, not multiple peaks
Zero skewness doesn't guarantee normality - a distribution can be symmetric but not normal

Equip yourself, find out how to address different types of skewness (overview).

For Right Skewed Data:

You can use:

Log transformation: log(x)
Square root transformation: √x
Box-Cox transformation: (x^λ - 1) / λ

For Left Skewed Data:

Reflection then transformation: Apply right-skew transformations to (max(x) + 1 - x)

To take home:

Skewness and kurtosis provide valuable insights that complement basic descriptive statistics and help in:

Choosing appropriate statistical methods
Identifying data anomalies
Understanding risk profiles
Making informed decisions about data transformations

When combined with visual tools like histograms and Q-Q plots, these measures form a comprehensive toolkit for distribution analysis.

List Comprehension vs Dictionary Comprehension in Python and how to come up with dictionary comprehensions from 2 lists.

Nicholus Gathirwa — Sat, 27 Sep 2025 07:55:54 +0000

List Comprehension vs Dictionary Comprehension in Python

Python provides comprehensions as a concise way to create lists, dictionaries, and even sets. Two of the most common are list comprehension and dictionary comprehension. Let's explore their differences and how to use them — including combining two lists into a dictionary.

List Comprehension

A list comprehension creates a list by iterating over an iterable and applying an expression.

Syntax:

[expression for item in iterable if condition]

Example:

numbers = [1, 2, 3, 4, 5]
squares = [n**2 for n in numbers]
print(squares)  
# [1, 4, 9, 16, 25]

Here, squares is a list where each number is squared.

Dictionary Comprehension

A dictionary comprehension creates a dictionary with key–value pairs.

Syntax:

{key_expr: value_expr for item in iterable if condition}

Example:

numbers = [1, 2, 3, 4, 5]
square_dict = {n: n**2 for n in numbers}
print(square_dict)  
# {1: 1, 2: 4, 3: 9, 4: 16, 5: 25}

Here, each number is used as a key, and its square is the value.

Combining Two Lists into a Dictionary

Suppose you have two lists:

keys = ["a", "b", "c"]
values = [1, 2, 3]

You can combine them into a dictionary using dictionary comprehension.

1. Using `zip()`

The most Pythonic way:

my_dict = {k: v for k, v in zip(keys, values)}
print(my_dict)  
# {'a': 1, 'b': 2, 'c': 3}

zip() pairs elements from both lists until the shortest list ends.

2. Using Indexing with `range()`

If both lists are the same length:

my_dict = {keys[i]: values[i] for i in range(len(keys))}
print(my_dict)  
# {'a': 1, 'b': 2, 'c': 3}

3. Using `enumerate()`

By enumerating one list and indexing the other:

my_dict = {key: values[i] for i, key in enumerate(keys)}
print(my_dict)  
# {'a': 1, 'b': 2, 'c': 3}

4. Adding Conditions

You can filter while combining. For example, include only values greater than 1:

my_dict = {keys[i]: values[i] for i in range(len(keys)) if values[i] > 1}
print(my_dict)  
# {'b': 2, 'c': 3}

Basically, just keep these points in mind:

List comprehension → creates a list.
Dictionary comprehension → creates a dictionary (key–value pairs).
You can combine two lists into a dictionary using:
- zip() (most common)
- range() with indexing
- enumerate()
- Filtering with conditions

If you have any questions, please let me know in the comment section, have a good day!

Stored Procedures vs Python Functions: Surprising Similarities

Nicholus Gathirwa — Mon, 08 Sep 2025 06:49:41 +0000

Introduction

While SQL stored procedures and Python functions operate in different environments, they share remarkable conceptual similarities. Understanding these parallels can help developers leverage their knowledge across both domains.

Core Similarities

1. Encapsulation and Reusability

Both stored procedures and Python functions encapsulate logic into reusable blocks of code.

SQL Stored Procedure:

CREATE PROCEDURE CalculateBonus(
    @EmployeeID INT,
    @PerformanceRating DECIMAL(3,2)
)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10,2);
    SELECT @Bonus = salary * @PerformanceRating * 0.1
    FROM employees 
    WHERE employee_id = @EmployeeID;

    RETURN @Bonus;
END;

Python Function:

def calculate_bonus(employee_id, performance_rating):
    # Simulating database lookup
    salary = get_employee_salary(employee_id)
    bonus = salary * performance_rating * 0.1
    return bonus

2. Parameter Handling

Both support various parameter types and default values.

SQL Stored Procedure:

CREATE PROCEDURE GetEmployees(
    @DepartmentID INT = NULL,
    @MinSalary DECIMAL(10,2) = 0,
    @MaxResults INT = 100
)
AS
BEGIN
    SELECT TOP (@MaxResults) *
    FROM employees
    WHERE (@DepartmentID IS NULL OR department_id = @DepartmentID)
    AND salary >= @MinSalary;
END;

Python Function:

def get_employees(department_id=None, min_salary=0, max_results=100):
    query = "SELECT * FROM employees WHERE salary >= %s"
    params = [min_salary]

    if department_id:
        query += " AND department_id = %s"
        params.append(department_id)

    query += f" LIMIT {max_results}"

    return execute_query(query, params)

3. Control Flow and Logic

Both support conditional logic, loops, and error handling.

SQL Stored Procedure:

CREATE PROCEDURE ProcessPayroll(@PayPeriod DATE)
AS
BEGIN
    DECLARE @EmployeeID INT, @Salary DECIMAL(10,2);
    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, salary FROM employees WHERE active = 1;

    BEGIN TRY
        OPEN employee_cursor;
        FETCH NEXT FROM employee_cursor INTO @EmployeeID, @Salary;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @Salary > 0
                INSERT INTO payroll (employee_id, amount, pay_date)
                VALUES (@EmployeeID, @Salary, @PayPeriod);

            FETCH NEXT FROM employee_cursor INTO @EmployeeID, @Salary;
        END;

        CLOSE employee_cursor;
        DEALLOCATE employee_cursor;
    END TRY
    BEGIN CATCH
        -- Error handling
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Python Function:

def process_payroll(pay_period):
    try:
        active_employees = get_active_employees()

        for employee in active_employees:
            if employee['salary'] > 0:
                insert_payroll_record(
                    employee_id=employee['id'],
                    amount=employee['salary'],
                    pay_date=pay_period
                )
    except Exception as e:
        # Error handling
        rollback_transaction()
        raise e

4. Return Values and Output

Both can return single values, multiple values, or complex data structures.

SQL Stored Procedure:

CREATE PROCEDURE GetDepartmentStats(
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT,
    @AvgSalary DECIMAL(10,2) OUTPUT
)
AS
BEGIN
    SELECT 
        @EmployeeCount = COUNT(*),
        @AvgSalary = AVG(salary)
    FROM employees
    WHERE department_id = @DepartmentID;

    -- Also return result set
    SELECT name, salary 
    FROM employees 
    WHERE department_id = @DepartmentID;
END;

Python Function:

def get_department_stats(department_id):
    employees = get_employees_by_department(department_id)

    employee_count = len(employees)
    avg_salary = sum(emp['salary'] for emp in employees) / len(employees) if employees else 0

    # Return tuple (similar to OUTPUT parameters)
    return employee_count, avg_salary, employees

Key Parallels

Performance Optimization

Stored Procedures: Precompiled and cached by database engine
Python Functions: Can be optimized with caching decorators, compiled with tools like Cython

Modularity and Organization

Stored Procedures: Group related database operations
Python Functions: Organize code into logical, testable units

Security and Access Control

Stored Procedures: Control data access through procedure permissions
Python Functions: Implement validation and authorization logic

Testing and Debugging

Both: Can be tested independently with mock data
Both: Support debugging with breakpoints and logging

Practical Example: User Authentication

SQL Stored Procedure:

CREATE PROCEDURE AuthenticateUser(
    @Username NVARCHAR(50),
    @Password NVARCHAR(255),
    @IsValid BIT OUTPUT,
    @UserRole NVARCHAR(20) OUTPUT
)
AS
BEGIN
    DECLARE @StoredHash NVARCHAR(255);

    SELECT @StoredHash = password_hash, @UserRole = role
    FROM users
    WHERE username = @Username AND active = 1;

    IF @StoredHash IS NOT NULL AND @StoredHash = HASHBYTES('SHA2_256', @Password + 'salt')
        SET @IsValid = 1;
    ELSE
        SET @IsValid = 0;
END;

Python Function:

import hashlib

def authenticate_user(username, password):
    user = get_user_by_username(username)

    if not user or not user['active']:
        return False, None

    # Hash the provided password
    password_hash = hashlib.sha256((password + 'salt').encode()).hexdigest()

    if password_hash == user['password_hash']:
        return True, user['role']
    else:
        return False, None

Key Differences to Consider

While similar in concept, important differences exist:

Aspect	Stored Procedures	Python Functions
Execution Environment	Database server	Application server
Language Features	SQL-specific	Full programming language
Data Access	Direct database access	Through connectors/ORMs
Deployment	Database-specific	Cross-platform
Version Control	Challenging	Native support
Unit Testing	Limited tools	Rich ecosystem

Keep in mind:

The similarities between stored procedures and Python functions highlight fundamental programming concepts that transcend specific technologies. Both serve as building blocks for creating maintainable, reusable, and efficient code. Understanding these parallels helps developers:

Transfer knowledge between database and application development
Make informed decisions about where to implement business logic
Appreciate the universal principles of good software design

Whether you're working with SQL stored procedures or Python functions, the core principles of modularity, reusability, and clear interfaces remain constant.

SQL Query techniques and their differences ie Subqueries, CTEs and stored procedures.

Nicholus Gathirwa — Mon, 08 Sep 2025 06:46:17 +0000

Overview

SQL offers multiple approaches for complex data operations. Understanding the differences between subqueries, Common Table Expressions (CTEs), and stored procedures is crucial for writing efficient and maintainable database code.

Subqueries

Definition

A subquery is a query nested inside another SQL statement. It executes first and passes its result to the outer query.

Characteristics

Execution: Runs once or multiple times depending on context
Scope: Limited to the statement where it's defined
Reusability: Cannot be reused across different queries
Performance: Can be less efficient for complex operations

Example

-- Find employees earning more than average salary
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery
SELECT e1.name, e1.department
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

Best Use Cases

Simple filtering conditions
One-time calculations
EXISTS/NOT EXISTS operations

Common Table Expressions (CTEs)

Definition

CTEs are temporary named result sets that exist only during query execution. They improve readability and can be referenced multiple times within a single statement.

Characteristics

Readability: Makes complex queries more readable
Reusability: Can be referenced multiple times in the same query
Recursion: Supports recursive operations
Performance: Similar to subqueries but more maintainable

Example

-- Basic CTE
WITH high_earners AS (
    SELECT name, salary, department
    FROM employees
    WHERE salary > 75000
)
SELECT department, COUNT(*) as high_earner_count
FROM high_earners
GROUP BY department;

-- Recursive CTE for hierarchical data
WITH RECURSIVE employee_hierarchy AS (
    -- Base case
    SELECT employee_id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Best Use Cases

Complex queries requiring multiple references to the same result set
Recursive operations (organizational charts, bill of materials)
Improving query readability
Window function operations

Stored Procedures

Definition

Stored procedures are precompiled SQL code blocks stored in the database that can accept parameters and return results.

Characteristics

Persistence: Stored permanently in the database
Performance: Precompiled and cached for faster execution
Reusability: Can be called from multiple applications
Security: Provide controlled access to data
Logic: Can contain complex business logic with control structures

Example

-- Creating a stored procedure
CREATE PROCEDURE GetEmployeesByDepartment(
    @DepartmentName VARCHAR(50),
    @MinSalary DECIMAL(10,2) = 0
)
AS
BEGIN
    SELECT 
        employee_id,
        name,
        salary,
        hire_date
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = @DepartmentName
    AND e.salary >= @MinSalary
    ORDER BY e.salary DESC;
END;

-- Calling the stored procedure
EXEC GetEmployeesByDepartment @DepartmentName = 'Engineering', @MinSalary = 60000;

Best Use Cases

Frequently executed operations
Complex business logic requiring multiple steps
Data validation and transformation
Batch processing operations
Enforcing business rules at the database level

Key Differences Summary

Aspect	Subqueries	CTEs	Stored Procedures
Scope	Single statement	Single statement	Database-wide
Reusability	No	Within same query	Across applications
Performance	Variable	Similar to subqueries	Optimized (precompiled)
Complexity	Limited	Moderate	High
Maintenance	Inline only	Inline only	Centralized
Parameters	No	No	Yes
Business Logic	No	Limited	Yes

When to Choose What

Use Subqueries When:

You need a simple, one-time filtering condition
The logic is straightforward and won't be reused
Working with small datasets

Use CTEs When:

You need to reference the same result set multiple times
Working with recursive data structures
You want to improve query readability
Breaking down complex queries into logical steps

Use Stored Procedures When:

The operation will be called frequently
You need to implement complex business logic
Multiple applications need the same functionality
You want centralized control over data access
Performance optimization is critical

Things to remember:

Each approach serves different purposes in database development. Subqueries are great for simple operations, CTEs excel at making complex queries readable and handling recursive scenarios, while stored procedures provide robust, reusable solutions for complex business logic. Choose based on your specific requirements for performance, maintainability, and complexity.

Lamda functions and decorators: A developer's guide.

Nicholus Gathirwa — Mon, 08 Sep 2025 06:42:05 +0000

Lambda Functions: Anonymous Functions Made Simple

Lambda functions are anonymous functions that can be defined inline without a formal def statement. They're particularly useful for short, simple operations that don't warrant a full function definition.

Syntax and Basic Usage

The basic syntax follows the pattern: lambda arguments: expression

# Traditional function
def square(x):
    return x ** 2

# Lambda equivalent
square_lambda = lambda x: x ** 2

# Usage
print(square_lambda(5))  # Output: 25

Common Use Cases

Lambda functions shine in functional programming contexts, especially with built-in functions like map(), filter(), and sorted():

# Filtering even numbers
numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
evens = list(filter(lambda x: x % 2 == 0, numbers))
# Result: [2, 4, 6, 8, 10]

# Sorting by custom criteria
students = [('Alice', 85), ('Bob', 92), ('Charlie', 78)]
sorted_students = sorted(students, key=lambda student: student[1])
# Result: [('Charlie', 78), ('Alice', 85), ('Bob', 92)]

When to Use Lambda Functions

Short, simple operations that fit on one line
Temporary functions needed for a specific context
Functional programming with map(), filter(), reduce()
Event handling in GUI applications

Important: Lambda functions are limited to expressions only—no statements like print() or assignments.

Decorators: Enhancing Functions with Style

Decorators are a powerful Python feature that allows you to modify or extend the behavior of functions or classes without permanently modifying their code. They implement the decorator pattern and are a prime example of higher-order functions.

Understanding the Basics

At its core, a decorator is a function that takes another function as input and returns a modified version of that function:

def my_decorator(func):
    def wrapper():
        print("Something before the function")
        func()
        print("Something after the function")
    return wrapper

@my_decorator
def say_hello():
    print("Hello!")

# Equivalent to: say_hello = my_decorator(say_hello)

Decorators with Arguments

Real-world decorators often need to handle functions with various arguments:

def timing_decorator(func):
    def wrapper(*args, **kwargs):
        import time
        start = time.time()
        result = func(*args, **kwargs)
        end = time.time()
        print(f"{func.__name__} took {end - start:.4f} seconds")
        return result
    return wrapper

@timing_decorator
def calculate_sum(n):
    return sum(range(n))

Practical Examples

Logging Decorator:

def log_calls(func):
    def wrapper(*args, **kwargs):
        print(f"Calling {func.__name__} with args: {args}, kwargs: {kwargs}")
        return func(*args, **kwargs)
    return wrapper

@log_calls
def add(a, b):
    return a + b

Authentication Decorator:

def require_auth(func):
    def wrapper(*args, **kwargs):
        if not user_is_authenticated():
            raise PermissionError("Authentication required")
        return func(*args, **kwargs)
    return wrapper

@require_auth
def sensitive_operation():
    return "Top secret data"

Higher-Order Functions: The Foundation

Higher-order functions are functions that either:

Take one or more functions as arguments
Return a function as their result

Both decorators and many uses of lambda functions are examples of higher-order functions in action.

Built-in Higher-Order Functions

Python provides several built-in higher-order functions:

map() - Applies a function to every item in an iterable
filter() - Filters items based on a function's return value
reduce() - Applies a function cumulatively to items in a sequence
sorted() - Sorts items using a custom key function

Creating Custom Higher-Order Functions

def apply_operation(operation):
    def operate_on_list(numbers):
        return [operation(x) for x in numbers]
    return operate_on_list

# Usage
double = apply_operation(lambda x: x * 2)
result = double([1, 2, 3, 4, 5])  # [2, 4, 6, 8, 10]

Best Practices and Tips

Lambda Functions

Keep them simple and readable
Use meaningful variable names even in short lambdas
Consider regular functions for complex logic
Avoid nested lambdas for better readability

Decorators

Use functools.wraps() to preserve function metadata:

  from functools import wraps

  def my_decorator(func):
      @wraps(func)
      def wrapper(*args, **kwargs):
          return func(*args, **kwargs)
      return wrapper

Document what your decorators do
Keep decorator logic separate from business logic
Consider using classes for complex decorators with state

General Guidelines

Favor clarity over cleverness
Test decorated functions thoroughly
Be mindful of performance implications
Use type hints when possible for better code documentation

Basically,

Lambda functions, decorators, and higher-order functions are powerful tools in Python's functional programming toolkit. Lambda functions provide quick, anonymous function definitions for simple operations. Decorators offer an elegant way to extend function behavior without modifying the original code. Higher-order functions enable flexible, reusable code patterns.

Mastering these concepts will make your Python code more expressive, maintainable, and Pythonic. Start with simple examples and gradually work your way up to more complex implementations as you become comfortable with these patterns.

PostgreSQL Installation and Setup on Linux Server: A Complete Guide by Nicholus Gathirwa.

Nicholus Gathirwa — Sun, 03 Aug 2025 19:37:34 +0000

PostgreSQL Installation and Setup on Linux Server: A Complete Guide

Prerequisites

Before we begin, you'll need:

An Azure account (free tier works fine)
A terminal application (Git Bash, PowerShell for Windows, or Terminal for Mac)
Basic knowledge of Linux command line

Step 1: Creating an Azure Virtual Machine

1.1 Creating Your Azure Account

Visit Azure Portal at portal.azure.com
Sign up for a free account - Azure provides $200 in free credits for new users
Student Account Benefits: If you have a student email, use Azure for Students to get:
- $100 in free Azure credits (no credit card required)
- Access to free services for 12 months
- Additional student-specific resources

1.2 Setting Up Your VM

Log into Azure Portal and navigate to Virtual Machines
Click "Create" to start the VM creation process
Choose your region - Select a region close to your users (e.g., West US 2)
Select VM specifications:
- Choose an appropriate image (Ubuntu Server recommended)
- Select VM size based on your needs
- Set up authentication

VM Configuration

# Example VM settings
Username: luxStudent
Password: developer@123
Region: West US 2

Review and Create your virtual machine
Go to Resource once deployment is complete
Copy the Public IP address for SSH access

Step 2: Connecting to Your Linux Server

2.1 SSH Connection

SSH (Secure Shell) operates on port 22 by default and provides secure remote access to your server.

# Basic SSH connection syntax
ssh username@ip_address

# Example connection
ssh luxStudent@102.37.147.102

2.2 First Login

Enter your password when prompted
Test your connection by creating a directory:

# Create a test directory
mkdir mercy

# List contents to verify
ls

2.3 Basic File Operations

# Create a file
touch script.sql

# Edit the file using vim
vim script.sql

# Exit vim without saving
:q!

Step 3: Installing PostgreSQL

3.1 System Preparation

Before installing PostgreSQL, update your system packages:

# Update package lists
sudo apt update

3.2 PostgreSQL Installation

Install PostgreSQL along with additional contrib packages:

# Install PostgreSQL and additional tools
sudo apt install postgresql postgresql-contrib

The postgresql-contrib package includes useful additional utilities and extensions.

3.3 Verify Installation

Check if PostgreSQL service is running:

# Check PostgreSQL service status
sudo systemctl status postgresql

You should see the service as "active" if installation was successful.

Step 4: PostgreSQL Initial Configuration

4.1 Accessing PostgreSQL

Switch to the postgres user and access the PostgreSQL prompt:

# Switch to postgres user
sudo -i -u postgres

# Access PostgreSQL command line
psql

4.2 Database Operations

Once in the PostgreSQL prompt, you can perform various operations:

-- List all databases
\l

-- Create a new database
CREATE DATABASE luxStudent;

-- Verify database creation
\l

4.3 User Management

Create a new user and assign privileges:

-- Create a new user with password
CREATE USER "username" WITH PASSWORD "password";

-- Grant superuser privileges
ALTER USER "username" WITH SUPERUSER;

-- Exit PostgreSQL prompt
\q

Then exit from postgres user:

exit

Step 5: Configuring PostgreSQL for Remote Access

5.1 PostgreSQL Configuration Files

Navigate to the PostgreSQL configuration directory:

# Go to PostgreSQL configuration directory
cd /etc/postgresql

# List PostgreSQL versions
ls

# Navigate to version directory (e.g., 16)
cd 16

# Go to main configuration directory
cd main

# List configuration files
ls

5.2 Editing postgresql.conf

Edit the main PostgreSQL configuration file:

# Edit PostgreSQL configuration
sudo vim postgresql.conf

Find and modify the listen_addresses setting:

# Change from:
# listen_addresses = 'localhost'

# To:
listen_addresses = '*'

Save and exit vim (:wq).

5.3 Configuring Client Authentication

Edit the pg_hba.conf file to allow remote connections:

# Edit authentication configuration
sudo vim pg_hba.conf

Add the following line to allow connections from any IP address:

# Allow all connections (use with caution in production)
host all all 0.0.0.0/0 md5

Save and exit vim.

5.4 Restart PostgreSQL Service

Apply the configuration changes:

# Restart PostgreSQL service
sudo systemctl restart postgresql

# Verify service status
sudo systemctl status postgresql

Step 6: Azure Network Configuration

6.1 Configure Inbound Port Rules

Go to Azure Portal → Your VM → Networking
Click "Add inbound port rule"
Configure the rule:
- Destination port ranges: 5432
- Protocol: TCP
- Action: Allow
- Name: PostgreSQL
Click "Add"

Step 7: Testing Remote Connection

7.1 Using DBeaver

To test your PostgreSQL setup with a database client like DBeaver:

Host: Your VM's public IP address eg 192.168.90.1
Port: 5432
Database: The database name you created
Username: The username you created
Password: The corresponding password

7.2 Connection Troubleshooting

During installation and setup, one might face a couple of issues. Follow the steps below, Generally applicable to most situations, which may include but not limited to potential issues faced during installation and setup:

Verify PostgreSQL is running:

sudo systemctl status postgresql

Check if port 5432 is listening:

sudo netstat -tlnp | grep 5432

Verify Azure firewall rules are properly configured
Check authentication settings in pg_hba.conf

Keep in mind

Important Security Notes:

Never use weak passwords in production environments
Restrict IP access instead of allowing 0.0.0.0/0 in production
Use SSL/TLS encryption for remote connections
Regularly update your PostgreSQL installation
Implement proper backup strategies

Production-Ready Security:

# Example of restricting access to specific IP ranges
host all all 192.168.1.0/24 md5
host all all 10.0.0.0/8 md5

Additional Resources

This guide provides a comprehensive walkthrough for setting up PostgreSQL on a Linux server deployed on microsoft azure, connecting to the server locally using dbeaver and other configurations that may be required. Remember to always follow security best practices and regularly update your systems. Have a good one!!!