DEV Community: PHILIP KAPLONG (Sirphilip)

SQL Joins and Window Functions- Ultimate Guide

PHILIP KAPLONG (Sirphilip) — Tue, 03 Mar 2026 11:25:15 +0000

SQL JOINS

What is a JOIN?

A JOIN is used to combine data from two or more tables based on a related column. Think of it like connecting two lists: one of employees, another of departments. You use a common field (like department_id) to link them.

Types of Joins

INNER JOIN

Returns only the rows that have matching values in both tables.
Example: Only employees assigned to a department will appear; employees without a department are ignored.
Syntax:

SELECT name, department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table.
If there’s no match in the right table, it returns NULL.
Example: All employees appear, even if they don’t belong to any department.

SELECT name, department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left table.
If there’s no match in the left table, it returns NULL.
Example: All departments appear, even if no employee is assigned.

SELECT name, department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

FULL OUTER JOIN

Returns all rows from both tables, filling in NULLs where no match exists.
Example: Employees without departments and departments without employees will all appear.

SELECT name, department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;

CROSS JOIN

Returns every possible combination of rows from both tables.
Example: 3 employees × 3 departments = 9 rows.
Use with caution as it can create very large results.

SELECT *
FROM employees
CROSS JOIN departments;

NATURAL JOIN

Automatically joins tables using all columns with the same name.
You don’t write the ON condition.
Example: Employees and departments both have department_id. SQL will join them automatically using this column.

SELECT *
FROM employees
NATURAL JOIN departments;

Warning: If a new column with the same name is added later, the join might change results unexpectedly. Use explicit joins in real systems for safety.

WINDOW FUNCTIONS

What is a Window Function?

A window function performs calculations across rows related to the current row while keeping all original rows intact. Unlike GROUP BY, which reduces rows to summaries, window functions add new computed columns without removing data.

Basic Syntax

FUNCTION() OVER (
    PARTITION BY column
    ORDER BY column
)

OVER() → indicates a window function.
PARTITION BY → divides data into groups.
ORDER BY → sets the order of rows within each group.

Ranking Functions

ROW_NUMBER()

Assigns a unique number to each row in the result.
No ties are allowed; every row is unique.

SELECT name,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

RANK()

Assigns the same rank to ties, but skips numbers for the next rank.
Example: Two employees with the same salary share rank 1, next rank becomes 3.

SELECT name,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK()

Assigns the same rank to ties but does not skip numbers.
Example: Two employees share rank 1, next rank is 2.

SELECT name,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

SUM() OVER() — Running Totals

Calculates cumulative totals while keeping all rows.
Example: Adding salaries cumulatively for a payroll report.

SELECT name,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;

PARTITION + ORDER TOGETHER

You can rank or sum values within a group.
Example: Ranking employees per department by salary.

SELECT name,
       department_id,
       salary,
       RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS dept_rank
FROM employees;

Ranking resets for each department.
Useful for finding top performers per group.

When to Use What

JOINS → Combine multiple tables for connected data.
GROUP BY → Summarize data, reduces rows.
WINDOW FUNCTIONS → Keep rows and perform calculations like ranking, running totals, or comparisons within groups.

Key Takeaways

Joins connect tables:

INNER → only matching rows
LEFT → keep all left rows
RIGHT → keep all right rows
FULL → keep everything
NATURAL → automatic join on same-named columns

GROUP BY → reduces rows to summary data.
Window Functions → keep all rows, add extra calculations:

PARTITION BY → group rows inside the function
ORDER BY → controls calculation order
ROW_NUMBER / RANK / DENSE_RANK → ranking functions
SUM() OVER → running totals

How Analysts Turn Messy Data into Clear Answers with Power BI

PHILIP KAPLONG (Sirphilip) — Mon, 09 Feb 2026 17:34:40 +0000

Imagine your data is a pile of papers on a messy office desk, reports, all mixed up. A data analyst’s job is to tidy that desk, find the important information, and use it to tell a story that helps a company make good decisions. Power BI is one of the favorite tool for doing exactly that.

Let’s break down the three-step magic trick they perform: cleaning data, using a special language called DAX, and building dashboards.

Step 1: Taming the "Messy Data"

Real-world data is rarely perfect. It comes from different places like spreadsheets, databases, and websites. You might have duplicates, misspelled names, blank cells, or dates formatted wrong. This is what we call "messy data."

In Power BI, the first stop is usually Power Query Editor. Think of this as a data cleaning workshop. Here, the analyst can:

Remove duplicates (like the same sales entry listed twice).
Fill in blanks or remove empty rows.
Standardize text
Split columns (like separating a full name into "First Name" and "Last Name"). This process is called data transformation. The goal is to turn that messy pile into a neat, organized table that Power BI can understand and work with efficiently.

