DEV Community: Maurine Nyongesa

Financial Risk Analysis in Excel: How I Used COUNTIFS, AVERAGEIF & Pivot Tables to Uncover Loan Default Patterns

Maurine Nyongesa — Wed, 18 Mar 2026 06:25:44 +0000

Tool: Microsoft Excel | Dataset: 1,000 loans | Period: 2018–2023

Overview

This project is a comprehensive loan portfolio analysis built entirely in Excel. The goal was to understand how borrower characteristics influence loan issuance, default behavior, and overall portfolio risk — and to answer five specific credit risk questions that financial institutions deal with daily:

What Is the Overall Health of the Loan Portfolio? — 37.5% of 1,000 loans ended in default or charge-off. That number alone demands investigation.
Does Loan Grade Reliably Predict Default Risk? Grade A and B loans recorded 0% bad loan rates. Grade D hit 79%. Grade E reached 100%.
Which Borrower Characteristics Drive the Highest Risk? Credit score below 650 produced a 77–100% bad loan rate. DTI above 30% pushed default rates to nearly 45%.
Does Repayment Period Influence Default Probability? 48-month loans carried the highest bad rate at 39.6%, marginally ahead of 36-month (38.8%) and 60-month (34.1%) loans.
Has Underwriting Quality Changed Over Time? Bad loan rates fluctuated between 33.3% (2021) and 41.5% (2023), with 2023 marking the worst recent vintage.

Through segmentation analysis, pivot modeling, and time-trend evaluation, this analysis simulates how a financial institution would assess portfolio health, risk concentration, and lending strategy — combining issuance behavior, risk drivers, and repayment performance into a single unified framework.
The objective was not just to visualize data. It was to let the data answer something.

The Dataset

1,000 loan records across 13 columns, covering two things: who the borrower is, and what happened to the loan.

Column	What It Tells Us
`Loan_ID`	Unique loan identifier
`Issue_Date`	When the loan was issued (2018–2023)
`Repayment_Period (Months)`	Loan term — 36, 48, or 60 months
`Loan_Amount`	Principal borrowed (USD)
`Interest_Rate (%)`	Annual interest rate on the loan
`Annual_Income`	Borrower's declared yearly income (USD)
`Debt_to_Income_Ratio (%)`	Monthly debt obligations as % of gross income
`Employment_Length (Years)`	Years employed at time of application
`Home_Ownership`	Rent / Own / Mortgage
`Loan_Purpose`	Medical, Car, Business, Education, Home Improvement, Debt Consolidation
`Credit_Score`	Borrower credit score at issuance (550–800)
`Loan_Grade`	Lender-assigned risk grade — A (lowest risk) to E (highest)
`Loan_Status`	The target variable — Fully Paid / Default / Charged Off

What This Analysis Covers

Portfolio Overview — baseline KPIs: total loans, volume, fully paid rate, bad loan rate, average credit score, interest rate, and income.
Risk Segmentation — bad loan rates broken down by loan grade, credit score band, DTI bracket, loan purpose, and home ownership.
Repayment & Issuance Trends — year-over-year portfolio performance from 2018–2023 and default rates across 36, 48, and 60-month terms
Grade × Status Cross-Tabulation — how each loan grade distributes across Fully Paid, Default, and Charged Off outcomes.
Recommendations — data-backed actions for underwriting, risk concentration, and portfolio strategy All analysis was done in Microsoft Excel only — no Python, no SQL, no Power BI. Just formulas, pivot tables, conditional formatting, and charts.

Setting Up the Analysis — Helper Columns First

Before writing a single summary formula, two helper columns were added directly to the raw data sheet. These columns powered every calculation that followed.

Helper Column 1 — Bad Loan Flag

A new column called Bad_Loan was created using:

=IF(OR(M2="Default",M2="Charged Off"),1,0)

This assigned a 1 to every loan that defaulted or was charged off, and a 0 to every fully paid loan. Because the column contains only 1s and 0s, taking the average of this column at any level of segmentation instantly returns the bad loan rate as a decimal.

Helper Column 2 — Issue Year

A Year column was extracted from the Issue Date column for all trend analysis:

=YEAR(B2)

Every segmentation formula then followed one of these patterns:

-- Overall bad loan rate
=AVERAGE(N2:N1001)

-- Segmented bad loan rate (one condition)
=AVERAGEIF(criteria_range, criteria, N2:N1001)

