DEV Community: Enock Kiprotich

Subqueries vs CTEs in SQL: A Practical Guide for Data Analysts

Enock Kiprotich — Wed, 22 Apr 2026 05:55:03 +0000

💡 How I Finally Understood Subqueries vs CTEs

When I first started learning SQL, I remember feeling confident—until I ran into subqueries.

At first, they seemed simple. “Just a query inside another query,” I told myself. But the moment I started working with more complex datasets, my queries became harder to read, harder to debug, and honestly… frustrating.

Then I discovered CTEs.

I still remember rewriting one of my messy nested queries using a CTE—and suddenly, everything made sense. The logic was clearer, the structure felt natural, and debugging became much easier. That was the moment I realized something important:

Writing SQL isn’t just about getting the right answer—it’s about writing queries that make sense.

Since then, I’ve learned that both subqueries and CTEs are powerful tools—but they serve different purposes. Knowing when to use each is what separates beginner SQL users from confident data analysts.

In this article, I’ll break down both concepts in a simple, practical way—based on what actually works in real-world data analysis.

When working with SQL, you’ll eventually run into situations where a single query isn’t enough. You need to break down logic, reuse results, or simplify complex operations.

🔍 Understanding Subqueries (The “Quick Solution” Tool)

A subquery is simply a query inside another query. Think of it as asking SQL to first answer a smaller question before solving the main one.

Example:

SELECT customer_name
FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Orders
    WHERE total_amount > 1000
);

In this case, SQL first finds customers with large orders, then uses that result to filter the main query.

🔹 Types of Subqueries You’ll Encounter

1. Single-Value Subqueries

Used when you expect one result (e.g., averages).

SELECT *
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

2. Multi-Value Subqueries

Return multiple results and are often used with IN.

SELECT *
FROM Products
WHERE product_id IN (
    SELECT product_id FROM Sales
);

3. Correlated Subqueries

These are more dynamic—they run once for every row in the outer query.

SELECT c.customer_name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

👉 Powerful, but can be slow if not used carefully.

🎯 When Subqueries Make Sense

Subqueries are best when:

You need a quick filter
The logic is simple
You don’t need to reuse the result
You’re working with aggregates like AVG or SUM

They’re great for straightforward problems, but can get messy fast.

🧠 CTEs: A More Structured Approach

A CTE (Common Table Expression) is like giving a name to a temporary result so you can use it in your query.

It’s defined using the WITH keyword.

Example:

WITH HighValueCustomers AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM Orders
    GROUP BY customer_id
)
SELECT *
FROM HighValueCustomers
WHERE total_spent > 1000;

Instead of nesting queries, you separate the logic into steps—which makes everything easier to read.

🔹 Types of CTEs

1. Simple CTE

Used to simplify complex queries into readable steps.

2. Recursive CTE

Useful for hierarchical data like:

Organizational structures
Category trees

3. Multi-CTE Queries

You can define multiple CTEs and combine them—very useful in real projects.

⚖️ Subqueries vs CTEs: What Really Matters

Let’s go beyond definitions and look at what actually matters in practice:

Aspect	Subqueries	CTEs
Readability	Can become confusing quickly	Much easier to follow
Reusability	One-time use	Reusable within the query
Debugging	Harder to isolate issues	Easier to test step-by-step
Performance	Can be inefficient (especially correlated ones)	Often optimized better
Best Use	Simple filtering	Complex logic

🚀 Choosing the Right Approach

Here’s a simple way to think about it:

Use Subqueries if:

The query is small and simple
You only need the result once
You’re filtering data

Use CTEs if:

The query is getting hard to read
You want to break logic into steps
You need to reuse results
You’re working on real-world data analysis

📊 Real-World Perspective

In real projects (especially dashboards or reporting):

Subqueries are often used for quick checks
CTEs are used to structure complex transformations

If you're building something that others will read or maintain, CTEs are usually the better choice.

🧾 Final Thoughts

Subqueries and CTEs solve similar problems—but they do it in different ways.

Subqueries are quick and compact.
CTEs are structured and scalable.

The real skill isn’t just knowing them—it’s knowing when to use each.

📌 Key Takeaway

If your query is becoming hard to read, that’s your signal to switch to a CTE.

✍️ Enock Kiprotich

Aspiring Data Analyst | SQL | Power BI | Python

📍 Open to Data Analyst Roles

