DEV Community: Martin Ndungu

Subqueries and Cte's

Martin Ndungu — Tue, 21 Apr 2026 13:37:43 +0000

Introduction

SQL is a powerful language used for managing and analyzing data. As queries become more complex, tools like subqueries and Common Table Expressions (CTEs) help simplify logic and improve readability.

What is a Subquery?

A subquery is a query nested inside another query. It is used to perform operations that depend on the result of another query.

Example:

SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id FROM sales
);

Types of Subqueries

Scalar Subquery
Returns a single value.
Multi-row Subquery
Returns multiple rows.
Correlated Subquery
Depends on the outer query.

When to Use Subqueries

Filtering based on aggregated data
Comparing values across tables
Handling nested logic

What is a CTE?

A Common Table Expression (CTE) is a temporary result set defined using the WITH clause.

Example:

WITH sales_data AS (
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id
)
SELECT * FROM sales_data;

Types of CTEs

Simple CTE
Recursive CTE (used for hierarchical data)

When to Use CTEs

When queries become complex
When logic needs to be reused
When readability is important

Subqueries vs CTEs

Feature	Subqueries	CTEs
Readability	Moderate	High
Reusability	Low	High
Complexity Handling	Limited	Excellent

Conclusion

Both subqueries and CTEs are essential tools in SQL. Subqueries are useful for simple logic, while CTEs are ideal for complex and readable queries.

Mastering both improves your ability to solve real-world data problems efficiently.

SQL JOINs Explained Simply – From Beginner to Data Analyst

Martin Ndungu — Sun, 19 Apr 2026 19:40:40 +0000

*SQL JOINs Explained Simply – From Beginner to Data Analyst
*
Introduction
When I first started learning SQL, JOINs felt confusing and overwhelming. But once I understood how they work, everything changed. JOINs are one of the most powerful tools in SQL they allow you to combine data from multiple tables and unlock real insights.

In this article, I’ll explain SQL JOINs in the simplest way possible using real examples from my project.

What is a JOIN?

A JOIN is used to combine rows from two or more tables based on a related column.

Think of it like this:

One table has students
Another table has exam results

A JOIN allows you to connect them and answer questions like:
👉 Which student scored what marks?

Types of SQL JOINs

INNER JOIN (Most Common)

Returns only matching records from both tables.

Example:

SELECT 
    a.appointment_id,
    p.full_name,
    d.full_name,
    a.diagnosis
FROM city_hospital.appointments a
INNER JOIN city_hospital.patients p 
    ON a.patient_id = p.patient_id
INNER JOIN city_hospital.doctors d 
    ON a.doctor_id = d.doctor_id;

✅ Only shows records where a match exists
❌ Drops unmatched data

LEFT JOIN

Returns ALL records from the left table and matching ones from the right.

Example:

SELECT 
    p.full_name,
    a.appointment_date,
    a.diagnosis
FROM city_hospital.patients p
LEFT JOIN city_hospital.appointments a 
    ON p.patient_id = a.patient_id;

✅ Shows ALL patients
✅ Even those without appointments

RIGHT JOIN

Returns ALL records from the right table.

Example:

SELECT 
    d.full_name AS doctor_name,
    p.full_name AS patient_name
FROM city_hospital.appointments a
RIGHT JOIN city_hospital.doctors d 
    ON a.doctor_id = d.doctor_id
LEFT JOIN city_hospital.patients p 
    ON a.patient_id = p.patient_id;

✅ Shows ALL doctors
✅ Even those who haven’t seen patients

Finding Missing Data (Advanced Trick)

Want to find patients who NEVER had appointments?

SELECT 
    p.full_name,
    p.city
FROM city_hospital.patients p
LEFT JOIN city_hospital.appointments a 
    ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;

🔥 This is a very important real-world technique.

Real-World Use Case

Imagine a hospital system:

Patients table → personal details
Doctors table → medical staff
Appointments table → interactions

