DEV Community: Wesley Clark

Joins and window functions

Wesley Clark — Mon, 02 Mar 2026 11:33:00 +0000

Joins
A Join is used to combine rows from two or more tables based on a related column between them.

For example, imagine:

A Customers table containing customer details.

Types of Joins

INNER JOIN

Returns only the rows that have matching values in both tables.

SELECT c.customer_name, o.order_id
FROM Customers c
INNER JOIN Orders o
    ON c.customer_id = o.customer_id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. If no match exists, NULL values are returned.

SELECT c.customer_name, o.order_id
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id;

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table.

SELECT c.customer_name, o.order_id
FROM Customers c
RIGHT JOIN Orders o
    ON c.customer_id = o.customer_id;

FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. Non-matching rows get NULLs.

SELECT c.customer_name, o.order_id
FROM Customers c
FULL JOIN Orders o
    ON c.customer_id = o.customer_id;

Why Joins Matter

Joins allow you to:

Combine normalized data
Create meaningful reports
Connect transactional data with master data
Avoid duplicate data storage

Window Functions
A Window Function performs calculations across a set of table rows that are related to the current row — without collapsing the result into a single row (unlike GROUP BY).

Common Window Functions

ROW_NUMBER() Assigns a unique number to each row within a partition.

SELECT employee_name,
       department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM Employees;

Employees are ranked within their department by salary.

RANK() Gives ranking but allows ties (skips numbers).

SELECT employee_name,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM Employees;

DENSE_RANK()
Similar to RANK but does not skip numbers.
SUM() as a Window Function

SELECT employee_name,
       department,
       salary,
       SUM(salary) OVER (PARTITION BY department) AS department_total
FROM Employees;

Shows total department salary on every row without grouping the data.

Running Total Example

SELECT order_date,
       sales_amount,
       SUM(sales_amount) OVER (ORDER BY order_date) AS running_total
FROM Sales;

Joins (In Simple Terms)
Think of joins like merging two Excel sheets using a common column (like ID). If two sheets share a customer ID, you can combine the details into one view.
Without joins, relational databases would not be useful because data is stored separately to avoid duplication.

Window Functions (In Simple Terms)
Window functions are like saying:

“Calculate something for a group, but don’t hide the individual rows.”

For example:

Instead of showing total salary per department (GROUP BY),

You show each employee’s salary AND the department total beside it.

It’s like giving each row extra intelligence.

How Analysts Translate messy Data, DAX, and Dashboards into Action using Power BI

Wesley Clark — Sun, 08 Feb 2026 09:01:58 +0000

Tools like Power BI, analysts turn messy data, complex DAX formulas, and dense dashboards into insights that actually drive decisions.

Messy data (Chaos to Structure)
Analysts often face:

Missing or inconsistent values
Duplicate records
Multiple data sources with conflicting definitions
Poorly formatted dates, currencies and text fields Use of Power Query in Power Bi where analysts clean and transform data before analysis starts.

Modeling data
Once data is clean analysts design a data model that reflects how the business actually works.
Power BI encourages the use of:

Fact tables(sales, transactions, events)
Dimension tables(customers, products, time)

Using DAX to turn numbers into Business Logic
DAX allows analysts to:

Define KPIs (Revenue Growth, Customer Retention, Profit Margin)
Handle time intelligence (YoY, MoM, rolling averages)
Apply complex business rules consistently

Designing dashboards
Effective Power BI dashboards:

Start with key questions, not visuals
Highlights trends, not just totals
Use filters and drill downs intentionally
Guide the user toward insight not confusion

Turning Insights into Action
Power BI enables this by:

Updating data automatically
Sharing dashboards securely across teams
Enabling real time monitoring of KPIs
Allowing users to explore dat without breaking it

Schemas and data modelling in Power Bi

Wesley Clark — Sun, 01 Feb 2026 13:12:57 +0000

What is data modelling?

It is the process of connecting different data sources, defining how they relate to one another and analyzing them so that the y can be analyzed differently.

What is a schema?