Step 2: The Superpower of DAX (Data Analysis Expressions)

Once the data is clean, the analyst needs to calculate things. Basic math like sums and averages is easy. But what if you need to answer a specific question like, "How much did sales grow from last month?" or "What is the year-to-date total for each salesperson?"

This is where DAX comes in. DAX is a simple formula language used in Power BI. It looks a lot like Excel formulas, but it’s built for analyzing relationships between data.

DAX in Simple Terms:
Let’s say you have a Sales table. A simple DAX formula to calculate total sales would be:
Total Sales = SUM(Sales[Amount])
This creates a new measure called "Total Sales" that adds up every number in the Amount column.

A more powerful DAX formula might calculate sales from the previous month:
Sales Last Month = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))`
This formula takes our "Total Sales" measure but changes the "filter" to look at the previous month automatically.

DAX is the analyst’s tool for creating the custom metrics that answer the business’s most important questions. You don’t need to be a programmer to learn basic DAX—it’s about learning to ask the data the right questions.

Step 3: Building the "Single Source of Truth" Dashboard

Clean data and smart calculations are useless if no one can understand them. The final step is visualization—building the dashboard.

A Power BI dashboard is a one-page summary of your most important metrics, often called KPIs (Key Performance Indicators). It uses simple, powerful visuals:

Bar/Column Charts: To compare things (e.g., sales by region).
Line Charts: To show trends over time (e.g., monthly website visitors).
Gauges & KPI Cards: To show a number and its target (e.g., Current Revenue vs. Goal).
Slicers: These are like interactive filters. Click "Q2" on a slicer, and every chart on the page instantly updates to show only data from the second quarter.

The magic here is interactivity. A good dashboard isn’t a static report. A manager can click on a bar for "Product A" and instantly see which customers bought it and which salesperson sold it. They can drill down from a yearly summary to a weekly view.

This turns the dashboard into a conversation tool. Instead of asking, "Can you get me the Q3 report for the Midwest?" and waiting days, the user can find the answer in seconds by clicking on the dashboard.

From Action to Impact

So, how does this translate into action?

1.Clean Data provides trust. Decision-makers can be confident they’re looking at accurate information.
2.DAX Measures provide insight. They answer the "why" and "what’s next" by showing trends, growth, and performance against goals.
3.The Interactive Dashboard provides clarity and speed. It puts insights in front of the right people in an understandable way, enabling fast, informed decisions.

Real-World Example: A store manager sees on their daily Power BI dashboard that a specific product’s sales have suddenly dropped in one location. With a few clicks, they drill down and see a spike in customer returns for that item at that store. They immediately call the store, discovering a damaged shipment. They can then stop the sale, address the quality issue, and protect customer satisfaction—all within minutes.

In short, Power BI analysts are translators. They take the messy, technical language of raw data and translate it into a clear, visual story that anyone in the company can understand and act upon. They bridge the gap between information and action, helping businesses move from asking "What happened?" to knowing "What should we do next?"

Schemas and Data Modeling in Power BI: The Complete Beginner-to-Intermediate Guide

PHILIP KAPLONG (Sirphilip) — Sun, 01 Feb 2026 17:42:33 +0000

If you want your Power BI dashboards to be fast, accurate, and scalable, you must understand schemas and data modeling.

Many beginners jump straight into visuals and DAX, but the real power of Power BI lies in how well your data is structured behind the scenes.

This article breaks down schemas and data modeling in a simple, practical way—with real examples and best practices.

1. Understanding Schemas in Power BI

What is a Schema?

A schema is the logical structure of your data. It defines:

What tables exist

What columns each table contains

How tables are connected

Think of a schema as the blueprint of a building. Without a good blueprint, the building may stand—but it will be weak, slow, and unreliable.

2. What is Data Modeling?

Definition

Data modeling is the process of designing how data is structured, stored, and related so it can be analyzed efficiently.

It involves:

Identifying fact and dimension tables

Defining relationships

Optimizing structure for performance

Preparing data for reporting and DAX calculations

In Power BI, data modeling happens mainly in the Model View.

3. Fact Tables vs Dimension Tables

Before understanding schemas, you must understand these two core concepts.

Fact Tables

A fact table stores measurable, numeric data.

Examples of facts:

Sales amount
Quantity sold
Profit
Discounts

Example:

Sales Fact Table

OrderID
CustomerID
ProductID
DateID
Quantity
Revenue
Profit

Characteristics:

Very large
Contains foreign keys
Stores transactional data

4. Types of Schemas in Business Intelligence

4.1 Star Schema

The star schema is the most common data model in Power BI.

Structure:

One central fact table
Multiple dimension tables connected directly to it
The layout looks like a star

Example:

Fact Table: Sales

OrderID, CustomerID, ProductID, DateID, Revenue, Quantity

Dimension Tables:

Customer, Product, Date, Geography

Key Benefits:

Fast performance

Simple to understand

Works well with DAX

Recommended by Microsoft

4.2 Snowflake Schema

The snowflake schema is a more complex version of the star schema where dimensions are split into multiple related tables.

Example:

Customer → City → Region → Country

Product → Category → Department

Pros:

Reduces redundancy

Saves storage

Cons:

More complex

Slower queries

4.3 Galaxy Schema

A galaxy schema has multiple fact tables sharing the same dimensions.

Example:

Fact tables: Sales, Inventory, Finance
Shared dimensions: Date, Product, Customer

5. Power BI Data Modeling Best Practices

Prefer star schema
Use a dedicated date table
Avoid many-to-many relationships
Remove unnecessary columns
Use clear naming (e.g., DimCustomer, FactSales)

6. Why Data Modeling Matters

Good data modeling leads to:

Faster dashboards
Accurate insights
Scalable models
Easy-to-use reports

Simply put:
A strong model = powerful analytics.

How I Started Doing Data Analysis with MS Excel

PHILIP KAPLONG (Sirphilip) — Sun, 25 Jan 2026 12:13:29 +0000

Let me be honest.

I didn’t wake up one day and say “Yeah, today I become a data analyst.”

It started with Excel. Just rows. Columns. Confusion. And a LOT of scrolling.

But somewhere between cleaning messy data and making my first chart, I realized something wild:Excel is basically data analysis in disguise.

If you’re a beginner and Excel feels scary, relax you’re exactly where you’re supposed to be. In this article, I’ll walk you through how MS Excel can be used for basic data analysis, using simple language, real-life vibes, and practical examples.

1. Understanding Data in Excel (The Foundation)

Before analysis, there must be data.

In Excel, data usually lives in:

Rows → individual records (one person, one sale, one day)
Columns → variables (name, age, salary, date, etc.)

Think of Excel like a table in real life:

Each row is one story
Each column is one detail about that story

For example:

Row 2 = Bongo’s details
Column C = everyone’s salary

💡 Rule of thumb:

If your data has clear headers and no empty random rows, you’re already winning.

2. Cleaning Data (Because Real Data Is Always Messy)

Nobody talks about this part enough.

Real data is:

Misspelled
Has extra spaces
Mixed uppercase and lowercase
Sometimes straight-up wrong

Before analysis, we clean.

Common cleaning tasks in Excel:

Removing extra spaces using TRIM()
Making text consistent using UPPER(), LOWER(), or PROPER()
Removing duplicates
Fixing date and number formats

Example:

" bongo lala " → "Bongo Lala"

Story moment: The first time I cleaned data, I thought I was doing something wrong because the numbers suddenly made sense. Turns out… that’s the point.

3. Sorting and Filtering (Finding Meaning Fast)

Imagine having 500 rows of data and trying to “just look” for answers.

Yeah… no.

That’s where Sort and Filter save your life.

Sorting helps you:

Arrange salaries from highest to lowest
Order dates from oldest to newest
Rank scores

Filtering helps you:

See only Sales department
View employees above age 30
Focus on specific categories

💡 Beginner win: If you can filter data, you can already answer real business questions.

4. Using Simple Formulas (Excel Starts Thinking for You)

This is where Excel stops being a table and starts being smart.

Basic formulas used in data analysis:

SUM() → total values
AVERAGE() → mean
COUNT() → number of entries
MAX() / MIN() → highest & lowest values

Example questions Excel can answer:

What is the total salary paid?
What is the average age?
Who earns the most?

5. Conditional Formatting (Let Excel Highlight the Story)

Data doesn’t always speak.

So we highlight it.

Conditional Formatting lets you:

Highlight high or low values
Color-code performance
Spot patterns instantly

Example:

Salaries above 100,000 → green
Low scores → red

Why this matters*: Your eyes understand colors faster than numbers.

6. Pivot Tables (Summary Without Stress)

Pivot Tables sound scary.

They’re not.

Think of a Pivot Table as:

A summary button for large data

With Pivot Tables, you can:

Count employees per department
Sum sales per month
Compare categories easily

And the best part? No formulas needed.

7. Charts and Visuals (Making Data Human)

Numbers are cool.

But visuals? They hit different.

Excel charts help you:

Compare values
See trends over time
Explain data to other humans

Common beginner charts:

Column charts
Bar charts
Line charts
Pie charts

Pro tip: If someone understands your chart in 5 seconds, you did it right.

Final Thoughts: Excel Is the Gateway Drug

Most people think data analysis starts with Python, SQL, or Power BI.

But for many of us? It starts with Excel.

Excel teaches you:

How data is structured
How to ask questions
How to find answers

And once that clicks… everything else becomes easier.

So if you’re learning Excel right now — keep going. You’re not just learning a tool.

You’re learning how to think with data.

If this helped you, feel free to share it or drop your Excel learning story. We’re all just one spreadsheet away from greatness.

Introduction to Git Bash and GitHub for Beginners

PHILIP KAPLONG (Sirphilip) — Sun, 18 Jan 2026 10:13:33 +0000

#What is Git?
Git is a free, open-source version control system that tracks changes in your code (or any files) over time. It lets developers work on projects of any size, revert to previous versions if needed, experiment safely with branches, and collaborate with others without overwriting each other's work.

What is Git Bash?

Bash is the default command-line shell on Linux and macOS
Git Bash is a lightweight application for Windows that brings a Bash-like Unix-style terminal environment, including Git commands. It allows Windows users to run Git and many Unix commands seamlessly in a familiar shell.
Git Bash is installed locally on your computer.

What is GitHub?

GitHub is a cloud-based hosting platform built on top of Git. It lets you store your Git repositories online, share code with others, collaborate in teams, review changes via pull requests, and discover open-source projects.

Installing Git Bash (Windows)

Go to the official Git website: https://git-scm.com/downloads
Download the Windows installer (it includes Git Bash).
Run the downloaded .exe file and follow the setup wizard.
- Accept defaults for most options (they work well for beginners). -Choose your preferred text editor (e.g., Notepad++ or VS Code if installed). -Keep line ending conversion enabled for cross-platform compatibility.
Complete the installation.

Verify installation
Open Git Bash (search for "Git Bash" in the Start menu) and run:bash

git --version

You should see something like git version 2.XX.X.windows.X.

Initial Git Configuration
Set your name and email (these appear in your commits):bash

git config --global user.name "Your Full Name"
git config --global user.email "your.email@example.com"

Use the same email you have on your GitHub account.

Linking Git Bash to Your GitHub Account (Using SSH)

SSH keys provide secure, password-less authentication for pushing/pulling code.
1.Generate a new SSH key (in Git Bash)
ssh-keygen -t ed25519 -C"your.email@example.com"`