-- Segmented bad loan rate (multiple conditions)
=AVERAGEIFS(N2:N1001, criteria_range_1, criteria_1, criteria_range_2, criteria_2)

Note on AVERAGEIF vs AVERAGEIFS: The argument order is different and easy to mix up.

AVERAGEIF → range, criteria, average_range (average range last)

AVERAGEIFS → average_range, criteria_range, criteria (average range first)

Finding 1 — The Portfolio is Significantly Non-Performing

The first question was straightforward: what does the overall portfolio health look like?

Formulas used:

-- Count per status
=COUNTIF($M$2:$M$1001,"Fully Paid")
=COUNTIF($M$2:$M$1001,"Default")
=COUNTIF($M$2:$M$1001,"Charged Off")

-- Percentage of portfolio
=COUNTIF($M$2:$M$1001,"Fully Paid")/COUNTA($A$2:$A$1001)

-- Bad loan rate (using helper column)
=AVERAGE($N$2:$N$1001)

What this tells us: More than one in three loans in this portfolio failed to perform. A 37.5% bad loan rate would trigger immediate portfolio review in any real lending institution. But an overall rate alone doesn't tell you where the problem is — that requires segmentation.

Finding 2 — Loan Grade is a Near-Perfect Risk Predictor

Loan grade is a risk classification assigned by the lender at issuance — A being the safest and E the most risky. It factors in credit score, income, employment, and DTI. The question was whether the grade system actually predicted outcomes.

Formulas used:

-- Bad loan rate per grade
=AVERAGEIF($L$2:$L$1001,"A",$N$2:$N$1001)

-- Count per grade
=COUNTIF($L$2:$L$1001,"A")

-- Average credit score per grade
=AVERAGEIF($L$2:$L$1001,"A",$K$2:$K$1001)

-- Average interest rate per grade
=AVERAGEIF($L$2:$L$1001,"A",$E$2:$E$1001)

What this tells us: The grade system worked almost perfectly as a predictor. Grades A and B — 380 loans combined — produced zero bad loans. Grade C had just one charge-off out of 203. Then the cliff edge. Grade D collapsed to 79% and Grade E hit an absolute 100%. Not a single Grade E loan was repaid.

The most alarming detail however is not the default rates — it's the interest rates. Despite Grade E borrowers defaulting at 100%, their average interest rate (15.22%) was barely higher than Grade A (14.82%). The lender identified the risk through grading but failed to price it accordingly. Riskier borrowers should carry significantly higher rates to compensate for expected losses.

Finding 3 — Credit Score is the Single Strongest Default Predictor

Credit score represents a borrower's creditworthiness based on their historical borrowing and repayment behavior. A band analysis was conducted by first creating a helper column:

-- Credit band helper column
=IFS(K2<600,"550-600",K2<650,"600-650",K2<700,"650-700",K2<750,"700-750",K2<=800,"750-800")

Formulas used:

-- Bad loan rate per credit band
=AVERAGEIF($O$2:$O$1001,"550-600",$N$2:$N$1001)

-- Count per credit band
=COUNTIF($O$2:$O$1001,"550-600")

What this tells us: A credit score of 650 functions as a near-perfect binary cutoff. Below 600 — every single borrower defaulted or was charged off. The 600–650 band was nearly as bad at 76.7%. Cross 650 and the bad loan rate drops to exactly 0% — and stays there all the way to 800. No other variable in this dataset draws such a clean, decisive line between performing and non-performing loans. A hard underwriting rule requiring a minimum credit score of 650 would have eliminated the vast majority of bad loans in this portfolio.

Finding 4 — DTI Ratio is a Consistent Risk Escalator

Debt-to-Income ratio measures a borrower's monthly debt obligations as a percentage of their gross monthly income. The higher the DTI, the more financially stretched the borrower. A band analysis was conducted using:

-- DTI band helper column
=IFS(G2<=10,"0-10",G2<=20,"10-20",G2<=30,"20-30",G2<=40,"30-40",G2>40,"40+")

Formulas used:

-- Bad loan rate per DTI band
=AVERAGEIF($P$2:$P$1001,"0-10",$N$2:$N$1001)

-- Average income per DTI band
=AVERAGEIF($P$2:$P$1001,"0-10",$F$2:$F$1001)

