Forem: Kelvin

Advanced SQL for Data Analytics:

Kelvin — Tue, 17 Mar 2026 15:20:06 +0000

WINDOW FUNCTIONS IN SQL.

Window functions perform calculations across a set of rows related to the current row without grouping the rows into a single output. They preserve the individual row details while providing additional contextual insights.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Common types of window functions.
1: RANKING - Row_number, rank, dense_rank - It assigns numbers or ranks to rows based on order.
Rank

Row number

2: AGGREGATE - sum, avg, min, max, count - calculates sum, averages or extremes across the window.
Total

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Subqueries

A subquery (or nested query) is a SQL query written inside another query. It allows you to
perform an operation that depends on the result of another query.

The SELECT clause - Subqueries in the SELECT clause add an extra computed column to the result set. Each subquery executes once for every row of the outer query.

SELECT  
name, 
(SELECT COUNT(*)  
FROM projects  
WHERE projects.employee_id = employees.employee_id) AS total_projects 
FROM employees;

The FROM clause - A subquery in the FROM clause creates a temporary or derived table. The outer query can then select or filter data from it.

SELECT * 
FROM ( 
SELECT  
department_id,  
COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
) AS dept_summary;

The WHERE or HAVING clause - Subqueries in the WHERE clause allow the outer query to filter results based on another query’s output.

SELECT  
department_name 
FROM departments 
WHERE department_id IN ( 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 1 
);

Correlated vs Non-Correlated Subqueries

Non-correlated subquery - A non-correlated subquery does not depend on the outer query. It can be executed on its own.
Correlated subquery - A correlated subquery depends on the outer query. It is evaluated once per row of the outer query.

Common Table Expressions (CTEs)

A CTE (Common Table Expression) is like creating a temporary result set (or virtual table)
that exists only during the execution of a single SQL query.

WITH high_salary AS ( 
SELECT name, salary 
FROM employees 
WHERE salary > 45000 
) 
SELECT * 
FROM high_salary;

CTE with Joins

You can use CTEs to simplify joins

WITH emp_dept AS ( 
SELECT e.name, d.department_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
) 
SELECT * FROM emp_dept;

Stored Procedures

A stored procedure is a named block of SQL logic stored inside the database that you can
execute using: CALL procedure_name(parameters);

Procedures are for actions, such as:

Insert data (add customer, add book, add order)
Update data (update contact, update email)
Delete data (delete customer safely)
Validation and enforcement of business rules
Multi-step operations (create order + check customer + log)

Procedure Syntax

CREATE OR REPLACE PROCEDURE procedure_name(param_name param_type, ...)
LANGUAGE plpgsql
AS $$
BEGIN -- SQL statements
END;
$$;

Explanation of each keyword

CREATE - creates the procedure
OR REPLACE - updates the procedure if it already exists (no need to drop first)
PROCEDURE procedure_name(...) - name + inputs (parameters)
LANGUAGE plpgsql - enables procedural features (IF, LOOP, variables)
AS $$ ... $$ - defines the body of the procedure
BEGIN ... END; - start/end of procedure logic
Call with: CALL procedure_name(...)

CREATE OR REPLACE PROCEDURE add_customer( 
p_first_name VARCHAR, 
p_last_name  VARCHAR, 
p_email      VARCHAR, 
p_contact    VARCHAR, 
p_city       VARCHAR) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
INSERT INTO customers(first_name, last_name, email, contact, city) 
VALUES (p_first_name, p_last_name, p_email, p_contact, p_city); 
END; 
$$;

Explanation

CREATE OR REPLACE PROCEDURE add_customer(...) - Creates a reusable procedure named add_customer.
Parameters like p_first_name VARCHAR - Inputs we pass when calling the procedure.
LANGUAGE plpgsql - Enables procedural execution.
BEGIN - Starts execution block.
INSERT INTO customers(...) - Specifies the table and columns being inserted.
VALUES (...) - Inserts the parameter values.
END;- Ends procedure.
CALL add_customer(...) - Runs the procedure by inserting values into our table.

Delete Customer Safely

CREATE OR REPLACE PROCEDURE delete_customer_safely( 
p_customer_id INT 
) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
DELETE FROM orders 
WHERE customer_id = p_customer_id; 
DELETE FROM customers 
WHERE customer_id = p_customer_id; 
END; 
$$;

Introduction to Python

Kelvin — Tue, 17 Mar 2026 14:59:28 +0000

Python is a powerful and flexible programming language created by Guido van Rossum and first released in 1991.Over the years, Python has become one of the most loved and used languages in the world.

Python installation.

python can be installed from Microsoft store or the browser.

python installation from the web.
python then select version.

checking the version of python installed in your pc

Open your command line and type python --version

This confirms the version of python installed in your computer.

example of a python code.

What Can You Use Python For

Web Development, and software development.
Data analysis, machine learning, data engineering, AI, and math.
System scripting.

Why Use Python?

Platform-independent - Python works everywhere: Windows, Mac, Linux, Raspberry Pi, or any other platform.
Simple & Readable Syntax - python is simple to read and understand.
Fewer Lines of Code - Python lets you do more with less typing.
Fast Prototyping - Python runs your code line by line, immediately showing results or errors.
Multiple Programming Styles - Python lets you write code in different ways i.e. procedural, object oriented and functional (using functions like math operations)

Python Syntax.

Python has rules about how code should be written so it can be understood and executed properly. It relies on indentation and clean line-by-line execution and throws errors if the rules are not followed. Python emphasizes readability and simplicity, making it easier for beginners to learn and professionals to maintain.

Key Syntax Rules in Python.

Python uses indentation to define blocks of code.
Indentation means adding spaces at the beginning of a line to show that this line belongs to a group or block of code.
You can technically use any number of spaces - at least one. But the convention is to use four spaces for each indentation level.

if 5 > 2: 
    print("Five is greater than two!")

Mixing indentation levels is not allowed Python needs consistency. Once you choose a number of spaces, stick with it throughout the block.

Python Variables.

A variable is a named memory location.

name = "lee"
    print("name")

here name is a variable for storing the string "lee"

In Python, you don’t have to declare the data type of a variable like you do in many other languages. Python understands it based on the value you assign.

Rules of naming variables.

A variable name must start with a letter (a–z, A–Z) or an underscore _.
It can only contain alphanumeric characters and underscores (A–Z, a–z, 0–9, and _)
Variable names are case-sensitive.
It cannot start with a number.
You cannot use reserved words i.e. (int, True ETC)

Best Practices.

Use descriptive names
Use snake_case for variables
Avoid short, vague names like x, y, unless in quick examples or math

Assigning Multiple Variables

Python lets you assign values to multiple variables at once: a, b, c = 1, 2, 3
All at once! Or assign the same value to multiple variables: x = y = z = "Python"

Checking the Type of a Variable - You can use the type() function to find out what kind of value a variable holds.

name = "Alice" 
print(type(name))

the output will be str (string)

Python Constants.

Values that don’t change during normal program execution
In real life, think of your birthdate. You store it once and it never changes - that's a constant.
In Python, we use uppercase letters to indicate that a variable is meant to be a constant e.g.

PI = 3.14159

Python does not have built-in constants, but we use uppercase variable names to show they are constants.

Python Data Types.

A data type is the classification of data that tells the computer what kind of data you are working with and what operations can be performed on it.

Python’s Built-in Data Types.

1. Numeric Types

int – Integer (whole numbers)

age = 25

float – Floating point (decimal numbers)

height = 5.9

complex – Complex numbers (used in scientific computing)

complex_num = 3 + 2j

2. Text Type

str – String (a sequence of characters)

"Python is amazing!"

3. Boolean Type

bool - Can only be True or False.

is_logged_in = True

Arithmetic Operators in Python

Addition (+) -- Adds two numbers
Subtraction (-) -- Subtract right from left
Multiplication (*) -- Multiply numbers
Division (/) -- Divide and get float
Modulus (%) -- Get the remainder

