DEV Community: rose odiwuor

The SQL Time Machine: How to use LAG(), LEAD(), FIRST_VALUE() & LAST_VALUE() to analyse business performance

rose odiwuor — Mon, 17 Aug 2026 21:35:22 +0000

Introduction

SQL window functions perform calculations across a set of rows related to the current row, without collapsing results into a single aggregated row. They are defined using the OVER() clause, which can include PARTITION BY (to group rows) and ORDER BY (to define row order within each group).

Basic Syntax:

SELECT column_name1,
      window_function(column_name2)
      OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) 
AS new_column
FROM table_name;

Unlike standard aggregates, window functions retain individual rows while adding calculated values such as rankings, running totals, or moving averages.

select 
    driver_id,
    sum(fare) as total_revenue,
    rank() over(order by sum(fare) desc) as revenue_rank
from safari.trips
group by driver_id;

We have 3 categories of window functions in SQL.

Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
Rank Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(),NTILE()
Value Window Functions: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()

The main focus will be on the last category - Value Window Functions, and how we incorporate them to answer key business questions.

Main Purpose of Value Functions

They enable us access a value from another row in order to do a comparison, without having to join tables or do self joins.
i.e compare current row values with values of the previous, next, first and last rows.

1. LAG()

Used to access a value from the previous row within a window/ checks the previous value

lag(fare) over(partition by driver_id order by trip_date) as previous_fare

returns the fare from the previous trip the driver took.

2. LEAD()

Used to access a value from the next row within a window/ checks the next value

lead(sales, 2, 0) over(order by month)

returns the sales value 2 months from the current row value.

sales -> expression
2 -> offset; get the value of sales two months ahead
0 -> default; if there's no corresponding value, return 0 instead of null

If there is no previous/next value we get a NULL for that row.

3. FIRST_VALUE()

Returns a value from the first row within a window

first_value(fare) over(partition by driver_id order by trip_date) as first_fare

returns the first ever fare recorded by the driver

4. LAST_VALUE()

Returns a value from the last row within a window

last_value(fare) over(partition by driver_id order by trip_date 
rows between current row and unbounded following) as last_fare

returns the last fare from the driver

Syntax

All the 4 functions accept all data types i.e integer, text, float etc
Partition clause is optional
Frame clause is not allowed for LAG() & LEAD(), optional for FIRST_VALUE() and required for LAST_VALUE().
Frame clause is especially important in LAST_VALUE() to get the correct results.
Default frame in FIRST_VALUE & LAST_VALUE is "range between unbounded preceding and current row" which works for FIRST_VALUE but not LAST_VALUE, hence the need to define its frame.

Here's how Value Functions help answer real business questions

Time Series Analysis

Analyze data to understand patterns, trends and behavior over time. e.g YoY, MoM sales performance.
The value functions will help analyze overall growth/decline of the business performance over time.

Sample business question:
How is revenue changing month by month? What are our best and worst months?

with monthly_revenue as (
    select
    travel_month,
    sum(total_fare) as total_revenue
    from safari_connect.v_clean_trips
    group by 1)
select
    travel_month,
    total_revenue,
    lag(total_revenue) over (order by travel_month) as prev_month_revenue,
    total_revenue - lag(total_revenue) over (order by travel_month) as mom_rev_change,
    round(100.0 * (total_revenue - lag(total_revenue) over (order by travel_month))
    / lag(total_revenue) over (order by travel_month), 2) as mom_pct_change
from monthly_revenue
order by travel_month;

The result >>>

The best month, using the month-on-month percentage change, is September 2024 and the worst month is January 2025.

Customer retention analysis

Measure a customer's behaviour & loyalty to help businesses build strong relationships with customers
Here we can understand customer's behaviour and loyalty by knowing how long it takes for each customer to buy/place their next order using lead(), and how it changes from one period to another.

Employee / Workforce Analysis

We could analyze metrics such as;

Employee salary changes over time, using LAG() - compare an employee's current salary with previous salary.
Salary at next review, using LEAD() - compare current salary with employee's upcoming salary.
This would help with staff costs planning and budgeting.
Performance changes, using FIRST_VALUE() - compare an employee's current rating/KPI/sales with their starting value.

Conclusion

Value window functions are more than just SQL techniques for moving between rows, they provide a way to gain insights on change and performance within data.
Once you understand how to look back, look ahead and compare values within a window, you can use these functions to answer business questions that go beyond "What happened?" to "Where were we, where are we going, where did we start and where did we end?"

Connecting Power BI to Local and Cloud-based PostgreSQL

rose odiwuor — Sat, 11 Jul 2026 09:43:16 +0000

Introduction

Power Bi is a data visualization tool that transforms raw data into meaningful and interactive insights, whereas PostgreSQL is a database management system that uses the SQL(structured query language) to store and interact with data from the databases.

Local PostgreSQL means that the database is hosted locally on my computer while a cloud-based stores the databases on cloud data platforms.

We therefore connect Power BI to PostgreSQL so that data from the databases can be analyzed, visualized and turned into meaningful reports and dashboards.
Instead of exporting data every day, Power BI retrieves the data directly from the database, saving time and reducing errors.

Connection Process