What this tells us: Unlike credit score which produces a sharp binary cutoff, DTI tells a gradual story. As debt burden increases the bad loan rate climbs steadily — from 27.1% at the lowest band to 44.9% at 30–40%. Critically, average credit scores were nearly identical across all DTI bands (668–676), confirming that DTI and credit score measure different dimensions of risk. A borrower can have an acceptable credit score but still be dangerously overleveraged — and this portfolio shows overleveraging carries real consequences regardless of score.

Finding 5 — No Loan Purpose is Safe

Formulas used:

-- Bad loan rate per purpose
=AVERAGEIF($J$2:$J$1001,"Medical",$N$2:$N$1001)

-- Bad loan count per purpose
=COUNTIFS($J$2:$J$1001,"Medical",$N$2:$N$1001,1)

What this tells us: Across all six loan purposes bad loan rates ranged from 34.8% to 40.8% — a narrow 6 percentage point band.

Medical loans carried the highest risk, likely because borrowers taking medical loans are often already under financial stress at application. The narrow spread means loan purpose alone cannot be used to make reliable lending decisions. It needs to be combined with credit score and grade to add meaningful predictive value.

Finding 6 — Home Ownership Adds Almost No Predictive Value

Formulas used:

=AVERAGEIF($I$2:$I$1001,"Rent",$N$2:$N$1001)
=AVERAGEIF($I$2:$I$1001,"Own",$N$2:$N$1001)
=AVERAGEIF($I$2:$I$1001,"Mortgage",$N$2:$N$1001)

What this tells us: The spread between all three groups is just 4.7 percentage points. Counterintuitively, outright homeowners performed worst of the three — directly challenging the conventional lending assumption that homeownership signals financial stability. In isolation home ownership is not a reliable risk indicator in this portfolio and should never be used as a standalone lending criterion.

Finding 7 — The Portfolio Has Been Deteriorating Since 2021

The trend analysis used the Year helper column combined with COUNTIFS and AVERAGEIFS to evaluate performance year over year.

Formulas used:

-- Loan count per year
=COUNTIF($O$2:$O$1001,A2)

-- Total volume per year
=SUMIF($O$2:$O$1001,A2,$D$2:$D$1001)

-- Bad loan rate per year
=AVERAGEIF($O$2:$O$1001,A2,$N$2:$N$1001)

-- Fully paid % per year
=COUNTIFS($O$2:$O$1001,A2,$M$2:$M$1001,"Fully Paid")/COUNTIF($O$2:$O$1001,A2)

-- Year over year bad rate change
=IFERROR((C3-C2)/C2,"-")

What this tells us: 2021 was the portfolio's strongest year — lowest bad loan rate (33.3%), highest fully paid rate (66.7%), and highest average credit score (682). From 2022 onwards the portfolio deteriorated consistently. By 2023 the bad loan rate hit 41.5% — the worst in the entire dataset — with the default rate alone reaching 24.5%, the highest single year figure across all six years. Average credit scores in 2022 and 2023 dropped back to 666, almost identical to 2018 levels. This pattern strongly suggests underwriting standards loosened after 2021.

Finding 8 — Grade D and Grade E Fail Differently

The cross-tabulation was built using a single COUNTIFS formula written once and dragged across the entire table:

-- Core cross-tabulation formula
=COUNTIFS($L$2:$L$1001,$A3,$M$2:$M$1001,B$2)

The $A3 locks the column but lets the row move. B$2 locks the row but lets the column move — allowing the formula to be written once and dragged across all grade and status combinations automatically.

% Breakdown (referencing count table):

-- % of row total =B3/$E3

What this tells us: Both Grade D and E record 100% bad loan rates but fail in completely different ways. Grade D loans predominantly charge off at 72.2% — the lender writes the debt off as unrecoverable in nearly three quarters of cases.

Grade E flips this pattern — 89.2% default while only 10.8% charge off, meaning the lender still pursues recovery on most Grade E failures. Since charge-offs represent total losses while defaults leave some recovery options open, Grade D is arguably more damaging to the lender than Grade E despite both recording identical bad loan rates.

Recommendations