comparison operators

Greater than (>)
Less than (<)
Equal to (==) note that (=) is an assignment operator
Not equal to (=!)
Greater than or equal to (>=)
Less than or equal to (>=)

Logical operators (and,or,not)

AND - Returns true only if both conditions are true
OR - Returns true if at least one condition is true
NOT - Reverses the value (true becomes false, false becomes true)

sql and databases

Kelvin — Fri, 13 Mar 2026 13:04:02 +0000

What is a database.

A database is an organized collection of data stored electronically.

What is SQL

SQL stands for structured query language. It is used for storing data, retrieving data, updating data and deleting data.

Schema in SQL

A schema is a logical container or namespace inside a database that groups related objects like tables, views, indexes, stored procedures, functions and sequences.
You can think of a schema as a folder inside a database helping you organize and manage objects.

Why use schemas.

Schemas help organize large datasets into sections, control access to different parts of the database and avoiding name conflicts,

Creating a schema.

CREATE SCHEMA sales_data;

this creates a schema named sales_data.

Setting search path.

set search_path to sales_data;

this sets the search path to the schema. To check what schema you are using show search_path;

Creating a table in that schema.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100));

this creates a table named customers.

Types of Database management systems(dbms).

DBMS means Database management system. It is a software that allows us to create, manage and interact with databases.

Types of databases.

Relational database - uses tables with rows and columns. examples: PostgreSQL, MySQL, Oracle.
NoSQL DBMS - non tabular (key-value, Json, Graph) examples: MongoDB, Cassandra.
In-Memory DBMS - stores data in RAM. example: Redis
Cloud DBMS - cloud-hosted & managed by providers. examples: Amazon RDS, Azure PostreSQL

SQL datatypes

Data types define the type of data that can be stored in a column. They enforce data integrity and data accuracy.

Main data types categories.

1. Numeric Types

Integer/int - whole numbers e.g. 10,50,-4
Serial - Auto-incrementing ID e.g. 1,2,3
Numeric - precision numbers (money) e.g. 12345.76
Decimal - same as numeric - 146505.95

2. Character Types

CHAR - fixed length string e.g. 'Kenya'
VARCHAR - variable-length string e.g. 'John Kamau'
TEXT - unlimited string length e.g. long comments, emails

3. Date & Time Types

DATE - stored date only e.g. '2020-01-01'
TIMESTAMP - stores date and time e.g.'2024-10-10 12:20:00'
TIME - time of the day e.g. '13:30:00'

4. Boolean

Boolean - True or False e.g. True or False

Creating tables in SQL

Basic syntax

A table consists of rows and columns

create table customers(
Customer_id serial primary key,
First_name varchar(50) not null,
Last_name varchar (50) not null,
Email varchar (100) unique not null,
Phone char(13) );

Constraints

PRIMARY KEY - uniquely identifies each record
NOT NULL - cannot be empty
UNIQUE - all values must be different
DEFAULT - provides a default value
FOREIGN KEY - links to another table's primary key

Best practices
Use singular table names e.g. employees
Define primary keys
Use meaningful column names
Prefer VARCHAR over CHAR unless fixed
Use foreign keys for relational integrity

Inserting Data into a database.

INSERT INTO customers (First_name, Last_name, Email, Phone) 
VALUES   
('John', 'Doe', 'john.doe@example.com', '+254712345678'), 
('Jane', 'Smith', 'jane.smith@example.com', '+254798765432'), 
('Paul', 'Otieno', 'paul.otieno@example.com', '+254701234567'), 
('Mary', 'Okello', 'mary.okello@example.com', '+254711223344');

Viewing and querying Tables.

Viewing databases

-- from postgreSQL

SELECT datname
FROM pg_database
WHERE datistemplate = false;

-- from MySQL

SHOW DATABASES;

Viewing all tables in PostgreSQL

SELECT table_name 
FROM schema_name.tables
WHERE table_schema = 'sales_data';

View data from a table.

SELECT *
FROM customers;

Table manipulation and common SQL key words.

Add a column to a table

ALTER TABLE customers  
ADD COLUMN city VARCHAR(100);

this adds a new city column to the customers table.

Updating existing data

Updating a single row

UPDATE customers  
SET city = 'Nairobi'  
WHERE customer_id = 1;

Updating multiple rows with a CASE statement.

UPDATE customers  
SET city = CASE customer_id  
WHEN 1 THEN 'Nairobi'  
WHEN 2 THEN 'Mombasa'  
WHEN 3 THEN 'Kisumu'  
WHEN 4 THEN 'Nakuru'  
ELSE city  -- Retain original city if no match 
END;

Updating specific quantity

UPDATE orders  
SET quantity = CASE order_id  
WHEN 1 THEN 2  
WHEN 2 THEN 1  
WHEN 3 THEN 3  
WHEN 4 THEN 2  
WHEN 5 THEN 1  
ELSE quantity 
END;

Updates orders with specific quantity.

Delete columns from SQL

Remove column from a table.

ALTER TABLE books  
DROP COLUMN published_date;

Delete rows from SQL

Delete a specific row

DELETE FROM orders  
WHERE order_id = 3;

Drop a table

DROP TABLE books;

Deletes the entire table and all its data. Use with caution!!

Rename column

ALTER TABLE customers RENAME COLUMN phone_number TO contact_number;

Renames a column.

MODIFY COLUMN TYPE - Change a Column’s Data Type

ALTER TABLE orders  
ALTER COLUMN quantity TYPE DECIMAL(5,2);

SET DEFAULT Value for Column

ALTER TABLE orders  
ALTER COLUMN quantity SET DEFAULT 1;

DROP DEFAULT Value

ALTER TABLE orders  
ALTER COLUMN quantity DROP DEFAULT;

Add a NOT NULL Constraint

ALTER TABLE customers  
ALTER COLUMN email SET NOT NULL;

Drop a NOT NULL Constraint

ALTER TABLE customers  
ALTER COLUMN email DROP NOT NULL;

Add a Foreign Key Constraint

ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

Drop a Foreign Key Constraint

You must know the constraint name. You can find it using:

SELECT conname  
FROM pg_constraint  
WHERE conrelid = 'orders'::regclass;

Then drop it:

ALTER TABLE orders DROP CONSTRAINT fk_customer;

COMMON SQL KEYWORDS

1. SELECT - Retrieve Data

SELECT column1, column2 FROM table_name;
Examples:

SELECT customer_name, city FROM customers; 
SELECT title, author FROM books; 
SELECT customer_id, book_id FROM orders;

2. WHERE - Filter Data

SELECT column FROM table WHERE condition;
Examples:

SELECT * FROM customers WHERE city = 'Nairobi'; 
SELECT title, price FROM books WHERE price > 2000; 
SELECT * FROM orders WHERE customer_id = 1;

3. ORDER BY - Sort Data

SELECT columns FROM table ORDER BY column ASC|DESC;
Examples:

SELECT title, price FROM books ORDER BY price ASC; 
SELECT customer_name FROM customers ORDER BY customer_name ASC;

SELECT * FROM orders ORDER BY order_date DESC;

4. GROUP BY - Group and Summarize Data

SELECT column, AGG_FUNCTION(column) FROM table GROUP BY column;
Examples:

SELECT author, COUNT(*) FROM books GROUP BY author; 
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

SELECT city, COUNT(*) FROM customers GROUP BY city;

5. HAVING - Filter After Grouping

SELECT column, COUNT() FROM table GROUP BY column HAVING COUNT() > 1;
Examples:

SELECT author, COUNT(*) FROM books GROUP BY author HAVING COUNT(*) > 1;

SELECT customer_id, COUNT(*)
FROM orders GROUP BY customer_id HAVING COUNT(*) 
> 1;

6. LIMIT - Restrict Number of Results

SELECT * FROM table LIMIT number;
Examples:

SELECT * FROM books LIMIT 3;

SELECT title, price FROM books 
ORDER BY price DESC LIMIT 2;

SELECT * FROM customers LIMIT 5;

Summary

SELECT -Retrieve data
WHERE - Filter records
ORDER BY - Sort records
GROUP BY - Group data
HAVING - Filter grouped data
LIMIT - Restrict number of results
INSERT - Add data
UPDATE - Modify existing data
DELETE - Remove rows
ALTER - Modify table structure

SQL Aggregate Functions

COUNT() Counts the number of rows (or non-NULL values)

SELECT COUNT(*) AS kisumu_customers FROM customer 
WHERE city = 'Kisumu';

SUM() Adds up numeric values in a column

SELECT SUM(price) AS total_book_price FROM books;

AVG() Calculates the average (mean) of numeric values

SELECT AVG(price) AS avg_price_2023 FROM books 
WHERE published_date >= '2023-01-01';

MAX() Finds the highest value in a column

SELECT MAX(order_date) AS latest_order FROM orders;

MIN() Finds the lowest value in a column

SELECT MIN(quantity) AS min_quantity FROM orders;

SQL Operators

1. Arithmetic Operators - Used for mathematical calculations.

Addition SELECT title, price, price + 200 AS new_price FROM books;
Subtraction SELECT book_name, price, price - 150 AS discounted_price FROM books;
Multiplication SELECT book_name, price, price * 2 AS double_price FROM books; / Division SELECT book_name, price, price / 2 AS half_price FROM books; % Modulo (Remainder) SELECT book_name, price, price % 3 AS remainder FROM books;

2. Comparison Operators - Used to compare values, often in `WHERE`.

= Equals - Selects rows where a column matches an exact value.
SELECT * FROM books WHERE author = 'David Kimani';
!= or <> Not equal to - Selects rows where a column value does not match the specified value.
SELECT * FROM orders WHERE order_date <> '2023-04-01';
Greater than - Selects rows where column value is greater than the given value.
SELECT * FROM orders WHERE order_date > '2023-04-03';
< Less than - Selects rows where column value is less than the given value.
SELECT * FROM orders WHERE order_date < '2023-04-04';
= Greater than or equal to - Selects rows where column value is greater than or equal to the given value.
SELECT * FROM customer WHERE customer_id >= 2;
<= Less than or equal to - Selects rows where column value is less than or equal to the given value.
SELECT * FROM books WHERE price <= 2200;
BETWEEN Between two values - Selects rows with column values between two values (inclusive).
SELECT * FROM orders WHERE order_date BETWEEN '2023-04-01' AND '2023-04-03';
LIKE Pattern matching - Use LIKE to search for patterns in text data.
SELECT * FROM customer WHERE email LIKE '%gmail.com';
IN Matches values in a list - Use IN to filter records by matching any value in a list.
SELECT * FROM books WHERE author IN ('David Kimani', 'Grace Achieng');

3. Logical Operators - combine multiple conditions.

AND - All conditions must be true
SELECT * FROM customer WHERE city = 'Kisumu' AND first_name = 'Paul';
OR - At least one condition is true
SELECT * FROM customer WHERE city = 'Nairobi' OR city = 'Kisumu';
NOT - Reverses the result of a condition
SELECT * FROM orders WHERE NOT order_date = '2023-04-01';

4. Bitwise Operators - Used for bit-level operations.

Bitwise AND
Bitwise OR
Bitwise XOR

5. Set Operators - Used to combine result sets of two SELECT queries.

UNION - Combines results, removes duplicates

SELECT first_name AS name FROM customer 
UNION 
SELECT author AS name FROM books;

UNION ALL - Combines results, keeps duplicates

SELECT first_name AS name FROM customer 
UNION ALL 
SELECT author AS name FROM books;

INTERSECT - Returns common records

SELECT first_name AS name FROM customer 
INTERSECT 
SELECT author AS name FROM books;

EXCEPT - Returns records in first query, not in second

SELECT first_name AS name FROM customer 
EXCEPT 
SELECT author AS name FROM books;

6. Other Useful Operators

IS NULL - Checks for missing values

SELECT * FROM customer 
WHERE city IS NULL;

IS NOT NULL - Checks for non-missing values

SELECT * FROM customer 
WHERE city IS NOT NULL;

DISTINCT - Removes duplicates

SELECT DISTINCT city FROM customer;

Aggregate Functions

1. COUNT() - Counting Rows

The COUNT() function counts the number of rows that match a condition.

SELECT COUNT(*) AS kisumu_customers FROM customer 
WHERE city = 'Kisumu';

2. SUM() - Total of Numbers

SUM() adds up the values in a numeric column.

SELECT SUM(price) AS total_book_price FROM books;

3. AVG() - Average Value

AVG() calculates the average (mean) of values in a numeric column.


SELECT AVG(price) AS avg_price_2023 FROM books 
WHERE published_date >= '2023-01-01';

4. MAX() - Maximum Value

MAX() finds the largest value in a numeric or date column.

SELECT MAX(order_date) AS latest_order FROM orders;

5. MIN() - Minimum Value

MIN() finds the smallest value in a numeric or date column.

SELECT MIN(quantity) AS min_quantity FROM orders;

String, Date, and Mathematical Functions

SQL String Functions

CONCAT(): Combine Strings - Joins multiple strings into one.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

SUBSTRING(): Extract Part of a String - Extracts a portion of a string

SELECT SUBSTRING(email, POSITION('@' IN email) + 1) AS domain FROM customers;

LENGTH(): String Length - Returns the number of characters in a string.

SELECT email, LENGTH(email) AS email_length FROM customers;

UPPER() and LOWER(): Change Case UPPER() – Converts text to uppercase.

SELECT UPPER(customer_name) AS upper_name FROM customers;

LOWER() – Converts text to lowercase.

SELECT LOWER(email) AS standardized_email FROM customers;

TRIM(), LTRIM(), RTRIM(): Remove Spaces - Cleans unwanted spaces from strings.

SELECT TRIM(string); 
SELECT LTRIM(string); 
SELECT RTRIM(string);

REPLACE(): Replace Text - Replaces all occurrences of a substring

SELECT REPLACE(country, 'Kenya', 'KE') AS short_country FROM customers;

SQL Date Functions

NOW(): Current Date and Time

SELECT NOW() AS current_datetime;

YEAR(), MONTH(), DAY(): Extract Date Parts

SELECT order_id, YEAR(order_date) AS order_year FROM orders; 
SELECT order_id, MONTH(order_date) AS order_month FROM orders; 
SELECT order_id, DAY(order_date) AS order_day FROM orders;

DATEDIFF() / Subtracting Dates Note: DATEDIFF() is for MySQL. In PostgreSQL, use subtraction.

SELECT order_id, CURRENT_DATE - order_date AS days_since_order FROM orders;

SQL Mathematical Functions

ROUND(): Round Numbers

SELECT ROUND(price, 2) AS rounded_price FROM books;

CEIL() and FLOOR(): Round Up/Down

SELECT order_id, CEIL(total_weight / quantity) AS shipping_cost_per_item FROM 
orders;

MOD(): Get Remainder

SELECT order_id, quantity, MOD(quantity, 2) AS remainder FROM orders;

POWER(): Raise to a Power

SELECT book_name, POWER(price, 3) AS cubed_price FROM books;

ABS(): Absolute Value

SELECT book_name, ABS(price - 500) AS price_difference FROM books;

1: INNER JOIN
INNER JOIN returns only matching rows from both tables. If there is no match between the tables the row is excluded from the result. It is used when you only want records that exist in both tables or want to combine related data.

2: LEFT JOIN
LEFT JOIN returns all rows from the left table and matching rows from the right table. Also known as left outer join. If no match exists null values are returned for the right table columns.

3: RIGHT JOIN
RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match exists null values are returned for the left table columns.