Is the visual pattern or layout of your model. It is the blueprint of how your Fact and Dimension tables are arranged and linked.

Types of Schemas

1. Star schema
In this set up, a single Fact table sits in the center, surrounded by Dimension Tables that connect directly to it.

Why it works;

Performance: Power BI is optimized for this structure. It minimizes the number of joins required to filter data
Simplicity: Its intuitive for users to navigate.
Usability: Data Analysis Expressions measures are easier to write when the model is a star.

2. Snowflake schema
A snowflake schema occurs when a Dimension table is further normalized meaning it connects to another dimension table rather than directly to the Facts table.

Snowflake schema saves a bit of storage space by reducing the data redundancy, it hurts performance in Power BI.

Managing Relationships

Relationships are the pipes through which filters flow.
Rules followed to keep your model healthy:

Cardinality; This is where one row in a dimension table matches to many rows in a Fact table.
Cross filter direction; Keep this set to Single by default. Setting it to bi can create circular dependencies and cause numbers to calculate in ways you didn't intend.
Active vs Inactive; You can only have only one active relationship between two tables. If you need to connect a Sales table to a Date table via both "Order Date" and "Ship date", use an inactive relationship for the second one and activate it using the DAX function USERELATIONSHIP

Why Good Modelling is Critical

Speed: Power BI uses a "columnar" engine. It can scan millions of rows in a Star Schema instantly, but it struggles with wide, messy tables or complex "snowflake" chains.
Accuracy: If your relationships are set up incorrectly (e.g., using "Many-to-Many" when you should use "One-to-Many"), your totals and averages will be wrong.
Simplicity: A clean model means you don't have to write 20-line formulas to get a simple Sales-Year-Over-Year calculation.

Introduction to MS Excel for Data Analytics

Wesley Clark — Sat, 24 Jan 2026 19:57:04 +0000

Microsoft Excel is one of the most powerful and accessible tools for basic data analysis. Data analysis is the process of collecting, cleaning, organizing, and making sense of information to help you make better decisions.

Microsoft Excel is a spreadsheet that allows you to:

Organize your data

The Header row data is categorized into Employee ID, First name, Last name, Department and Salary.
Clearing and Explaining Data
Sort & Filter are tools used for cleaning and exploring.

Go to the data tab and Click filter and then select any category you want e.g Department (HR and Finance) and you will only see data from the checked category.

Clearing data by use of Remove duplicates

Basic calculations using formulas

=SUM(E2:E12)     Total Salary
=AVERAGE(E2:E2)  Average Salary
=COUNT(E2:E12)   No. of Values
=MAX(E2:E12)     Highest salary
=MIN(E2:E12)     Lowest salary

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tv4m98w841pz33bl3i7t.png

Creating and visualization of charts

Use of pivot tables

Creation of charts

Conclusion

Excel is a essential in data analytics by learning how to organize data, apply formulas, create charts and use pivot tables.

How to push and pull code, track changes and understand version control using Git

Wesley Clark — Sat, 17 Jan 2026 18:08:42 +0000

Introduction

What is version control?

Version control is a system that records changes to a file or set of files over time so that you can recall specific versions later.

Why use Git

Collaboration- Multiple people can work on the same project without overwriting each other's code.
History- One can see who exactly changed what and why.
Experimentation- One create branches to try new ideas without breaking the main project.

Tracking changes

Main areas of a Git project

Working directory- Where you are currently editing your files.
Staging area- Which changes you want to include in your next save point.
Local repository- Where Git permanently stores the history of your project.

Work flow

Modify a file in your directory
Stage the file(git add)
Commit the file(git commit)

Pushing code

When you have finished making "commits" on your computer and you want to share them with the world, you Push(upload).

Pulling code

If a teammate makes changes to the code on GitHub, your local computer won't know about it automatically. You need to pull those changes down(refresh or sync).

Essentials Commands Cheat Sheet

Action Command
Initialize git init
Check Status git status
Stage Changes git add <file>
Commit git commit -m "message
Push git push origin main
Pull git pull origin main