Using JOINs, you can answer:

Which doctor treated which patient?
Which patients have never visited?
What diagnoses were given?

This is exactly how data analysts work in real companies.

Common Mistakes Beginners Make

❌ Forgetting the ON condition
❌ Joining wrong columns
❌ Using INNER JOIN when LEFT JOIN is needed
❌ Not understanding NULL results

Key Takeaways

INNER JOIN = matching records only
LEFT JOIN = keep everything from left
RIGHT JOIN = keep everything from right
JOINs are essential for real data analysis

Conclusion

JOINs are not just a SQL topic — they are a core skill for data analysis, business intelligence, and real-world problem solving.

Once you master JOINs, you move from writing queries to actually understanding data.

About This Project

This article is based on my SQL practice project using:

PostgreSQL
Real-world structured datasets
Topics including JOINs, Window Functions, and Data Analysis

Connect With Me

If you're also learning SQL or data analysis, feel free to connect and share ideas!

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Martin Ndungu — Tue, 31 Mar 2026 17:15:39 +0000

When I first started using Power BI, I was all about the visuals. I’d drag charts onto the canvas, tweak colors, and feel like I was building something impressive. But before long, things started going sideways numbers didn’t add up, filters acted strangely, and performance slowed to a crawl as my data grew.
That’s when it clicked: the real foundation of a good Power BI report isn’t the visuals. It’s the data model sitting underneath.
This article comes from that shift in perspective. I’ll walk through data modeling in a practical, down‑to‑earth way covering joins, relationships, schemas, and some of the common challenges you’ll run into.
What Is Data Modeling, Really?
Data modeling is simply the process of organizing your data so you can analyze it accurately and efficiently.
In practice, that means splitting your data into logical tables and defining how they connect. Instead of jamming everything into one giant dataset, you build a system where each table has a clear job.
Here’s how I think about it: if raw data is a messy pile of papers on your desk, data modeling is what happens when you sort those papers into labeled folders so you can actually find what you need.
Understanding SQL Joins with Real Scenarios
Before diving into Power BI relationships, it helps to understand joins. Joins are how you combine data from different tables, usually during data prep.
Let’s say you have two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, Amount). Here’s how different joins play out in a real business context.
INNER JOIN
This returns only records that exist in both tables. If you run an inner join, you’ll see only customers who’ve actually placed an order. Anyone who signed up but never bought anything drops out. That makes this join great for analyzing active customers or confirmed transactions.
LEFT JOIN
A left join returns all records from the left table, plus matching ones from the right. If you start with Customers, you’ll see every customer including those with no orders. Their order fields will just show up blank. I’ve found this especially useful in marketing analysis, like when you want to spot registered users who haven’t converted yet.
RIGHT JOIN
Right join is the reverse it keeps everything in the right table. If you lead with Orders, you’ll see all orders, even if some are missing customer details. That’s handy for catching data quality issues, like incomplete records.

FULL OUTER JOIN
This grabs everything from both tables. You’ll get all customers and all orders, whether they match or not. I’ve used this most often in audits, when I need a complete picture of what’s there mismatches and all.
LEFT ANTI JOIN
This returns records from the left table that have no match in the right one. For example, it’ll show you customers who signed up but never bought anything. In practice, this kind of insight is gold for creating targeted campaigns or follow‑up promotions.
RIGHT ANTI JOIN
Similarly, this returns records from the right table with no match on the left. A typical use case is finding orders that aren’t tied to a valid customer. That usually signals something like a system error or bad data entry that needs fixing.
Where Joins Happen in Power BI
In Power BI, joins are handled in Power Query during the data preparation stage. Here’s how I usually do it:

Click “Transform Data” to open Power Query
Select your main table
Click “Merge Queries”
Choose the second table
Select the matching columns (e.g., CustomerID)
Pick your join type (Inner, Left, etc.)
Expand the merged column to pull in the fields you need At this point, you’re physically combining data into one table. How Relationships Work in Power BI Relationships are different. Instead of merging tables, they connect them while keeping them separate. You define relationships in “Model View” or through “Manage Relationships”. Types of Relationships • One to Many This is the workhorse of data modeling. One customer can have many orders. The Customer table holds unique CustomerIDs, while the Orders table may repeat them. Most business models are built around this structure. • Many to Many Both tables can have duplicates. Think of products and promotions: a product can belong to multiple promotions, and a promotion can include many products. In my experience, many‑to‑many relationships need careful handling they can easily throw off totals if you’re not deliberate.

• One to One
Each value appears just once in both tables. It’s less common, but I’ve used it when splitting large tables for performance or security reasons.
• Active vs Inactive Relationships
Power BI lets you have multiple relationships between tables, but only one can be active at a time.
An “active” relationship gets used automatically in calculations.
An “inactive” one must be activated through DAX when needed.
A good example: a sales table with both an Order Date and a Delivery Date. Depending on what you’re analyzing, you might need to switch between them.
• Cardinality

Cardinality defines how tables relate one‑to‑many, many‑to‑one, or many‑to‑many. Getting this right matters because it directly impacts how filters behave and how your calculations turn out.
• Cross Filter Direction
This controls how filters move between tables. Single direction means filters flow one way only; both directions means filters move back and forth. I usually stick with single direction unless I have a clear reason to change it. Both directions can lead to confusing results and slower performance if you’re not careful.
Joins vs Relationships: A Quick Comparison
This distinction is important. Joins combine tables into one. They happen in Power Query, during data prep. Relationships connect tables without merging them. They happen in the Model View and shape how analysis works.
Another way to think about it: joins shape the data before it’s loaded; relationships define how the data behaves after it’s loaded.
• Fact and Dimension Tables
A clean data model separates tables into two types. Fact tables hold measurable, numeric data sales, revenue, quantity. Dimension tables hold descriptive attributes customer names, product categories, dates.
For example, in a sales model, you’d have one Sales fact table connected to Customer, Product, and Date dimension tables. This setup improves both performance and clarity.
Data Modeling Schemas
• Star Schema
This is the go‑to approach. You have a central fact table connected directly to multiple dimension tables. In most of my projects, I start with a star schema because it’s simple, efficient, and easy to maintain.
• Snowflake Schema
Think of this as a more normalized version of a star schema. Here, dimension tables are broken down furtherfor example, splitting a Product table into Category and Subcategory tables. It reduces redundancy but adds complexity.
• Flat Table
Everything in one table. It’s quick to set up, but as your data grows, it becomes a bottleneck. I’d say flat tables are fine for small projects but best avoided once you start scaling.
• Role‑Playing Dimensions
Sometimes a single dimension table gets used in multiple ways. A classic example: one Date table used for Order Date, Shipping Date, and Delivery Date. Instead of duplicating the table, you create multiple relationships with different roles. It keeps the model clean while supporting flexible analysis.
Common Data Modeling Challenges
From experience, a few problems pop up again and again: overusing many‑to‑many relationships can mess up aggregations; skipping thorough data cleaning leads to unreliable results; incorrect cross filter direction creates confusing outputs; loading too many unnecessary tables slows performance. These aren’t just technical slip‑ups they directly affect the quality of insights.
Personal Reflection
Learning data modeling completely changed how I use Power BI. I don’t start with visuals anymore. I start by asking: how is the data structured? How should tables relate? I’ve also gotten more disciplined I double‑check relationships, validate totals, and think through how filters will behave.
From a practical standpoint, this shift has made my reports more accurate, faster, and easier to explain.
Final Thoughts
Data modeling is the backbone of any solid Power BI solution. It ensures your numbers are correct, filters work as expected, and reports perform well. In my experience, taking the time to build a clean, thoughtful model always pays off fewer errors, clearer analysis, and more confidence in the results.
If you’re serious about data analysis, mastering data modeling isn’t optional. It’s essential.