🔗 linkedin.com/in/enock-kiprotich-30382a189

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

Enock Kiprotich — Sun, 29 Mar 2026 13:46:56 +0000

Introduction

Data modeling is a key step when working with Power BI. Before creating dashboards, data must be structured in a clear and logical way. A good data model improves performance, reduces errors, and makes analysis easier.

This article explains data modeling, SQL joins, Power BI relationships, schemas, and how to create them step-by-step.

What is Data Modeling?

Data modeling is the process of organizing data into tables and defining how those tables are connected.

It involves:

Structuring tables
Defining keys
Creating relationships

Why it matters

Faster reports
Accurate calculations
Better organization

SQL Joins (With Diagrams)

Assume two tables:

Customers (ID, Name)
Orders (ID, CustomerID)

1. INNER JOIN

Returns only matching records.

Example: Customers who made orders

2. LEFT JOIN

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

Example: All customers, including those without orders

3. RIGHT JOIN

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

Example: All orders, even if customer details are missing.

4. FULL OUTER JOIN

Returns all records from both tables.

Example: All customers and all orders

5. LEFT ANTI JOIN

Returns rows from the left table with no match.

Example: Customers who never ordered

6. RIGHT ANTI JOIN

Returns rows from the right table with no match.

Example: Orders without customers

Creating Joins in Power BI (Step-by-Step)

Open Power BI Desktop
Click Transform Data
Select a table
Click Merge Queries
Select the second table
Choose matching columns
Select join type (Inner, Left, Right, Full, Anti)
Click OK
Expand columns

Power BI Relationships

Relationships connect tables without merging them.

Types of Relationships

1. One-to-Many (1:M)

One record relates to many

Example: One customer → many orders

2. Many-to-Many (M:M)

Both tables contain repeated values

3. One-to-One (1:1)

Each record matches exactly one

Active vs Inactive

Active: Used automatically
Inactive: Used only when specified

Cardinality

Defines how tables relate:

One-to-many
Many-to-many

Cross Filter Direction

Single direction
Both directions

Creating Relationships in Power BI (Step-by-Step)

Method 1: Model View

Open Model View
Drag a column from one table to another

Method 2: Manage Relationships

Click Manage Relationships
Click New
Select tables
Select columns
Set cardinality
Choose cross-filter direction
Click OK

📸 Insert Screenshot: Relationship configuration dialog box

Joins vs Relationships

Joins	Relationships
Combine tables	Link tables
Power Query	Model View
Used in data preparation	Used in analysis

Fact vs Dimension Tables

Fact Table

Contains numeric data used for analysis.

Examples:

Sales
Revenue

Dimension Table

Contains descriptive data.

Examples:

Customer
Product
Date

Data Schemas

Star Schema

One central fact table
Connected dimension tables

Use case: Best for Power BI performance

Snowflake Schema

Dimension tables split into smaller related tables

Use case: Complex datasets

Flat Table (DLAT)

All data in one table

Use case: Small or simple datasets

Role-Playing Dimensions

A single dimension used multiple times.

Example:

Order Date
Ship Date
Delivery Date

Common Data Modeling Issues

Duplicate keys
Incorrect relationships
Many-to-many confusion
Circular relationships
Poor performance

Best Practices

Use star schema
Keep models simple
Avoid unnecessary joins
Validate relationships

Conclusion

Data modeling is the foundation of Power BI. Understanding joins, relationships, and schemas helps build accurate, efficient, and scalable reports.

From Spreadsheets to Insights: How Excel is Used in Real-World Data Analysis

Enock Kiprotich — Tue, 24 Mar 2026 16:19:51 +0000

Introduction

In today’s digital world, data is everywhere. Organizations collect large amounts of information daily, but raw data alone has little value unless it is properly analyzed and interpreted. This is where Microsoft Excel becomes essential. Excel is more than just a spreadsheet tool—it is a practical solution used to transform raw data into meaningful insights that support decision-making.

What is Excel?

Microsoft Excel is a spreadsheet application developed by Microsoft that allows users to organize, store, and analyze data in a structured format using rows and columns. It provides powerful features such as formulas, functions, and data visualization tools that make it easier to process and understand data.

Real-World Applications of Excel

1. Business and Financial Analysis

In business environments, Excel is widely used to support financial decision-making. Organizations rely on it to:

Track sales performance
Monitor expenses and budgets
Analyze profits and losses

By using Excel, businesses can make informed decisions based on actual data rather than assumptions.

2. Healthcare Data Management

In healthcare, Excel helps improve efficiency and organization by:

Managing patient records
Tracking medical supplies and inventory
Generating reports for planning and resource allocation

Accurate data management in Excel contributes to better service delivery.

3. Data Analysis and Reporting

Excel is a key tool for data analysts. It is commonly used to:

Clean and organize raw data
Identify trends and patterns
Create reports and dashboards

This allows analysts to turn complex datasets into clear and actionable insights.

4. Government and Public Sector Use

In public institutions, Excel supports:

Data digitization and record keeping
Statistical analysis and reporting
Preparation of reports for decision-making

It plays an important role in improving efficiency and transparency.

Key Features and Functions in Excel

1. Basic Functions

Excel provides essential functions that simplify calculations:

=SUM(A1:A10) – Adds a range of values
=AVERAGE(A1:A10) – Calculates the average
=COUNT(A1:A10) – Counts the number of entries

2. Logical Functions

Logical functions help automate decision-making:

=IF(A1>=50,"Pass","Fail") This formula evaluates a condition and returns a result.

3. Lookup Functions

Lookup functions make it easy to retrieve specific data:

=VLOOKUP(A2, A1:C10, 2, FALSE) This searches for a value and returns related data from a table.

4. Error Handling

To handle errors in calculations:

=IFERROR(A1/B1,"Error") This prevents formulas from displaying error messages.

5. Data Analysis Tools

Excel also includes advanced tools such as:

Pivot Tables for summarizing large datasets
Charts (bar, line, pie) for visualization
Sorting and filtering for organizing data

Practical Example

For instance, a business can use Excel to analyze monthly sales data. By applying functions like SUM and using Pivot Tables, the business can identify top-performing products and trends. This information helps managers make informed decisions and improve performance.

Conclusion

Microsoft Excel remains one of the most important tools for real-world data analysis. Its ability to organize, analyze, and visualize data makes it valuable across different industries. Whether in business, healthcare, or government, Excel helps transform raw data into meaningful insights that drive better decision-making.

excel #dataanalysis #beginners #luxdevhq.ai

DEV Community: Enock Kiprotich

Subqueries vs CTEs in SQL: A Practical Guide for Data Analysts

💡 How I Finally Understood Subqueries vs CTEs

🔍 Understanding Subqueries (The “Quick Solution” Tool)

Example:

🔹 Types of Subqueries You’ll Encounter

1. Single-Value Subqueries

2. Multi-Value Subqueries

3. Correlated Subqueries

🎯 When Subqueries Make Sense

🧠 CTEs: A More Structured Approach

Example:

🔹 Types of CTEs

1. Simple CTE

2. Recursive CTE

3. Multi-CTE Queries

⚖️ Subqueries vs CTEs: What Really Matters

🚀 Choosing the Right Approach

Use Subqueries if:

Use CTEs if:

📊 Real-World Perspective

🧾 Final Thoughts

📌 Key Takeaway

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

Introduction

What is Data Modeling?

Why it matters

SQL Joins (With Diagrams)

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. FULL OUTER JOIN

5. LEFT ANTI JOIN

6. RIGHT ANTI JOIN

Creating Joins in Power BI (Step-by-Step)

Power BI Relationships

Types of Relationships

Active vs Inactive

Cardinality

Cross Filter Direction

Creating Relationships in Power BI (Step-by-Step)

Method 1: Model View

Method 2: Manage Relationships

Joins vs Relationships

Fact vs Dimension Tables

Fact Table

Dimension Table

Data Schemas

Star Schema

Snowflake Schema

Flat Table (DLAT)

Role-Playing Dimensions

Common Data Modeling Issues

Best Practices

Conclusion

From Spreadsheets to Insights: How Excel is Used in Real-World Data Analysis

Introduction

What is Excel?

Real-World Applications of Excel

1. Business and Financial Analysis

2. Healthcare Data Management

3. Data Analysis and Reporting

4. Government and Public Sector Use

Key Features and Functions in Excel

1. Basic Functions

2. Logical Functions

3. Lookup Functions

4. Error Handling

5. Data Analysis Tools

Practical Example

Conclusion

Tags

excel #dataanalysis #beginners #luxdevhq.ai