4: FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables. If no match exists nulls appear on the missing side. It is used for combining two datasets and finding mismatches between systems.

5: CROSS JOIN
CROSS JOIN returns the cartesian product - every row from the first table combined with every row from the second table. No matching is required.

WINDOW FUNCTIONS IN SQL.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Subqueries

A subquery (or nested query) is a SQL query written inside another query. It allows you to
perform an operation that depends on the result of another query.

The SELECT clause - Subqueries in the SELECT clause add an extra computed column to the result set. Each subquery executes once for every row of the outer query.

SELECT  
name, 
(SELECT COUNT(*)  
FROM projects  
WHERE projects.employee_id = employees.employee_id) AS total_projects 
FROM employees;

The FROM clause - A subquery in the FROM clause creates a temporary or derived table. The outer query can then select or filter data from it.

SELECT * 
FROM ( 
SELECT  
department_id,  
COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
) AS dept_summary;

The WHERE or HAVING clause - Subqueries in the WHERE clause allow the outer query to filter results based on another query’s output.

SELECT  
department_name 
FROM departments 
WHERE department_id IN ( 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 1 
);

Correlated vs Non-Correlated Subqueries

Non-correlated subquery - A non-correlated subquery does not depend on the outer query. It can be executed on its own.
Correlated subquery - A correlated subquery depends on the outer query. It is evaluated once per row of the outer query.

Common Table Expressions (CTEs)

A CTE (Common Table Expression) is like creating a temporary result set (or virtual table)
that exists only during the execution of a single SQL query.

WITH high_salary AS ( 
SELECT name, salary 
FROM employees 
WHERE salary > 45000 
) 
SELECT * 
FROM high_salary;

CTE with Joins

You can use CTEs to simplify joins

WITH emp_dept AS ( 
SELECT e.name, d.department_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
) 
SELECT * FROM emp_dept;

Stored Procedures

A stored procedure is a named block of SQL logic stored inside the database that you can
execute using: CALL procedure_name(parameters);

Procedures are for actions, such as:

Insert data (add customer, add book, add order)
Update data (update contact, update email)
Delete data (delete customer safely)
Validation and enforcement of business rules
Multi-step operations (create order + check customer + log)

Procedure Syntax

CREATE OR REPLACE PROCEDURE procedure_name(param_name param_type, ...)
LANGUAGE plpgsql
AS $$
BEGIN -- SQL statements
END;
$$;

Explanation of each keyword

CREATE OR REPLACE PROCEDURE add_customer( 
p_first_name VARCHAR, 
p_last_name  VARCHAR, 
p_email      VARCHAR, 
p_contact    VARCHAR, 
p_city       VARCHAR) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
INSERT INTO customers(first_name, last_name, email, contact, city) 
VALUES (p_first_name, p_last_name, p_email, p_contact, p_city); 
END; 
$$;

Explanation

CREATE OR REPLACE PROCEDURE add_customer(...) - Creates a reusable procedure named add_customer.
Parameters like p_first_name VARCHAR - Inputs we pass when calling the procedure.
LANGUAGE plpgsql - Enables procedural execution.
BEGIN - Starts execution block.
INSERT INTO customers(...) - Specifies the table and columns being inserted.
VALUES (...) - Inserts the parameter values.
END;- Ends procedure.
CALL add_customer(...) - Runs the procedure by inserting values into our table.

Delete Customer Safely

CREATE OR REPLACE PROCEDURE delete_customer_safely( 
p_customer_id INT 
) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
DELETE FROM orders 
WHERE customer_id = p_customer_id; 
DELETE FROM customers 
WHERE customer_id = p_customer_id; 
END; 
$$;

Connecting Power BI to SQL databases.

Kelvin — Fri, 13 Mar 2026 11:27:30 +0000

Power BI
Power BI is a business intelligence and data visualization tool that helps organizations collect, analyze and visualize data so as to make better and informed business decisions. It converts raw data from multiple sources into interactive dashboards, charts and reports that are used in decision making.

Power BI is used in data analysis and business intelligence because it allows the users to import data i.e. from MS Excel, databases and cloud services. Power BI has tools like power query which is used for data cleaning and Data Analysis Expressions(DAX) functions which are used to perform calculations. It has charts that are used for data visualization too.

Databases are used by large organizations to store large volumes of data. These databases are connected to Power BI to retrieve the data which is then used for analysis. Companies connect power bi to databases because the provide data security and integrity, can handle large sets of data and provide real time data access which allows dashboards to show regularly refreshed data.

SQL databases are important for storing and managing analytical data because they provide data storage, facilitate data management i.e. inserting new data, updating and deleting outdated data which helps maintain data consistency. databases also facilitate data retrieval which is used for analysis.

Connecting Power BI to a local postgreSQL database.

then open a blank report

from the home tab click on get data

from get data click on database then select postgreSQL database then click on connect.

On the connection window that appears enter the server and database details.

from the database window provide the authentication credentials then click on connect

from the navigator select the data you want to upload then load from the available tables.

wait for the data to load

Connecting Power BI to Cloud Databases.
From your browser search Aiven then create an account or login if having an existing account.
Once on the Aiven console click on services to create a new service

select service type

select service tier, cloud, plan then on service basic enter a name then create service.

open the service and copy the connection information.

open dbeaver to connect to the cloud database
from dbeaver click on database then new database connection

select the database you want to connect to. Ensure it matches the cloud database. Then click on next.

on the connection settings carefully replace them with the connection information copied from the cloud database (Aiven). i.e. host, database, port, username and password then test connection.

after testing the connection then click on finish.

Downloading secure sockets layer (SSL) certificates.
From the Aiven connection information click on the download button. A file named ca.pem is downloaded to your local computer. when connecting on Power BI when configuring SSL the SSL mode should be verify.ca then on root certificate select the downloaded ca.pem file from your downloads then click connect. SSL certificates are important because they encrypt data sent between Power BI and the postgreSQL server, verifies authentication and ensures data integrity between the client and the server.

Why Is an SSL Certificate Required?

When connecting to a cloud database over the internet, your credentials and data travel across public networks. An SSL (Secure Sockets Layer) certificate encrypts this connection, ensuring that:

Your data cannot be intercepted by third parties (man-in-the-middle attacks)
The server you're connecting to is verified and legitimate
Your username and password are transmitted securely
Aiven enforces SSL by default on all connections, which is a security best practice.

Using SSL certificate

step 1 -> download the ssh file from Aiven.
step 2 -> search for (certlm.msc from windows R) or (certmgr from search) then click on >Trusted Root Certification Authorities
right on certificates >> all tasks >> browse the downloaded file from your downloads >> next >> import file name >> next >> then finish.
From Power BI you can now connect to your cloud database
check below

Step 3: Connect in Power BI

Follow the same steps as the local connection:

Click Get Data - PostgreSQL Database
In the Server field, enter your Aiven host:port (e.g., pg-yourservice.aivencloud.com:12345)
Enter your database name
Expand Advanced Options and paste the following into the Additional Connection Parameters field:

sslmode=require;sslrootcert=C:\certs\ca.pem

Enter your Aiven username and password when prompted
Click Connect

Relationships in Power BI.
After loading data to Power BI click on model view to view the relationship between the tables

The primary keys connect with the foreign keys in the other tables.
Data modelling is the process of organizing data tables and defining how they relate to each other so that data can be interpreted correctly. Relationships define how records from one table relate to records in another allowing Power BI to correctly combine data. The common types of relationships are one to many, many to one and many to many. A star schema is the most common structure that is used in Power BI. The star schema contains the fact tables which contain measurable data and Dimension tables which contain descriptive data.

