DEV Community: rosemutai

Mastering SQL Subqueries and CTEs

rosemutai — Tue, 21 Apr 2026 16:19:12 +0000

Introduction

SQL is one of the most important tools in data analysis because it allows analysts to retrieve, transform and analyze data directly from relational databases. Often, we will use basic queries but then as the datasets we are using grow, we will require advanced querying techniques like subqueries and Common Table Expressions (CTEs).
In this article, we are going to learn:

What subqueries are and how they work
Types of subqueries
When to use subqueries
What CTEs are
Comparison between subqueries and CTEs

Subquery

A subquery is a query that is written inside another SQL query. It is also called an inner query or a nested query.

How it works

First, the inner query executes, then the result is passed to the outer query and finally the outer query uses that result to filter rows, apply aggregate functions like SUM, update data or delete rows based on conditions.
For example
This query will:

Group customers by city
Count how many customers each city has
Select the city with the highest count

SELECT city
FROM customers
GROUP BY city
ORDER BY COUNT(*) DESC
LIMIT 1;

Result

Types of subqueries

To understand the different types of subqueries, we are going to use two tables: customers and orders table:

1. Single-Row Subquery

This subquery returns only one value
For example
To display customers who spent more than the average order amount

SELECT name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > (
        SELECT AVG(total_spent)
        FROM (
            SELECT SUM(amount) AS total_spent
            FROM orders
            GROUP BY customer_id
        ) AS customer_totals
    )
);

Result:

2. Multi-Row Subquery

This subquery returns more than one value
For example
To display customers who have placed orders

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

Result

3. Correlated Subquery

This is a subquery that depends on the outer query for its values
For example:
To display customers whose order is above their own average order value:

SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

Result

When to use Subqueries

When filtering based on aggregated data
When breaking down complex logic
When performing dynamic comparisons

Common Table Expression (CTE)

A Common Table Expressions (CTE) is a temporary result set in SQL that you can reference within a single query.
It only exists during query execution.

For example
To calculate the total spending per customer:

WITH customer_spending AS (
    SELECT 
        customer_id,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.name,
    cs.total_spent
FROM customers c
JOIN customer_spending cs
ON c.customer_id = cs.customer_id;

Output

Uses of CTEs

CTEs are widely used because they help simplify and organize complex SQL queries. They:

Improve readability as each step is clearly separated
Handle hierarchical data
Enable step by step transformations

Limitations of CTEs

Temporary, a CTE only works while the query is running.
They can be slower on very large datasets.

Subqueries vs CTEs

Here is a comparison of CTEs and Subqueries:

Feature	Subqueries	CTEs
Readability	Low when nested	High
Reusability	Limited	High
Performance	Can be slower	Often optimized better
Best for	Simple filtering	Complex logic

Conclusion

Subqueries and CTEs are powerful SQL tools that a good analyst should master. Subqueries are useful for quick embedded logic while CTEs provide clarity and scalability.

Publishing and Embedding Power BI Reports on the Web with IFrames

rosemutai — Fri, 10 Apr 2026 13:25:27 +0000

Microsoft Power BI is a tool created by Microsoft to turn raw data into interactive insights.
The power of Microsoft Power BI lies in its ability to pull data from many sources, e.g., web, excel, clean the data and then create visuals that are easy to understand.

Key Components

Power BI Desktop
Power BI Service
Power BI Mobile

Why Publish and Embed Reports?

After creating reports, there is need to share with your teammates and stakeholders. Hence the need to publish these reports.
Publishing to the web allows users to make reports publicly available and accessible on the internet creating interactive visuals that anyone with a URL can view without signing in.

Iframe

This is a HTML element used to embed another document or website within a web page,
You will use iframes to embed your reports on the web.

Syntax

<iframe src="url" title="description"></iframe>

Use the height and width attributes to specify the size of the iframe. The height and width are specified in pixels by default.

Publishing Process

Create a report in Power BI Desktop
Publish it in Power BI Service
Generate an embedded code
Embed it in a website

Create a report in Power BI Desktop

First, start by building your report in Power BI Desktop.
You can include: KPI cards, trend visuals, category breakdowns, filters and slicers, etc.

Create a workspace

A workspace is a Power BI container for dashboards, reports, workbooks, datasets, and dataflows.

How to create a workspace

Go to Power Bi Service: https://app.powerbi.com
Click Workspaces in the left pane
Select on +New workspace button
Fill in the details: workspace name etc.

Publish your Report

Once your report is ready we will upload it in the Power BI Service by following these steps:

Open your report in Power BI Desktop
Click Publish
Sign into your account if not signed in
Select your workspace

Click select button After publishing the report, it is now available online.

Generate the Embedded Code

How to generate the Embedded Code us

To generate an embedded code, open the report in your workspace and follow these steps:
1.** Click File** --> Embed Report --> Publish to Web

Click Create Embed Code You will get code like this:

<iframe title="Electronics_Sales" width="1140" height="541.25" src="https://app.powerbi.com/reportEmbed?reportId=2bddbd15-9eb5-40c5-bea5-d196cd5f4ee2&autoAuth=true&ctid=c95984e8-6a70-4512-a440-1c79bca9cc37" frameborder="0" allowFullScreen="true"></iframe>

Copy the generated iframe code.

Embedding the report in a website

Now go to your favorite editor where your code is to embed the report.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Electronic Sales</title>
</head>
<body>
    <iframe title="Electronics_Sales" width="1140" height="541.25" 
        src="https://app.powerbi.com/reportEmbed?reportId=2bddbd15-9eb5-40c5-bea5-d196cd5f4ee2&autoAuth=true&ctid=c95984e8-6a70-4512-a440-1c79bca9cc37" 
        frameborder="0" allowFullScreen="true">
    </iframe>
</body>
</html>

Test

Always test your report after embedding. You can check for things like:

Does the report load correctly?
Is it responsive on different screen sizes?

Conclusion

Publishing and embedding Power BI reports transforms your dashboards from static files into interactive and shareable web experiences.
Embedding enhances accessibility and presentations.

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

rosemutai — Wed, 01 Apr 2026 07:48:52 +0000

Introduction

In Power Bi, a data model is a collection of tables, relationships, and calculations that represent the underlying structure of your data.
Data Modeling, therefore, is the process of organizing data into tables and defining how these tables relate to each other.
Data modeling happens after loading data and before visualization.

Components of a Power Bi Data Model

1. Tables
These are the basic building blocks of a data model.
2. Columns
These are custom columns that you create in your tables using DAX(Data Analysis Expressions), a powerful formula language in Power Bi.
3. Measures
These are calculations used to aggregate data in a model.
4. Relationships
These determine how tables are connected in a data model.

Why model data?

Data is never perfect
To reshape data for analysis
To compress data usage
It is easier to understand a model

Joins in Power Bi

Joins enable you to establish relationships between data tables. A join combines data from two tables into one based on a common column.

Types of joins in Power Bi

1. Inner Join
It returns the rows present in both left and right table only if there is a match.
2. Left Outer Join
It returns all the rows present in the left table and matching rows from the right table.
3. Right Outer Join
It returns all the rows present in the right table and matching rows from the left table.
4. Full Outer Join
It returns all the rows present in both the left and right table

Where to use Joins

Use joins when:

You need to clean or transform data
You want a single table for simpler analysis

Relationships in Power BI

Relationships in Power BI connect tables allowing data from multiple sources to be analyzed together. They define how rows in one table relate to one another enabling cross-filtering for visuals.
There are several types of relationships:

1. One-to-One Relationship
Each row in the first table is related to only one row in the second table.
2. Many-to-One Relationship
Many rows in the first table are related to one row in the second table.
3. One-to-Many Relationship
One row in the first table is related to one or more rows in the second table.
3. Many-to-Many Relationship
Each row in the first table can be related to multiple rows in the second table

How to create relationships in Power BI

Power BI can auto-detect relationships or allow manual creation for better control.
To create relationships in Power BI, you drag a field from one table onto the corresponding field in another within the Model View.

On the Ribbon click on Manage Relationships:

Cardinality and Cross-Filtering

Cardinality and Cross-Filtering in Power BI determine how tables relate and how filters pass between them.
Cardinality, e.g., one-to-many, many-to-many, defines the relationship type based on unique key values while Cross-Filtering(single or both) defines the direction filters propagate.

Data Modeling Schemas

What is a schema?

A schema is a blueprint that defines how tables are organized and connected in your model.

Types of schemas

1. Star Schema
The star schema is a simple and commonly used schema in data warehousing. It consists of a central fact table surrounded by dimensions table.

source: _(https://tabulareditor.com/hs-fs/hubfs/Example-of-a-star-schema.jpg?width=754&height=508&name=Example-of-a-star-schema.jpg)

Benefits

Has a simpler design
It has faster queries
It is easier to maintain

2. Snowflake Schema
The snowflake schema is a more normalized version of the star schema. Dimension tables are further divided into sub-tables.

source: _(https://share.google/V57rZDUnrxAockCtJ)

Benefits

Reduces redundancy

Best Practices for Modeling data in Power BI

To build efficient data models:

Use a star schema whenever possible
Keep relationships simple and clean
Remove unused columns
Perform data cleaning before modeling

Example:
Say we are analyzing sales data.

Step 1: Load Data

Import the Sales, Customers, and Products tables.

Step 2: Clean Data

Use Power Query
We are going to remove duplicates, handle missing values and ensure consistent data models.

Step 3: Create relationships

For the Sales table and Customers table, choose customerid and id respectively
For the Sales table and Products table, choose productid and id respectively.

Step 4: Apply Star Schema

The Sales table becomes the fact table.
Customers and Products become dimensions tables.

After this we can:

Analyze sales by products
Build a dynamic dashboard

Conclusion

Data modeling is the backbone of effective modeling in Power BI. By understanding the fundamental concepts such as joins, relationships and schemas you can transform raw data into meaningful insights.
A well designed model improves performance and makes reports more flexible and easier to maintain.

How Excel is Used in Real-World Data Analysis

rosemutai — Fri, 27 Mar 2026 14:07:53 +0000

Introduction

Excel is a powerful tool used for mathematical functions developed by Microsoft.
Data analysis is the process of inspecting, cleaning, transforming and modeling data to discover useful information, conclusions and support decision making.
In this article we are going to learn Excel, understand Excel's functions, how it is used in data analysis and why it is still in use today despite the many advanced available tools.

What is Excel in Data Analysis?

Excel is used for organizing, manipulating and analyzing data using rows and columns. It serves as a tool for performing calculations, identifying trends, and generating insights.

Key Strengths

Accessibility- almost everyone has it.
Ease of use- low learning curve
It is flexible for different industries

Data Collection and Importing Data

Your data might have to come from different sources hence the need to import it to Excel. Some of these sources are:

Web sources
Text Files
XML Files
Databases

Excel provides tools like Power Query which allows users to connect to external data sources and import data efficiently.

Data Cleaning

This is the most important step in data analysis. Before any analysis can be performed, data must be cleaned. Data Cleaning involves:

Handling missing values or blanks
Removing duplicate records
Correcting inconsistent formats like dates, currencies and texts
Converting negative values

Data Organization and Structuring

After cleaning data, it must be properly structured. Excel allows users to organize datasets into tables which improve readability and functionality.
Data can be organized by:

Converting data into tables
Using clear column headers
Sorting and filtering data

This step is important because well structured data allows Excel features like formulas and pivot tables to work correctly.

Data Analysis Techniques in Excel

1. Formulas and Functions
Excel offers a wide range of formulas that enables users to perform calculations and logical operations.
Examples:

SUM: Used to add values. Example:

=SUM(A2:A10)

Adds the values in cell A2:A10

AVERAGE: Returns the average (mean). Example:

=AVERAGE(A2:A20)

Returns the average of these numbers, only if the range A2:A20 contains numbers.

COUNT: Counts the number of cells that contain numbers, and counts numbers within the list of arguments. Example:

=COUNT(A2:A20)

Counts the numbers in the range A1:A20

IF: Allows you to make logical comparisons between a value and what you expect. Example:

=IF(B8>C8,"Fast Speed", "Slow Speed")

VLOOKUP: Used to find things in a table or range by row, e.g, to find an employee based on their employee id. Example:

=VLOOKUP(102, A2:C20,2,False)

HLOOKUP: Used to search for a value in the top row of a table or an array of values and then returns a value in the same column from a row you specify in the table or array. Example:

=HLOOKUP("BMW", A1:C10, 2, True)

Looks up "BMW" in row 1 and returns the value from row 2 that is in the same column

COUNTIF: Used to count the number of cells that meet a criterion. Example:

=COUNTIF(A2:A10, "Nairobi")

Counts the number of cells with Nairobi in cells A2 through A5.

2. Pivot Tables
Pivot tables are one of Excel's most powerful features for data analysis. They allow users to summarize large datasets quickly without writing complex formulas, for example, use it to summarize total sales by region.

Data Visualization and Dashboards

Data visualization helps transform raw data into meaningful insights.
Excel provides various chart types such as:

Bar charts
Line Charts
Pie Charts
We also have such features as conditional formatting, which is used to highlight trends and patterns directly in the data.

Excel dashboards combine multiple visual elements into a single view. It may contain:

Key Performance Indicators (KPIs)
Charts showing trends over time
Slicers for interactive filtering
Dashboards are used widely in business settings to present insights in a clear and visual appealing way.

Trend Analysis and Insights

One of the main goals of data analysis is to identify trends and patterns. Excel comes in by making it easy to analyze changes over time and compare different categories, for example, tracking monthly sales growth.
These insights help businesses make data-driven decisions such as improving customer experience.

Automation and Efficiency

In real-world environments, analysts often deal with repetitive tasks. To improve efficiency, Excel provides automation. These include:

Macros: for automating repetitive tasks such as formatting, data entry, report generation
Power Query: for importing, cleaning and transforming data from diverse sources.
Office Scripts: for automating tasks in Excel for the web allowing for scheduled, automated workflows.

Real World Use Cases

1. Business
Companies use Excel to track revenue and monitor sales performances.
2. Finance
Excel helps organizations plan and manage their finances effectively as it is used for budgeting, financial modeling and forecasting.
3. Healthcare
Hospitals used Excel to analyze patient data, track admissions and monitor trends. This helps improve decision making and resource allocation.
4. Marketing
Marketing Teams use Excel to analyze campaign performance, measure engagement and to understand customer behavior.

Limitations of Excel

Despite its numerous strengths, Excel has some limitations such as:

It struggles with very large datasets
It is prone to manual errors
It lacks real time data processing capabilities

Conclusion

In conclusion, Excel remains one of the most powerful and accessible tools for data analysis. The combination of simplicity and functionality makes it suitable for both beginners and experienced professionals.
Excel plays a vital role in real-world decision-making from data cleaning and analysis to visualization and reporting.