For a local host, download and install PostgreSQL.

For the cloud based;

create an account in Aiven - which is a Database-as-a-Service platform
download and install DBeaver - a database management tool

From Aiven you'll get the required connection information as below;

After successful connection, DBeaver is as below;

Data Importation

To import data to DBeaver, on the left side expand on databases > defaultdb > schemas > either create a new schema or use the public schema

Right click on your preffered schema and click import data. Brings you to the window below

Go to -> Input file(s), Browse and select your data

Click Proceed at the bottom, till its done. After importing is done you should see

Voila, you just imported a file to aiven database.

Connecting Power BI to the Database

Now we have the file in a database. In order to work on the data and visualize it, we connect our visualization tool to the database.

Open Power BI Desktop on your computer
Click Get Data in the Home ribbon and select More

In the dialog box, search for PostgreSQL database under the Database category and click Connect

Input server and database details gotten from the connection information on Aiven.
Server -> Host:Port
Database -> Database name

After a successful connection, check the data under navigator.
You'll get a preview and you can choose either to load or transform the data on Power BI.

To connect to the local postgreSQL, the only difference is the connection information used.
Server -> localhost or the IP address
Database -> Database name

Conclusion

With these steps in place, you are well-equipped to leverage PostgreSQL data in Power BI for interactive analysis and informed decision-making.

Joins, Relationships and Schemas in Power BI

rose odiwuor — Mon, 29 Jun 2026 21:23:37 +0000

Joins

Imagine you have two separate tables with a common column and you'd like to merge them into one table.
Joins help in merging such kind of tables by using the "Merge Queries" feature in Power BI.

Types of Joins

1. Inner Join

Returns only rows with matching values in both tables. If there is no match, the row is not included in the result.

2. Left Join / Left Outer Join

Returns all records from the left table and matching rows from the right table. If there is no match, the result will contain null values for columns from the rights table.

3. Right Join / Right Outer Join

Returns all records from the right table with matching values from the left table. If there is no match, the result will contain null values for columns from the left table.

4. Full Join / Full Outer Join

Returns all records from both tables, with matching values from both sides where available. If there is no match, the result will contain null values for columns from the table that lacks a match.

5. Left Antijoin

Returns records from the left table that don't have matches on the right table.

6. Right Antijoin

Returns records from the right table that don't have matches on the left table.

Relationships

Managing relationships in Power BI is crucial for connecting multiple table and enabling accurate data analysis and visualization.
Relationships define how tables interact.
A model relationship relates one column in a table to one column in a different table.

Cardinality

Each model relationship is defined by a cardinality type. It defines how rows in one table relate to rows in another table.

Types of cardinality

One-to-many(1:*) and Many-to-one(*:1) - are essentially the same and the most common. The "one" side means the column contains unique values; the "many" side means the column can contain duplicate values.

One-to-one(1:1) - Both columns, in the different tables, contain unique values. It isn't common, and it likely represents a suboptimal model design because of the storage of redundant data.
Many-to-many(:) - Both columns can contain duplicate values. This cardinality type is infrequently used.

Schemas

This is how facts and dimension tables are structured after modelling.
-> Facts table - records of what happened
-> Dimension table - contains a common column with the facts table

Star schema

Has one facts table with multiple dimension tables

Snowflake schema

It's more like star schema (one facts table with multiple dimension tables), but one dimension table will not be directly related to the facts table.

Conclusion
By understanding joins, relationships and schemas in Power BI, we can create meaningful connections between datasets and generate reliable insights for better decision-making.
Mastering these fundamentals provides a strong foundation for developing powerful and interactive Power BI reports.

HOW EXCEL IS USED IN REAL-WORLD DATA ANALYSIS

rose odiwuor — Sun, 07 Jun 2026 20:20:41 +0000

What is Excel?

Everything we do nowadays forms part of data that can be useful to an organization or individual. This leads to the need for data collection. Excel therefore helps us to organize this data in rows and columns, stores the data and analysis can be done to generate insights.

Uses of Excel in real-world Data Analysis:

Sales data from a supermarket can be used to determine fast moving products and those that bring in most revenue; from which they ensure adequate stock of those two categories.
Customer feedback to an organization can be analyzed to get information on areas that need improvement, what is working well and even suggest what products/services they'd like to be introduced.
We use excel to track daily, monthly, annual expenditures of organizations and individuals. As an individual you can get to know how much you spend on different categories and how you can best optimize your income and expenses.

Excel features & formulas learned so far:

Data types we have in excel are numeric and alphabetic. Numeric data types include currency, percentage, date. Alphabetic data are basically in text format.
Formula bar is a toolbar that is used to enter, view and edit cell contents.
Inserting a new row/column by selecting a row/column, right click and select insert.
The categories of functions;
1. Aggregate / Mathematical functions - SUM(), AVERAGE(), MIN(), MAX()
2. Statistical functions - COUNT(), COUNTIF(), COUNTIFS()

Personal Reflection:

How has learning Excel changed the way I see data?

Usually when presented with a large dataset I can 'freeze' from the sight of it all and wonder where to start from. But learning excel and its features ensures that I understand the process of data collection, cleaning, analysis, visualization and generating insights.