Why SQL skills are important for Analysts.
SQL skills enable analysts to retrieve, organize and prepare data efficiently before using it for visualization and reporting. SQL enables analysts extract specific information from large datasets using queries instead of importing the entire datasets which can be tiresome and time consuming. It also enables analysts to filter relevant records that are needed at a particular time using conditions like where. Aggregation functions like sum, count, avg, max and min help analysts to quickly summarize large datasets and identify trends before building visualizations. SQL also enables joining of tables, removing duplicates, sorting and grouping data which improve Power BI performance.

SQL Joins

Kelvin — Sun, 01 Mar 2026 16:18:49 +0000

Joins are used to combine rows from two or more tables based on a related column between them. This allows you to retrieve connected data stored across multiple tables in a single result.

Types of joins.

WINDOW FUNCTIONS IN SQL.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Translating Messy Data into Action Using Power BI.

Kelvin — Sun, 08 Feb 2026 10:58:03 +0000

Power BI Desktop is a free Windows application where you create reports and
dashboards. You connect your data, clean it, build visualizations, and save your reports.

Power BI Interface.

Ribbon

Home tab: Get data, Transform data, New Measure, Publish.
modelling tab: Manage relationships, Insert tab, Add visuals.
view tab: Control layout, options.
Format tab: Customize visuals.
Insert: used to add visual and design elements to your report page

Fields pane
Displays all data fields in your model.

Visualizations Pane
Select and configure charts, tables, visuals.

Views

Report View: Create and visualize reports.
Table View: View data in tabular format.
Model View: Manage table relationships.

Uploading Excel files on Power BI.

Load the Data
Open Power BI desktop then click on get data then Excel workbook. Select the excel file you intend to use then load the file.

Transform Data
Power Query is used to clean, transform, and prepare raw data before analysis.
From the home tab, click on transform data to open power query.

Basic Transformations

Rename Queries
Give your query a meaningful name for easier reference.
In Power Query right-click the default query name (e.g., Sheet1). Click rename then type a meaningful name like KenyaCropsData or HospitalAdmissionsData.

Remove Rows
Helps clean blank or unwanted rows.

Options under remove rows:

Remove top rows – e.g., remove header rows accidentally repeated.
Remove bottom rows – to remove totals or notes at the bottom.
Remove blank rows – for completely empty rows.
Remove errors – from a column after converting to correct data types.
Remove duplicates – ensure data uniqueness.

Keep Rows

Opposite of "Remove"; helps retain only what you need.

Options under keep rows

Keep Top Rows – e.g., top 10 hospitals or counties.
Keep Bottom Rows
Keep Range of Rows
Keep Duplicates – to analyze duplicate entries.

Filter Rows
Exclude invalid entries like “N/A”, “None”, or “Error”.

Click the filter icon on the column.
Uncheck values like Error, N/A, None.
You can also apply Text Filters, Number Filters, or Date Filters for specific conditions.

Remove Blank or Error Rows

Select the first few rows to check for any completely blank rows or column headers repeated.
Use "Remove Rows" > "Remove Blank Rows" if any are found.
Also use "Remove Errors" on numeric columns like "Yield", "Market Price" etc., after converting them to proper types you can check for blank rows by clicking on the dropdown next to the column name and checking from the list.

Data Types
For each column, set the correct data type:

Text: Farmer Name, County, Crop Type, Season, Crop Variety, Soil Type, Pest Control, etc.
Decimal Number: Planted Area, Yield (Kg), Market Price, Revenue, Cost of Production, Profit.
Whole Number or Decimal: Farmer Code, depending on use.
Date: Planting Date, Harvest Date.
Use Transform > Detect Data Type to auto-detect, then adjust manually if needed.

Rename Columns
Click on each column header and rename using friendly names

Fix typos like Crop Varie to crop variety, farmer Na to Farmer Name.

Handling Errors.

Columns like “Yield”, “Crop Variety”, “Fertilizer Used” have errors like "Error" or invalid values.
For each column click the filter dropdown, deselect "Error", "N/A", or "None" if those aren't valid options.
Or use Replace Values to change "Error" to null.

Replacing problematic values
Use Transform - replace Values:

Replace "Error" with "N/A" or null.
Replace "None" with null (if not meaningful)
"Short Rain" with Short Rains, "Long Rain" with Long Rains for consistency.

Format Text Data

Standardize formatting of names and categories.
Tools under Transform > Format:

Uppercase – for consistency (e.g., county names).
Lowercase
Capitalize Each Word – best for names like "Crop Type", "County".
Trim – removes leading/trailing spaces.
Clean – removes non-printable characters.

Standardize text capitalization.

To capitalize the first letter, whole word in uppercase or lower case, clean or trim.
Use Transform - Format - capitalize each word or select the preferred format.

Remove Duplicates

If each row must be unique (e.g., by Farmer + Crop Type + Season), select those columns then remove duplicates.

Reorder or group columns

You can move related columns together (e.g., financials: Revenue, Cost, Profit).
Click and drag columns to reorder

Remove Columns/Rows

Right click column header then remove
Filter column (e.g. exclude "Paid" rows)

Split Columns

• Split Name column by space to get First and Last Name

Merge Columns

• Select First and Last Name > Merge > Add separator then OK

Group By

Aggregate data (e.g., total Revenue by County).

Select the column to group by (e.g., County).
Go to Transform > Group By.
Choose aggregation: Sum, Count, Average, etc.
Add additional groupings if needed.

Sort

Organize your data logically.

Click column > Home > Sort Ascending or Descending.
Can sort alphabetically (A-Z), numerically, or by date.

How to Deal with Blanks in Power Query

Identify Blank Cells

Blanks show up as:

Empty cells (null values).
Cells with “N/A”, “None”, “Error” — these are not technically blank, but should be treated as such.

How to Find Them:

Click the filter icon on any column.
If blanks exist, you'll see (null) as a filter option.
look for custom errors like "Error", "N/A", "None" especially in text columns.

Replace Blanks with Default or Meaningful Values
Use when the column is important and must not be left empty.

Select the column - go to Transform - Replace Values, Replace null with:

"Unknown" for text, 0 for numbers or "No Data" or "Not Provided" for descriptions.

You can also use Transform > Replace Errors for error-based blanks.

Fill Down or Fill Up
Use when the blank value should be copied from the row above or below.

Example Use Case: If a “County” or “Farmer Name” is listed only once and applies to several
rows.

Select the column.

Go to Transform > Fill > Down or Up.

Remove Rows With Blanks

Use when:

The blank row has missing critical info (like Revenue, Yield, or Crop Type).
The row has too many blanks and is not usable.

After cleaning

Click Close & Apply to load your cleaned data into Power BI.
Always check the resulting table in the data view for issues.
Ensure your work is saved.

DAX

DAX (Data Analysis Expressions) is a formula language used in Power BI to create
calculations and data analysis logic. DAX is used to build measures, calculated columns, and
calculated tables that help transform raw data into meaningful insights.

Aggregation
Aggregation is the process of combining multiple rows of data into a single summarized value.

Types of Aggregation Functions in DAX

Simple aggregation functions (work directly on a column)
Iterator aggregation functions (end with X and work row by row on an expression)

SUM
SUM adds all numeric values in a column.
SUM works on one numeric column and returns the total based on the current filters.
Total Revenue = SUM('Kenya Crops'[Revenue (KES)])
SUMX
SUMX evaluates an expression for each row in a table and then sums those results.
SUMX is used when the value you want to sum is not stored as a single column but must be calculated row by row first.
Total Revenue (SUMX) = SUMX('Kenya Crops', 'Kenya Crops'[Yield (Kg)] * 'Kenya Crops'[Market Price (KES/Kg)])