Enforce a Hard Credit Score Floor of 650 The data is unambiguous. No borrower above 650 defaulted. No borrower below 600 repaid. Implementing a minimum credit score of 650 as a hard underwriting rule would eliminate the vast majority of bad loans. 577 of 1,000 loans already sit above this threshold — meaning this rule would not dramatically reduce origination volume while significantly improving portfolio quality.
Restrict Origination to Grades A, B, and C Grades A, B, and C combined produced just one bad loan out of 583. Grades D and E produced 374 bad loans out of 417. Concentrating origination in the top three grades while either eliminating or dramatically repricing Grades D and E would transform this portfolio's performance. At minimum, Grade D and E interest rates need to reflect the actual level of risk — not sit within 0.5% of Grade A rates.
Investigate and Address the Post-2021 Deterioration The consistent worsening of bad loan rates from 2021 to 2023 — combined with declining average credit scores — points to a loosening of underwriting standards in recent years. The 2023 default rate of 24.5% is the highest in the dataset. Without intervention the trajectory points toward a 2024 bad loan rate exceeding 41.5%. Understanding what changed in underwriting policy after 2021 is the most urgent question this portfolio raises.

Conclusion

This analysis set out to answer one question — what does the data tell us about why loans go bad? The answer turned out to be surprisingly clean. Two variables dominate everything else: credit score and loan grade. Both draw near-perfect lines between performing and non-performing loans. DTI adds a secondary layer of independent risk. Everything else — purpose, home ownership, repayment period — contributes marginally at best.

The broader lesson for any data analyst working in financial risk is this: averages deceive, segmentation reveals. The average borrower in this portfolio looks reasonable — $82,924 income, 672 credit score, 9.8 years employed.

But segment by grade and credit score and the picture changes completely. The risk is not spread evenly across the portfolio. It is concentrated, identifiable, and largely predictable — which means it is also largely preventable.

Dataset sourced from a financial risk portfolio simulation.
Here is the Dataset: Financial Dataset

Mastering DAX in Power BI: A Beginner’s Guide

Maurine Nyongesa — Sun, 10 Nov 2024 00:27:12 +0000

Introduction

1. What is DAX?

DAX (Data Analysis Expressions) is the formula language used in Power BI, Excel Power Pivot, and SQL Server Analysis (SSAS). It is designed to handle data manipulation and computations, allowing for powerful data models and analysis.

Example Scenario: Imagine you have a dataset of retail transactions. You want to calculate total sales, profit margin, or year-over-year growth. DAX enables you to write formulas that generate these values dynamically, adapting to filters and user selections in your reports.

It plays a vital role in enhancing the reporting and analysis capabilities of Power BI, enabling users to dive deeper into their data by creating custom insights.

DAX is the primary used for creating calculated columns, calculated measures and managing relationships between data tables.

This guide introduces essential DAX functions and concepts to get you started with creating powerful calculations in Power BI.

2. Essential DAX Concepts and Functions

Calculated Columns vs. Measures

Understanding the difference between calculated columns and measures is crucial when working with DAX:

Calculated Columns: These are computed row-by-row and added to your data model as new columns. They are useful for creating static calculations.

Example: Calculating a Profit column in the Sales table:

 Profit = Sales[Revenue] - Sales[Cost]

Measures: Measures are dynamic calculations that aggregate based on the current filter context, which means they change based on the data you view in your reports. Measures are great for calculations like sums, averages, and ratios.

Example: Calculating total sales as a measure:

 Total Sales = SUM(Sales[Amount])

3. Key DAX Functions Categories with examples

3.1 Aggregate Functions:

SUM: Adds up all the values in a specific column.

Example: This DAX formula calculates the total sales amount from the SalesAmount column of the Sales table.

 Total Sales = SUM(Sales[SalesAmount])

AVERAGE: Returns the average of all values in a column.

Example:

 Average Sales = AVERAGE(Sales[SalesAmount])

This computes the average sales amount for all rows in the Sales table.

COUNT: Counts the number of non-blank cells in a column.

Example:

 Number of products = COUNT(Products[ProductID])

This counts the number of non-empty ProductID entries in the Products table.

SUMX: This performs row-by-row calculations for each record in a table and then returns the total sum of these calculations.

Example:

 Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

This formula multiplies Quantity by UnitPrice for each row and sums the result across all rows.

3.2 Filter Funtions:

CALCULATE: Modifies the filter context of an expression, allowing you to customize the filters applied to your calculations.

Example:

 Sales in West = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

This calculates the total sales only for the west region, regardless of any other filters applied.

FILTER: Returns a table containing only rows that satisfy a given condition.

Example:

 Expensive Products = FILTER(Products, Products[Price] > 100)