HOW EXCEL IS USED IN REAL-WORLD DATA ANALYSIS

Martin Ndungu — Tue, 31 Mar 2026 17:10:21 +0000

When I first started using Excel, I honestly thought it was just a digital grid for typing in numbers and maybe doing a little addition. But the more I worked with it, the more I realized it’s actually one of the most powerful tools in real-world data analysis.
These days, you’ll find Excel everywhere in finance, business, healthcare, logistics, even small startups. It’s often what people use to make decisions based on data. What surprised me most was how something that looks so simple on the surface can completely change the way you understand information and raw data.
So, What Is Excel, Really?
At its core, Excel is a spreadsheet that organizes data into rows and columns. But beyond that, it helps you clean up messy data, spot patterns, and present insights in a way that actually makes sense. Instead of just staring at numbers, you start answering questions like:

What’s happening?
Why is it happening?
What should we do next? How Excel Gets Used in Practice As I’ve been practicing especially with datasets like product analysis, I’ve started to see how Excel really works in real-life situations. Examples of this are;
Cleaning Messy Data In the real world, data almost never comes clean. You don’t just open a file and start analyzing. There’s always something off:
Prices written as "KSh 1,200"
Ratings like "4.5 out of 5"
Discounts shown as "25%"
Missing entries or duplicates I used to overlook how important cleaning was. Now I know that this step is the foundation of any good analysis. With tools like Find and Replace, or functions like SUBSTITUTE, VALUE, and TRIM, I’ve learned to turn messy text into usable numbers. That alone changed how I approach any new dataset.
Turning Raw Data into Useful Information Once the data is clean, the next step is making it meaningful. Instead of just looking at prices, I started calculating things like discount amounts, average prices, and total reviews. Using formulas like IF to group data into categories like "High" or "Low," or ABS to clean up negative values, I realized that analysis isn’t just about reading data it’s about transforming it into something more useful.

Pivot Tables Changed Everything Pivot tables completely shifted how I use Excel. Instead of manually sorting and calculating, I could summarize large datasets in seconds, find top performing products, or compare categories at a glance. Want to know which products have the most reviews? Which have the highest discounts? How ratings are distributed? Pivot tables answer that in a few clicks. Before this, I didn’t think Excel could handle that kind of analysis so easily.
Visualizing the Story Numbers alone can be hard to follow. But when you turn them into charts, patterns start to pop out. Using bar charts, pie charts, or slicers, I could see things like whether higher discounts actually led to more reviews, or if better ratings attracted more engagement. That’s when data starts to tell a story.
Bringing It All Together with Dashboards One of the most exciting parts for me has been building dashboards. A good dashboard pulls everything into one place: key metrics, charts, filters, slicers. Instead of digging through rows of data, you can look at one screen and immediately understand what’s going on. This is exactly how businesses track performance in real life. Where Excel Fits in the Real World Now that I’ve gotten deeper into Excel, I see how it’s used across different fields:
Finance: tracking budgets, expenses, profits
Business: analyzing product performance and customer behavior
Marketing: measuring campaign results
Operations: managing inventory and logistics This isn’t just theory. This is what people actually do day to day. How It’s Changed My Thinking Learning Excel has really shifted my mindset. Before, data felt like a bunch of numbers with no real meaning. Now, I see it as something that can answer questions and guide decisions. I’ve become more analytical, instead of guessing, I look for evidence in the data. I’ve also learned to be more careful, especially with cleaning, because small mistakes can lead to wrong conclusions. Most importantly, Excel gave me confidence. I feel like I can take any dataset, make sense of it, and pull out a complete and useful insights. Final Thoughts Excel is more than a spreadsheet. It’s a practical, hands-on way to understand the world through data. Whether you’re in finance, running a business, or working on a personal project, it gives you the power to turn raw numbers into real insights. For me, learning Excel was the first big step into data analysis and it’s a skill I know I’ll keep building on.