AVERAGE
AVERAGE calculates the mean of a numeric column.
Average Yield = AVERAGE('Kenya Crops'[Yield (Kg)])
AVERAGEX
AVERAGEX evaluates an expression for each row and then returns the average of those results.
Average Profit per Acre = AVERAGEX('Kenya Crops', DIVIDE('Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)]))

MEDIAN
MEDIAN returns the middle value in a column when the values are sorted.
Median Yield = MEDIAN('Kenya Crops'[Yield (Kg)])

MEDIANX
MEDIANX evaluates an expression for each row and then returns the middle value of the results
Median Revenue = MEDIANX('Kenya Crops', 'Kenya Crops'[Revenue (KES)])

MIN
MIN returns the smallest value in a column.
Minimum Yield = MIN('Kenya Crops'[Yield (Kg)])

MINX
MINX evaluates an expression for each row and returns the smallest result.
Minimum Profit per Acre = MINX( 'Kenya Crops', DIVIDE( 'Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area(Acres)]))

MAX
MAX returns the largest value in a column.
Maximum Yield = MAX('Kenya Crops'[Yield (Kg)])

MAXX
MAXX evaluates an expression for each row and returns the largest result.
Maximum Profit per Acre = MAXX('Kenya Crops', DIVIDE('Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)]))

COUNT counts numeric values in a single column.
COUNTROWS counts the number of rows in a table.
COUNTX counts rows where an expression returns a non-blank value.
ABS (Absolute Value) returns the absolute (positive) value of a number.
POWER raises a number to a given power.
SQRT (Square Root) returns the square root of a number.
MOD returns the remainder after division.

LOGICAL FUNCTIONS IN DAX.

Logical functions in DAX are used to make decisions based on conditions. They allow Power BI
to answer “yes or no” questions, classify data into categories, apply business rules, and control
how results are calculated and displayed.

IF FUNCTION
Evaluates a condition and returns one value if the condition is true and another value if the condition is false.
Profit Status = IF(SUM('Kenya Crops'[Profit (KES)]) > 0, "Profitable", "Loss")

NESTED IF STATEMENTS
A nested IF occurs when one IF function is placed inside another IF. This is used when more than two outcomes are required. Profit Level = IF('Kenya Crops'[Profit (KES)] < 0, "Loss", IF('Kenya Crops'[Profit (KES)] < 50000, "Low Profit", "High Profit"))
This logic first checks for losses. If the farm is not making a loss, it then checks whether profit is
low or high.

IF WITH AND (AND FUNCTION)
The AND function is used when all conditions must be true for a result to be returned.
High Yield & Profitable = IF( AND( 'Kenya Crops'[Yield (Kg)] > 2000, 'Kenya Crops'[Profit (KES)] > 0 ), "Yes", "No")
Here, a farm is marked “Yes” only if it has both high yield and positive profit.

IF WITH && (LOGICAL AND OPERATOR)
The && operator performs the same function as AND, but it is more concise and commonly used in professional DAX code.
High Yield Large Farm = IF('Kenya Crops'[Yield (Kg)] > 2000 &&'Kenya Crops'[Planted Area (Acres)] > 10, "Qualified", "Not Qualified")

IF WITH OR (OR FUNCTION)
The OR function is used when at least one condition must be true.
Risk Category = IF(OR('Kenya Crops'[Profit (KES)] < 0, 'Kenya Crops'[Weather Impact] = "Severe" ), "High Risk", "Normal")
A farm is classified as high risk if it made a loss or experienced severe weather.

IF WITH || (LOGICAL OR OPERATOR)
High Risk Farm = IF('Kenya Crops'[Profit (KES)] < 0 || 'Kenya Crops'[Weather Impact] = "Severe", "High Risk", "Low Risk")

THE SWITCH FUNCTION
The SWITCH function is a cleaner and more readable alternative to nested IF statements. It is
ideal when there are many possible outcomes.
Profit Category = SWITCH(TRUE(), 'Kenya Crops'[Profit (KES)] < 0, "Loss", 'Kenya Crops'[Profit (KES)] < 50000, "Low Profit", 'Kenya Crops'[Profit (KES)] < 200000, "Medium Profit", "High Profit")

NOT FUNCTION
The NOT function reverses a logical condition.
Irrigation Type = IF(NOT('Kenya Crops'[Irrigation Method] = "Irrigated"), "Rain-fed", "Irrigated")

ISBLANK FUNCTION
The ISBLANK function checks whether a value is blank.
Yield Availability = IF(ISBLANK('Kenya Crops'[Yield (Kg)]), "Missing", "Available")

CALCULATE FUNCTION
The CALCULATE function is the most important function in DAX. It evaluates an expression under a modified filter context.
In simple terms, CALCULATE changes “what data is being used” for a calculation.
Maize Revenue = CALCULATE( SUM('Kenya Crops'[Revenue (KES)]), 'Kenya Crops'[Crop Type] = "Maize")

CALCULATE WITH MULTIPLE FILTERS
CALCULATE can accept more than one filter. All filters are combined using AND logic,
meaning all conditions must be true.
Maize Long Rains Revenue = CALCULATE(SUM('Kenya Crops'[Revenue (KES)]), 'Kenya Crops'[Crop Type] = "Maize", 'Kenya Crops'[Season] = "Long Rains")

DAX TEXT FUNCTIONS

They help clean text, create readable labels, combine fields, and standardize values for analysis and
reporting.
CONCATENATE / CONCATENATEX
CONCATENATE joins two text values into one. Used to create readable labels, combine fields, or build descriptive columns.
County Crop = 'Kenya Crops'[County] & " - " & 'Kenya Crops'[Crop Type]

LEFT, RIGHT, MID

LEFT extracts characters from the start.
RIGHT extracts characters from the end.
MID extracts text from the middle.

Extract first 3 letters
Crop Code = LEFT('Kenya Crops'[Crop Type], 3)

Extracts last 4 digits
Farmer Code Short = RIGHT('Kenya Crops'[Farmer Code], 4)

UPPER, LOWER, PROPER

UPPER → all caps
LOWER → all lowercase
PROPER → first letter capitalized

Standardize county names
County Clean = UPPER('Kenya Crops'[County])
Make crop types readable
Crop Type Clean = PROPER('Kenya Crops'[Crop Type])

LEN, TRIM, CLEAN

LEN counts characters
TRIM removes extra spaces
CLEAN removes hidden non-printable characters

Removes extra spaces
County Cleaned = TRIM('Kenya Crops'[County])
Checks text length
County Length = LEN('Kenya Crops'[County])

DATE AND TIME FUNCTIONS

Date and time functions are used to work with dates, extract parts of dates, and perform date
based calculations.

DATE, YEAR, MONTH, DAY

DATE creates a date
YEAR extracts the year
MONTH extracts the month
DAY extracts the day

Extracts tear from planting date
Planting Year = YEAR('Kenya Crops'[Planting Date])

TODAY and NOW

TODAY returns today’s date
NOW returns current date and time

TIME INTELLIGENCE FUNCTIONS

Time intelligence functions allow you to compare performance across time periods.

DATEADD

DATEADD shifts the current date context backward or forward.
Revenue Last Year = CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, YEAR))

DATEDIFF
Calculates the difference between two dates
Growth Days = DATEDIFF( 'Kenya Crops'[Planting Date], 'Kenya Crops'[Harvest Date], DAY )

SAMEPERIODLASTYEAR
Returns the same period as the current one, but last year.
Revenue last year = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))

TOTALYTD, TOTALMTD, TOTALQTD

Year-to-date
Month-to-date
Quarter-to-date

Year-to-date revenue
Revenue YTD = TOTALYTD([Total Revenue], 'Date'[Date])
Month-to-date profit
Profit MTD = TOTALMTD( [Total Profit], 'Date'[Date])

Dashboards

A dashboard is a single-page, interactive summary view of your most important data and KPIs.
Dashboards helps you monitor performance, track metrics, make quick decisions and share insights easily.

From Report view on visualizations select the presentation you would like to use. i.e. column charts, pie charts, slicers, bar charts, tables, etc.
From data select the data you want on the x and y axis. i.e. county, Total revenue, Harvest date.

Add KPIs using the card visualization and slicers using the slicer.
Create a dashboard by copying visualizations from one sheet to the Dashboard sheet.
Ensure your dashboard is not redundant. The visualizations should show different comparisons and should have a theme.

Schemas and Data modelling in Power BI.

Kelvin — Sun, 01 Feb 2026 16:56:17 +0000

In Power BI, schemas and data modelling are about how you structure tables and define relationships so your data is accurate, fast, and easy to analyze.

A schema describes how tables are organized and related. It is framework that defines how data is structured, organized, and related within a system.

Data modelling is the process of connecting multiple data sources, defining relationships between them, and creating calculations to transform raw data into a structured model.

Dimensional schemas

Star schema.
Snowflake schema.

Star schema.

The Star Schema is the industry standard data modelling approach for Power BI. It gets its name from its visual layout: a central fact table surrounded by dimension tables, resembling a star.

Components of a Star schema

Fact table stores quantitative or measurable data.
Dimension table stores descriptive data e.g. date, product name.
Relationships describe how fact tables and dimension tables are connected and how filters flow between them. Features of Dimension Tables.

Fields.
Primary Keys
Every record in a dimension table is uniquely identified by its main key.

Attributes
These are descriptive fields that provide context.
Features of Fact Tables

Foreign Keys
The keys connect to dimension tables and offer context for the measures.

Measures
The provided figures are numerical data that quantify business performance indicators, such as sales income and units sold.

Relationships in star schema

One-to-Many - one row from a table is linked to many rows in another table.
One-to-One - Each row in one table matches one row in another table, and vice versa.
Many-to-One - many rows in a table relate to one row in another table.
Many-to-Many - multiple rows from one table match multiple rows in another table.

Normalization and Denormalization.

Normalization is the term used to describe data that's stored in a way that reduces redundancy.
Denormalization is the process of flattening related dimension data into single wide tables to improve analytical performance and simplify reporting.

Snowflake schema.
A snowflake schema is a variation of the star schema where dimension tables are normalized into multiple related tables instead of being kept as one wide table.

Key Features of snowflake schema

Normalized dimension tables - dimension tables are normalized into third normal form (3NF) or higher splitting them into smaller and granular sub tables.
Hierarchical structure - dimensions are organized into hierarchical layers (e.g. product - category -manufacturer) which naturally represents complex business relationships.
Reduced data redundancy - by eliminating repeated values in dimension tables, this schema significantly reduces the risk of data inconsistencies.

Components of snowflake schema.

Fact table is the core of the schema storing the primary quantitative data or measures of business processes. It contains multiple foreign keys that link directly to the primary dimension tables. It captures events at their most atomic level, typically resulting in the largest table in the warehouse.
Normalized dimension tables that surround the fact table and provide descriptive context for the recorded facts. Unlike the star schema these tables are normalized meaning repeated descriptive values are moved into separate tables reducing redundancy.
Lookup function that acts as the first level of description for the fact table.
Sub dimension tables are formed by further splitting or snowflaking the main dimension tables.
Hierarchical layers represent multiple levels of a hierarchy. e.g. a product dimension may link to a category table which then links to a manufacturer table.
Maintenance enhance data integrity because updates to a category name that only need to happen in one record rather than multiple records.

Key fields in Snowflake schema.

Primary keys are unique identifiers in dimension and sub dimension tables e.g. ProductID.
Foreign Keys are fields in the fact table or dimension tables that reference a primary key in a related table to form a join.
Surrogate keys are system generated integers used as keys instead of natural business keys to improve performance.

Importance of good modelling.

Proper modelling prevents logical errors in relationships that can lead to double counting or missing data.
Clear naming conventions and hidden technical fields make the model easier for non technical users to navigate.
Simple relationships between a central fact table and surrounding dimension tables reduce ambiguity.
Centralized dimension tables ensure every visual uses the same definitions preventing different charts from showing conflicting totals for the same metrics.
A modular model allows you to add new data sources or dimensions without rebuilding the entire system.

Introduction to MS Excel for data analytics

Kelvin — Fri, 23 Jan 2026 17:24:07 +0000

Overview.

Microsoft Excel is a spreadsheet program used to organize, analyze, calculate, and visualize data in rows and columns. Its core structure uses cells, where data is entered, allowing for complex calculations and data insights, making it essential for professionals in many fields.

Excel interface.

Excel's user interface is designed for efficiency, with a grid-based workspace where data is organized into rows and columns.
A workbook is the file containing multiple tabs at the bottom
A worksheet is a grid of cells identified by column letters A,B,C and row numbers 1,2,3.forming addresses like A2,b3.

Ribbon is the top toolbar with tabs like Home, Insert, Formulas, Data, and Review.
The Formula bar displays the content of the selected cell.
The Name box shows the active cell's address or named ranges.
The Status bar at the bottom, it provides quick stats like sum or average of selected cells.

Removing duplicates.

In Excel, you can remove duplicates by selecting your data, going to the Data tab, clicking Remove Duplicates, choosing the columns to check, and confirming to keep only unique records

Conditional formatting.

Conditional formatting in Excel lets you automatically change the appearance of cells (colour, icons, data bars) based on rules, making patterns and issues easy to spot. You apply it by selecting your data, going to Home then conditional Formatting, choosing a rule such as highlighting values greater than a target, and setting the desired format.

Freeze panes.

Freeze Panes in Excel keeps specific rows or columns visible while you scroll through your worksheet, making large datasets easier to read. You use it by selecting a cell below the row and to the right of the column you want to keep visible.

Data filter

A data filter in Excel lets you display only the rows that meet specific criteria, making it easier to analyze large datasets. You apply it by selecting your data and clicking Data then Filter, then using the dropdown arrows in the column headers to filter by values, text, numbers, dates, or conditions.

Data sorting.

Data sorting in Excel arranges data in a specific order—such as A to Z, Z to A, smallest to largest, or by date—to make information easier to analyze. You sort data by selecting a column, going to Data then Sort, choosing the sort order and if needed multiple levels for more complex sorting

Excel functions.

Microsoft Excel functions are predefined formulas that perform specific calculations or operations on data. They are essential for data analytics, enabling users to manipulate, analyze, and visualize datasets efficiently without writing custom code from scratch

Types of excel functions.

Aggregate functions

Aggregate functions summarize multiple rows of data into a single meaningful value.
sum =SUM(B2:B100)

average =AVERAGE(B2:B100)

maximum =MAX(B2:B100)
minimum =MIN(B2:B100)
count =COUNT(B2:B100)
Conditional aggregation
Conditional aggregation is the process of summarizing data only when specific conditions are met.
SUMIF() aggregates values based on a single condition.
SUMIFS() aggregates values based on multiple conditions.
COUNTIF Used to count records that meet one or more conditions.
COUNTIFS Used to count records that meet multiple conditions.

Logical functions

logical functions automate decision making.
IF
If function performs a logical test and returns value if the test is true.
=IF(J2>6, "high performance", "low performance")
Nested IF
A nested if is simply an if formula inside another if formula. It lets you test multiple conditions.
=IF(R2>30,"Highly Experienced",IF(R2>20,"Moderately Experienced",IF(R2>10,"Low Experience","Very Low Experience")))
And
And function returns value when two conditions have to be met.
=IF(AND(R2>30,W2>10),"assign bonus", "do not assign")
Or
Or function returns value when either of the conditions have been met
=IF(AND(G2>1/1/2006, L2>6), "promoted", "not promoted")

Lookup functions.

VLOOKUP (vertical lookup) function is used to look up a value in the first column of a table and return a related value from another column.
=VLOOKUP(10871, A2:AA877, 5, FALSE) 5 is column index number.
HLOOKUP (Horizontal Lookup) is used when your lookup values are in the top row of a table and you want to return a value from a row below.
=HLOOKUP(10011, A1:AGS21, 4, FALSE)
10011 is data in a cell
INDEX
Index function returns the value from a actual position in a range.
=INDEX(G2:G877, MATCH(10871, F2:F877, 0))
MATCH
Match function Finds the position of a value in a range

Text functions.

Trim removes extra spaces. =TRIM(A2)
Len returns length of a string.=LEN(A2)
Left extracts the leftmost characters of a string. =LEFT(A2,4)returns first 4 characters.
Right function extracts the rightmost characters of a string. =RIGHT(A2,2) returns last 2 characters.
Mid extracts characters from the middle of a string.
=MID(b2,2,3)
Concatenation used to combine two texts.
=CONCAT(C2, " ", C3

Date and Time functions.

Now function returns the current date and time and updates automatically.
=NOW()
today function returns the current date
=today()
end of month returns end of 3 months =eomonth(f2,3) i.e. 22/1/2026 returns 30/4/2026
network function returns difference between two days excluding weekends.=networkdays(k2,j2)
date difference returns difference between two dates. =datedif(k2,j2, "M")
edate returns today's date added 3 months =edate(f2,3) ie 22/1/2026 returns 22/4/2026

Pivot tables, Pivot charts & Dashboards

Pivot table.

A Pivot Table is an Excel tool used to summarize, analyze, and explore data quickly without writing formulas.
To insert a pivot table on excel click on the data you want to create a pivot table then click on insert on the toolbar, then click on pivot table.

From the pivot table fields on the right, drag fields to the desired area i.e. filter, rows, columns, and values.

Slicers.

A Slicer is a visual filter that lets you interactively filter data in Pivot Tables and using clickable buttons.

Pivort charts.

Pivot Charts are charts that are directly linked to Pivot Tables. When the Pivot Table changes the chart updates automatically.
From the pivot table analyze menu click on the pivot chart and select the desired chart.

Dashboards.

A Dashboard is a single screen report that shows key metrics and insights using charts, PivotTables, and visuals so decision makers can understand performance at a glance.
To create a dashboard create a new worksheet, rename it to Dashboard. open the worksheet and select a wide area or ctrl+A then fill with your desired background colour. Copy your pivot charts and slicers from the separate worksheets and paste them on your dashboard worksheet. Add shapes to add text e.g. "dashboard title" then resize them to the desired sizes.

GIT BEGINNERS GUIDE.

Kelvin — Wed, 14 Jan 2026 17:07:48 +0000

Version control

version control is a system that records changes to files over time making it easier to track code, restore and collaborate safely.

Git

Git is a distributed version control system used for code collaboration, code restoration, tracking code changes and who made them.

Git commands

git --version

git --version checks whether git is installed on your computer and what version is installed.

git config --user.name"yourname"

git config --user.name"yourname" sets the author's name linked to the commits.

git config --user.email"youremail"

git config --user.email"youremail" sets the email address linked to your commits.

git config --list

git config --list shows all git configuration settings currently applied to the system.

ls ~/.ssh

ls ~/.ssh looks for SSH folder in the home folder.

ssh-keygen -t ed25519 -C"youremail"

--
ss-keygen -t ed25519 -C"youremail" generates private and public SSH keys.

eval "$(ssh-agent -s)"

eval "$(ssh-agent -s)" starts the ssh agentand sets the necessary environment variables in the current terminal session.

--unset

--unset is used to remove a setting.

ssh-add ~/.ssh/id_ed25519

ssh-add ~/.ssh/id_ed25519 allows git to use your key automatically without asking for the passphrase every time.

git init

git init creates a hidden .git folder that git uses to track changes.

git add .

git add . tells git to stage all changes in the current directory and its sub directories so they are ready to be committed.

Push commands.

git push

git push is used to send local commits to a remote repository.

git push -u

git push -u is used to push the current branch and set its tracking branch.

git push --all

git push --all pushes all the local branches to the remote directory.

git push --tags

git push --tags pushes all the local git tags to the remote repository.

git push --force

git push --force forces the local branch to overwrite the local branch.

pull commands.

git pull

git pull gets changes from a remote repository and merges them into the current branch.

git pull origin main

git pull origin main fetches the latest changes from the remote origin and merges the main branch into the current local branch.

git pull --rebase

git pull --rebase updates the branch by replaying the local commits on top of the latest remote commit.

git fetch

git fetch downloads the latest changes from a remote repository without modifying the local branches.

git fetch --all

git fetch --all fetches updates from all remotes without changing the local branches.

git pull --ff

git pull --ff tells git to update the branch using a fast forward merge only.

git fetch

git fetch downloads the latest changes from a remote repository without changing the local branches.

git fetch --all

git fetch -- all fetches updates from all every remote without changing the local branches.

Creating directory commands

mkdir

mkdir is a command used to create a new directory.

mkdir -m

mkdir -m is used to create a directory and set its permission at the same time.

mkdir -p

mkdir -p creates a directory and any missing parent directories.

cd

cd (change directory) is used to navigate into a git repository before running git commands.

cd ~

cd ~ moves you to the home folder.

cd -

cd - takes you back to the previous directory you were in.

cd /

cd / command moves you to the root directory of the file system.

cd "my folder"

cd "my folder" is used to change into a directory whose name contains spaces.

Tracking changes

git status

git status shows the current state if the working directory and staging area.

git diff

git diff is used to see the exact changes between files, commits, branches or stages in git.

git log

git log is used to view the commit history of a git repository. what was committed by whom and when.

git blame

git blame is used to see who last modified each line of a file, when, and in which commit.

git show

git show is used to display details of a git object. It lets you see what changed who changed it and when.

Luxdev markdown language class

Kelvin — Tue, 13 Jan 2026 10:49:45 +0000

How to write a markdown language

This is the first markdown language the students have learnt

The first thing that the students learnt

The students have learnt how to write a heading

lastly but not least

they told me they love tech I told them I enjoy tech too

morning class
evening class

m = 8

visit my x acc on

Forem: Kelvin

Advanced SQL for Data Analytics:

WINDOW FUNCTIONS IN SQL.

Subqueries

Correlated vs Non-Correlated Subqueries

Common Table Expressions (CTEs)

CTE with Joins

Stored Procedures

Procedures are for actions, such as:

Procedure Syntax

Explanation of each keyword

Explanation

Delete Customer Safely

Introduction to Python

Python installation.

checking the version of python installed in your pc

Python Syntax.

Key Syntax Rules in Python.

Python Variables.

Rules of naming variables.

Best Practices.

Assigning Multiple Variables

Python Constants.

Python Data Types.

Python’s Built-in Data Types.

1. Numeric Types

2. Text Type

3. Boolean Type

Arithmetic Operators in Python

comparison operators

Logical operators (and,or,not)

sql and databases

What is a database.

What is SQL

Schema in SQL

Why use schemas.

Creating a schema.

Setting search path.

Creating a table in that schema.

Types of Database management systems(dbms).

Types of databases.

SQL datatypes

Main data types categories.

1. Numeric Types

2. Character Types

3. Date & Time Types

4. Boolean

Creating tables in SQL

Basic syntax

Constraints

Best practices

Inserting Data into a database.

Viewing and querying Tables.

Viewing databases

Viewing all tables in PostgreSQL

View data from a table.

Table manipulation and common SQL key words.

Add a column to a table

Updating existing data

Updating a single row

Updating multiple rows with a CASE statement.

Updating specific quantity

Delete columns from SQL

Remove column from a table.

Delete rows from SQL

Delete a specific row

Drop a table

Rename column

MODIFY COLUMN TYPE - Change a Column’s Data Type

SET DEFAULT Value for Column

DROP DEFAULT Value

Add a NOT NULL Constraint

Drop a NOT NULL Constraint

Add a Foreign Key Constraint

Drop a Foreign Key Constraint

COMMON SQL KEYWORDS

1. SELECT - Retrieve Data

2. WHERE - Filter Data

3. ORDER BY - Sort Data

4. GROUP BY - Group and Summarize Data

2. Comparison Operators - Used to compare values, often in `WHERE`.