-Press Enter to accept the default file location (~/.ssh/id_ed25519).
-Optionally set a passphrase (recommended for extra security).

2.Start the SSH agent and add your key:

eval "$(ssh-agent -s)" ssh-add ~/.ssh/id_ed25519

3.Copy your public key to the clipboard:

cat ~/.ssh/id_ed25519.pub

(Select and copy the output starting with ssh-ed25519 ...)

4.Add the key to GitHub:

Log in to GitHub → Click your profile picture → Settings → SSH and GPG keys → New SSH key.
Give it a title (e.g., "My Windows PC").
Paste the key and click Add SSH key

Test the connection:
ssh -T git@github.com

You should see: Hi username! You've successfully authenticated...

Pulling and Pushing Code

git pull
Fetches changes from the remote repository (e.g., GitHub) and merges them into your local branch.
git pull origin main

(git pull = git fetch + git merge)
Always pull before starting work to avoid conflicts.

git push
Uploads your local commits to the remote repository.
git push origin main

origin = default name for your GitHub remote.
main = default branch name (some older repos use master).

If it's your first push to a new repo, you may need to set upstream:
git push -u origin main

Tracking Changes – The Core Workflow

Use these three commands in almost every session:
1.git status
Shows what's changed, staged, or untracked.
git status

2.git add
Stages changes (prepares them for commit).

Stage one file: git add filename.txt
Stage all changes: git add . or git add -A

3.git commit
Saves staged changes permanently with a message.
git commit -m "Add new feature: user login page"

Good commit messages are short, descriptive, and in present tense (e.g., "Fix bug in login form").

Typical workflow:

git status # Check what's changed #Edit files... git add . # Stage everything git commit -m "Your message here" git pull origin main # Get latest changes first! git push origin main # Send to GitHub

What is Version Control and Why It Matters

Version control records every change to files over time so you can recall specific versions later.
Key benefits:

Full history of who changed what and why.
Branching & merging: Work on features/bug fixes in isolation, then combine safely.
Easy rollback if something breaks.
Collaboration: Multiple people work on the same project without chaos.
Backup: Your code lives safely on GitHub.

Master these basics, and you'll be ready to create repositories, clone projects, create branches, and contribute to open source!
Happy coding!
Feel free to practice by creating a simple repo on GitHub and pushing a "Hello World" file.