This filters out products that cost more than 100, returning only those rows.

ALL: Ignores any filters that might be applied to a column or table.

Example:

 Total Sales All Regions = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Region]))

This calculates the total sales without considering any filters on the Region column.

RELATED: Retrieves values from a related tables using relationships.

Example:

 Product Name = RELATED(Products[ProductName])

This pulls the product name from the Products table into the Sales table, assuming a relationship exists between the two tables.

3.3 Time Intelligence Functions:

SAMPLEPERIODLASTYEAR: Compare data from the same period in the previous year.

Example:

 Sales Last Year = CALCULATE(SUM(Sales[SalesAmount]),SAMPLEPERIODLASTYEAR(Sales[Date]))

This formula calculates the total sales for the same period last year.

TOTALYTD: Calculates the year-to-date total measure.

Example:

 YTD Sales = TOTALYTD(SUM(Sales[SalesAmount]), Sales[Date])

This gives the total sales from the beginning of the year to the current date.

DATEADD: Shifts dates in a date column by a specified number of days, months, or years.

Example:

 Sales Previous Month = CALCULATE(SUM(Sales[SalesAmount]),  DATEADD(Sales[Date], -1, MONTH))

This moves the date back by one month and calculates the sales for that period.

3.4 Logical Functions:

IF: Evaluates a condition and returns one values if the condition is TRUE and another if it is FALSE.

Example:

 SalesCategory = IF(SUM(Sales[SalesAmount] > 1000,"High","Low")

This classifies sales into "High" or "Low" categories based on whether the SalesAmount is greater than 1000

AND: Returns TRUE if all conditions are TRUE

Example:

 Big Discount = IF(AND(Sales[Quality] > 50, Sales[Discount] > 10), "Yes","No")

This checks if both conditions (quantity greater than 50 and discount greater than 10 are met, then labels them "Yes" or "No".

OR: Returns TRUE if atleast one conditions is TRUE

Example:

 Special Offer = IF(OR(Sales[Quality] > 100, Sales[Discount] > 20), "Special","Regular")

Thsi checks if either quantity is greater than 200 or the discount is greater than 20.

3.5 Text Functions

CONCATENATE: Joins two or more strings into one.

Example:

 Full Product Name = CONCATENATE(Products[ProductName],"-", Products[Category])

This joins the product name and category with a hyphen in between.

UPPER: Converts text to uppercase.

Example:

 Upper Product Name = UPPER(Products[ProductName])

This converts the product name to uppercase.

LEFT/RIGHT: Extracts a specified number of characters from the left or right side of a text string.

Example:

Left Part of Name = LEFT(Products[ProductName], 5)

This returns the first 5 characters from the left of the product name.

5.6 Mathematical Functions:

DIVIDE: Performs division and handles division by zero.

EXAMPLE:

 Price Per Unit = DIVIDE(Sales[Amount], Sales[Quantity])

This calculates the price per unit, handling cases where the quantity might be zero.

MOD: Returns the remainder of a division operation.

Example:

 Remainder = MOD(Products[Quantity],2)

This returns the remainder when dividing the quantity by 2 (Useful for checking odd or even numbers)

Conclusion:

DAX provides a powerful set of functions that allow you to create dynamic, context-aware calculations in Power BI. By using aggregate, filter, time intelligence, and logical functions, you can craft precise and flexible reports and dashboards. It's essential to understand how these functions interact with your data model, especially in terms of context and relationships, to build complex and insightful calculations.

Employee Management System Using SQL

Maurine Nyongesa — Sun, 13 Oct 2024 09:44:48 +0000

Introduction

The Employee Management System is an SQL-based project that streamlines managing employee and department data, tracks performance reviews, and generates insights through queries. It uses key SQL concepts like database creation, table relationships, joins, and window functions to analyze employee performance, salary, and department metrics. This system ensures efficient data management and provides insights for strategic HR decision-making.

Project Overview

Database Creation: employee_management
Tables
departments: Stores information about departments (name, location).
employees: Stores employee data (personal details, hire date, salary, job title, department).
performance_reviews: Tracks performance reviews with ratings and comments.

Database Creation

We begin by creating the employee_management database, which stores employee, department, and performance review data.

-- CREATING DATABASE
CREATE DATABASE employee_management;
USE employee_management;

Table Creation

Next, we create three tables: departments, employees, and performance_reviews, each linked via foreign keys to model relationships between employees, their departments, and performance reviews.

-- CREATING TABLES
CREATE TABLE departments(
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees(
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    department_id INT,
    job_title VARCHAR(50),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE performance_reviews(
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    review_date DATE NOT NULL,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    comments TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Data Insertion

Once the tables are created, we can insert sample data into the departments, employees, and performance_reviews tables.

-- INSERTING DATA
-- Departments Table
INSERT INTO departments(department_name, location) VALUES
    ('Sales', 'New York'),
    ('HR', 'Los Angeles'),
    ('IT', 'San Francisco');

-- Employees Table
INSERT INTO employees(first_name, last_name, email, hire_date, salary, department_id, job_title)
VALUES
    ('Maurine', 'Nyongesa', 'maurine.nyongesa@gmail.com', '2021-03-15', 55000, 1, 'Sales Executive'),
    ('Asa', 'Moh', 'asa.moh@gmail.com', '2020-06-30', 60000, 2, 'HR Manager'),
    ('Steve', 'Jobs', 'steve.jobs@gmail.com', '2019-08-10', 75000, 3, 'IT Specialist');

-- Performance Reviews Table
INSERT INTO performance_reviews(employee_id, review_date, rating, comments) VALUES
    (1, '2023-06-15', 4, 'Great performance but needs improvement in client interaction.'),
    (2, '2023-05-12', 5, 'Excellent management skills, highly recommended for promotion.'),
    (3, '2023-07-20', 3, 'Satisfactory performance but needs more technical training.');

Querying Data

Below are SQL queries used to retrieve, update, and analyze the data in the Employee Management System.

Basic Queries
Retrieve all employee details:

SELECT * FROM employees;

Update an employee's salary:

UPDATE employees
SET salary = 65000
WHERE employee_id = 1;

Add a new employee:

INSERT INTO employees (first_name, last_name, email, hire_date, salary, job_title, department_id)
VALUES ('Jane', 'Doe', 'jane.doe@email.com', '2023-05-15', 55000, 'Marketing Specialist', 2);

Advanced Queries

Joining Tables: Retrieve all employees with their department names.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Salary Analysis: Find employees with a salary above 60,000.

SELECT first_name, last_name
FROM employees
WHERE salary > 60000;

Performance Review: Retrieve performance reviews for a specific employee.

SELECT e.first_name, e.last_name, p.review_date, p.rating, p.comments
FROM employees e
JOIN performance_reviews p ON e.employee_id = p.employee_id
WHERE e.employee_id = 1;

Salary Analysis by Department: Calculate the average salary for each department
This Query the average salary for each department and shows it alongside each employee's information.

SELECT AVG(e.salary) AS average_salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Employee Ranking by Salary:

SELECT first_name, last_name, salary,
       RANK() OVER (ORDER BY salary) AS salary_rank
FROM employees;

Cumulative Salary:

SELECT first_name, last_name, salary,
       SUM(salary) OVER (ORDER BY salary) AS cumulative_salary
FROM employees;

Row Number Based on Hire Date:
This assigns a row number to each employee, sorted by the date they were hired.
It’s useful for scenarios where you want to track the order of employee hiring.

SELECT first_name, last_name, hire_date,
       ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM employees;

Performance Review Trend using Lead and Lag Functions:
This query shows an employee's current review rating along with the previous and next ratings (if they exist).
It uses the LAG() function to pull the previous review and LEAD() to pull the next review, within the same employee’s records

SELECT employee_id, review_date, rating,
       LAG(rating) OVER (PARTITION BY employee_id ORDER BY review_date) AS prev_rating,
       LEAD(rating) OVER (PARTITION BY employee_id ORDER BY review_date) AS next_rating
FROM performance_reviews;

Percentile Ranking of Salaries:
This query calculates the percentile rank of each employee’s salary compared to others.

SELECT first_name, last_name, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank_salary
FROM employees;

Conclusion

The Employee Management System demonstrates how SQL can be leveraged to efficiently manage and analyze employee and department data. By utilizing core SQL concepts such as database creation, table relationships, and advanced queries like window functions and joins, this system offers valuable insights into employee performance, salary distributions, and department metrics. This project showcases the powerful capabilities of SQL in organizing and extracting data, aiding
in data-driven decision-making for HR and management.
You can explore more of my work on LinkedInand find the complete repository for this Employee Management System project on Github