Forem: Menje

Subqueries and CTEs in SQL

Menje — Tue, 21 Apr 2026 20:51:06 +0000

When working with SQL, you eventually run into situations where a single query just isn’t enough. You need to break a problem into parts, compute an intermediate result, and then use that result elsewhere. That’s where subqueries and Common Table Expressions (CTEs) come in.

They solve similar problems, but they do it in slightly different ways, and choosing between them can affect not just performance, but also how readable and maintainable your code is.

Let's Dive into it;

What is a Subquery?

A subquery is a query written inside another query. It produces a result that the outer query depends on.

A helpful way to think about it is like asking a question within a question. For example, instead of directly filtering employees by salary, you might first calculate the average salary and then compare each employee against that value. That inner calculation is the subquery.

Subqueries are usually embedded in clauses like WHERE, SELECT, or FROM, depending on what role they’re playing.

Types of Subqueries

Subqueries come in a few forms, depending on their behavior and the results they return.

Scalar subqueries return a single value. These are often used in comparisons, like checking whether something is above or below an average or a maximum.
Multi-row subqueries return multiple values and are typically used with operators like IN. For example, you might retrieve a list of departments and then filter employees based on whether they belong to any of them.
Correlated subqueries are a bit different. They rely on values from the outer query, meaning they don’t run just once—they run repeatedly, once for each row being processed. This makes them powerful but also potentially slow if not used carefully.
Nested subqueries are simply subqueries inside other subqueries. While technically valid, they can quickly become hard to read and are often a sign that the logic could be reorganized more clearly.

When Should You Use Subqueries?

Subqueries are useful when the logic is relatively straightforward and doesn’t need to be reused. They’re great for quick calculations or filters, especially when the result is only needed once.
They also work well when you want to keep everything compact and close together, rather than breaking it into multiple steps.
That said, once subqueries become deeply nested or start depending on each other, they can become difficult to follow. Correlated subqueries in particular should be used carefully, as they may lead to performance issues on large datasets.

What are CTEs (Common Table Expressions)?

A Common Table Expression, or CTE, is a named temporary result set defined at the beginning of a query. Instead of embedding logic inside the query, you define it upfront and then reference it by name.
In a way, CTEs let you structure your query more like a sequence of steps. You compute something, give it a name, and then use it later. This makes the overall logic easier to read and reason about.

Types and Use Cases of CTEs

Simple CTEs are used to make queries more readable. Instead of writing everything in one block, you separate parts of the logic into named sections.
Multiple CTEs allow you to build queries step by step. One CTE can depend on another, which helps when dealing with more complex transformations or filtering logic. This layered approach often feels more natural than stacking multiple subqueries.
Recursive CTEs are used for hierarchical data, such as organizational structures or category trees. They repeatedly reference themselves to build results that would otherwise require loops or multiple queries. This is something subqueries are not well-suited for.

Subqueries vs CTEs

Readability

Subqueries tend to become harder to read as they grow, especially when nested. You often have to work from the inside out to understand what’s happening.
CTEs, on the other hand, present the logic in a more structured way. You can read them from top to bottom, which makes complex queries easier to follow and debug.

Performance

There’s no simple rule here. Some subqueries are optimized very well by database engines, especially simple ones. However, correlated subqueries can be expensive because they run multiple times.
CTEs can sometimes be materialized (stored temporarily), which might affect performance depending on the database system. In other cases, they’re optimized just like subqueries.
In practice, performance depends more on how the query is written and how the data is indexed than on whether you use a subquery or a CTE.

Reusability

Subqueries are typically written inline and used once. If you need the same logic in multiple places, you often end up repeating it.
CTEs allow you to define something once and reference it multiple times within the same query. This reduces duplication and makes updates easier.

Handling Complexity
Subqueries work well for simple problems, but they can become unwieldy when layered.
CTEs are better suited for complex queries because they let you break the logic into manageable steps. This is especially useful when multiple transformations or filters are involved.

When Should You Use Each?

Subqueries are a good choice when the task is simple, the result is only needed once, and keeping the query compact makes sense.
CTEs are more appropriate when the logic is complex, when readability matters, or when you need to reuse intermediate results. They’re also the better option for hierarchical data through recursion.

Final Thoughts

Subqueries and CTEs aren’t competing tools so much as different ways of thinking about the same problem. Subqueries are quick and direct, while CTEs are structured and expressive.
In practice, it’s common to start with a subquery and then switch to a CTE as the query grows in complexity. That shift usually reflects a deeper understanding of the problem—and a move toward writing queries that are not just correct, but also clear and easy to maintain.

A Deep Dive into SQL Basics

Menje — Sun, 12 Apr 2026 14:37:18 +0000

This week, I transitioned from simply viewing data to actively managing it. Using a practical assignment focused on a hypothetical "Nairobi Academy," I explored how SQL serves as the backbone for creating, modifying, and querying relational databases. Here is a breakdown of the core concepts and how they were applied.

The Architecture of Data: DDL vs. DML

DDL (Data Definition Language): This is used to define the structure of the database. It includes commands like CREATE (to build tables or schemas), ALTER (to change the structure), and DROP (to delete the structure).
DML (Data Manipulation Language): This is used to manage the data within the structures that have been defined by the DDL. It includes commands like INSERT (adding records), UPDATE (modifying existing records), and DELETE (removing records).

Step By Step example in a hypothetical project.

Project Goal: Building a school management system from scratch (nairobi_academy project)

CREATE: I first established a schema named nairobi_academy and then defined three core tables: students, subjects, and exam_results.

INSERT: Once the structure existed, I populated the tables with data.

UPDATE: Data is rarely static. When one of the students relocated from Nakuru to Nairobi, I used the UPDATE command paired with a WHERE clause to modify her specific record.

DELETE: To handle errors or cancellations, I used DELETE to remove specific rows from the table in question.

Precision Querying with WHERE clause

The WHERE clause is the most powerful tool for filtering data. With it, we can find exactly what we need. During the assignment, I practiced using various operators:

Comparison (=, >=): Finding all female students (gender = 'F') or identifying high achievers (marks >= 70).

BETWEEN: This allowed for clean range filtering, such as finding exams taken within a specific four-day window in March 2024.

IN: A more efficient way to check against a list. Instead of multiple OR statements, I used IN ('Nairobi', 'Mombasa', 'Kisumu') to find students from specific cities.

LIKE (Wild Card): Essential for pattern matching. I used this to find all subjects containing the word "Studies" (%Studies%) or students whose names started with "A" or "E".

Transforming Data with CASE WHEN

One of the most interesting tools explored was the CASE WHEN statement. This allows for conditional logic within a query, effectively creating new data labels without changing the original table.

For the Nairobi Academy, I used CASE WHEN to:
Assign Grades: Convert numeric marks into labels like 'Distinction', 'Merit', or 'Pass'.
Classify Levels: Group students into 'Senior' (Form 3/4) or 'Junior' (Form 1/2) categories based on their class.

Reflection

The most interesting part of this week was realizing how critical the order of operations is, especially when dealing with Foreign Keys or dropping columns. A small challenge I encountered was dealing with instances where the column name I was trying to create had the same name as one of the SQL functions CLASS. For this, I had to wrap the column name in double quotes to specify that it is the column I’m referring to and not the function.

The highlight was definitely the CASE WHEN logic. It feels like "programming" within SQL, providing a way to turn raw numbers into meaningful insights that a school principal or teacher could actually use. It’s the difference between seeing "78" and seeing a "Distinction."

From Desktop to Web: A Guide to Publishing and Embedding Power BI Reports

Menje — Sun, 05 Apr 2026 19:54:31 +0000

Power BI is a powerful business intelligence tool that transforms raw data into immersive, interactive visual stories. However, the true value of a report is realized only when it is shared with stakeholders. Publishing is the process of moving your report from the local Power BI Desktop environment to the cloud-based Power BI Service, where it can be managed, shared, and integrated into other platforms like company websites or portals.

Step 1: Creating a Workspace

A Workspace is a collaborative container in the Power BI Service where you house your reports, dashboards, and datasets.

Sign in to the Power BI Service.
On the left-hand navigation pane, click on Workspaces.
Select Create a workspace (usually at the bottom of the pane).
Give your workspace a unique name (e.g., "Sales Analytics 2026") and description. Click Save. You now have a dedicated "folder" in the cloud for your project.

Step 2: Uploading and Publishing Your Report

Once your report is ready in Power BI Desktop, you need to push it to the workspace you just created.
Open your report in Power BI Desktop.
On the Home tab of the ribbon, click the Publish button on the far right.
A dialog box will appear asking you to select a destination. Choose the Workspace you created in Step 1.
Click Select. Power BI will begin uploading the file and its underlying data model.
Once the "Success!" message appears, click the link to open the report in the Power BI Service.

Step 3: Generating the Embed Code

To put your report on a website, you need a snippet of code (an iFrame) that tells the website how to display the Power BI content.

In Power BI Service, open the report you just published.
Click the File menu in the top left corner.
Select Embed report.
Choose Publish to web (Public).
Note: This option makes the data visible to anyone with the link. For internal company data, you would typically use the "Website or portal" option, which requires users to sign in.
Click Create embed code and then Publish to confirm.
A dialog will appear showing two links: a URL for email sharing and an HTML iFrame code for website embedding. Copy the iFrame code.

Step 4: Embedding the Report on a Website

Now, you simply need to place that code into the HTML structure of your website. (For this, I used a basic custom HTML code from W3Schools HTML tutorials.)
Open the HTML editor of your website (e.g., WordPress, Wix, or a custom .html file).
Navigate to the page or section where you want the report to appear.
Paste the iFrame code you copied in Step 3.
Save and refresh your webpage. Your interactive Power BI report should now be live on the site!

Key Insights

• Security First: "Publish to Web" is for public data only. Always use "Website or Portal" for sensitive business data to ensure only authorized users can see the visuals.
• Data Freshness: Publishing is a one-time move. To keep the website data current, you must configure a Scheduled Refresh in the Power BI Service settings.
• Mobile Optimization: If your website is viewed on phones, consider creating a Mobile Layout in Power BI Desktop before publishing to ensure the charts resize correctly.

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

Menje — Sun, 29 Mar 2026 20:26:06 +0000

If you think of Power BI as a high-performance engine, data modeling is the blueprint that ensures every part connects perfectly. Without a solid model, even the most beautiful dashboards will provide slow, inaccurate, or confusing results. Data modeling is the process of connecting different data sources, defining how they relate to one another, and organizing them into a structure that Power BI can easily navigate.

1. The Building Blocks: Fact vs. Dimension Tables

Before we connect anything, we must categorize our tables.

Fact Tables: These contain the "quantitative" data; these are the numbers you want to aggregate.
Dimension Tables: These are "lookup" tables containing descriptive attributes. Examples include a Product table (Product Name, Category), a Customer table, or a Geography table. Dimensions allow you to "slice and dice" the facts.

2. SQL Joins: Merging Data in Power Query

In Power BI, joins usually happen in Power Query (via the "Merge Queries" button), which can be accessed from the ribbon.

Joins physically combine two tables into one based on a matching column.

Inner Join: Only returns rows where there is a match in both tables. If a product has no sales, it won't appear.
Left Outer Join: Keeps everything from the left table and only matching rows from the right. (Most common for adding descriptions to sales data).
Right Outer Join: Keeps everything from the right table and matching rows from the left.
Full Outer Join: Keeps all rows from both tables, filling in nulls where there are no matches.
Left Anti Join: Returns rows only in the left table that have no match in the right. (Great for finding "Products that never sold").
Right Anti Join: Returns rows only in the right table that have no match in the left.

3. Power BI Relationships: The Model View

Unlike joins, Relationships do not merge tables; they create a connection between them in the Model View. This is more efficient for large datasets.

Cardinality

One-to-Many (1:M): The gold standard. One product in the Dimension table appears many times in the Fact sales table.
One-to-One (1:1): Rare; usually means the tables should have been merged into one.
Many-to-Many (M: M): Avoid where possible, as it introduces ambiguity. For example, if many students are enrolled in many classes.

Cross-Filter Direction

Single (One-Way): The dimension table filters the fact table. This is the safest and most performant setting.
Both (Bi-directional): The fact table can also filter the dimension. Use this sparingly; this is because it can cause "circular dependency" errors.

Active vs. Inactive

A model can only have one Active relationship between two tables at a time. If you have two dates (Order Date and Shipping Date) connecting to a Calendar table, one must be Inactive. You can "wake it up" using the DAX function USERELATIONSHIP.

4. Designing the Architecture: Schemas

How you arrange these tables determines your "Schema."

Star Schema: The "Gold Standard" for Power BI. One Fact table in the center, surrounded by Dimension tables. It is fast, simple, and easy to maintain.
Snowflake Schema: An extension of the Star where dimensions are further broken down (e.g., Product connects to Sub-Category, which connects to Category). It saves space but can slow down performance.
Flat Table (Denormalized): Everything in one massive table. While easy for small Excel files, it becomes a nightmare for performance and data integrity in Power BI.

5. Advanced Concepts & Common Issues

Role-Playing Dimensions: This is when a single dimension acts in multiple roles. The best example is a Date Table that needs to handle both OrderDate and ShipDate.

Common Issues:

Circular References: When two tables filter each other in a loop, breaking the logic.
Ambiguity: When there are multiple paths between two tables, and Power BI doesn't know which one to follow.

6. Step-by-Step: Where to Create These

To Join (Merge): Go to Home > Transform Data. In Power Query, select Merge Queries from the ribbon. Choose your two tables and the type of join (Inner, Left, etc.).
To Create Relationships: Click the Model View icon (the three small boxes on the far left). Drag a column from the Dimension table (e.g., ProductID) and drop it onto the matching column in the Fact table.
To Manage Details: Go to Modeling > Manage Relationships. Here you can toggle relationships as Active/Inactive or change the Cross-filter direction.

How Excel is Used in Real-World Data Analysis

Menje — Sun, 29 Mar 2026 19:17:09 +0000

In today’s data-driven world, the ability to analyze and interpret data is an essential skill across many industries. One of the most widely used tools for this purpose is Microsoft Excel. While often perceived as a simple spreadsheet application, Excel is in fact a powerful data analysis tool used by businesses, researchers, and analysts to organize, clean, and extract insights from data.

What is Excel?

Microsoft Excel is a spreadsheet software that allows users to store data in a tabular format (rows and columns), perform calculations, and create visualizations. Its strength lies in its flexibility. It can handle everything from basic data entry to complex analytical tasks.

In real-world scenarios, Excel is used in finance for budgeting, in sales for performance tracking, in logistics for inventory management, and even in research for statistical analysis.

Using Excel in Real-World Data Analysis

To better understand how Excel is applied in practice, below is a preview of a sales dataset I worked with.

Figure 1: Preview of Sales Dataset

1. Data Cleaning and Organization

Before any analysis can begin, data must be clean and well-structured. In the dataset, columns like ORDER DATE, SALES USD, and REGION needed to be consistent and properly formatted.
For example:
• Dates were standardized to ensure correct sorting and filtering.
• Column names were reviewed for clarity and consistency.

Figure 2: Preview of the data before clean up

This step is critical in real-world analysis because messy data can lead to incorrect conclusions.

2. Aggregation Using Functions

One of the most useful Excel functions I applied is SUMIF.
For instance, to calculate total sales for a specific region:
=SUMIF(D:D, "East", F:F)
This formula sums all values in the SALES USD column where the REGION is "East".
Similarly, COUNTIF can be used to count entries that meet certain conditions. For example:
=COUNTIF(I:I, "Q1")

This counts how many sales occurred in Quarter 1.

These functions are widely used in business settings to quickly summarize large datasets without manually filtering data.

3. Grouping and Summarizing with Pivot Tables

Pivot tables are one of Excel’s most powerful features for data analysis.
Using the dataset, I created a pivot table to:
• Summarize total sales by region
• Compare performance across Sales Persons
• Analyze trends across Quarters (QTR)

Figure 3:Pivot table showing Sales by Region and Quarter

This makes it easy to answer questions like:
• Which region generates the highest revenue?
• Which quarter has the most sales?
• Which salesperson performs best?
In real-world scenarios, managers use pivot tables to make quick, data-driven decisions.

4. Time-Based Analysis

With columns like YEAR, MONTH, and QTR, Excel makes it easy to analyze trends over time.
For example:
• Grouping sales by quarter shows seasonal patterns.
• Comparing monthly sales helps identify growth or decline.
A simple pivot table or chart can reveal whether sales increase in certain periods, which is useful for planning and forecasting.

Figure 4: Chart showing Sales by Month

5. Data Visualization

Excel allows users to turn raw data into meaningful visuals.
Using charts such as:
• Bar charts for comparing regions
• Line charts for trends over time
I was able to present insights more clearly.
Visualization is important because it helps stakeholders understand data quickly without needing to interpret raw numbers.

6. Building Dashboards for Decision-Making

After all of the above and followed by performing descriptive and trend analysis, the next step is to present insights in a more interactive and decision-friendly way. This is where dashboards come in.

A dashboard in Excel is a visual summary of key metrics, often presented using charts, tables, and slicers in a single view. It allows users to monitor performance and make decisions without going back to the raw dataset.

Figure 5: Dashboard Analyzing Pricing, Discounts, and Customer Reviews

In this project, I created a JUMIA PRODUCT PERFORMANCE DASHBOARD, which focuses on analyzing pricing, discounts, and customer reviews.

Real-World Excel Application

In a business context, a dataset like Figure 1 Preview of Sales Dataset this could be used to:
• Track company revenue across different regions
• Evaluate the performance of sales personnel
• Identify top-performing products
• Monitor partnerships through the CHANNEL PARTNERS column
For example, if one region consistently underperforms, management can investigate and take action.

Personal Reflection

Learning Excel has completely changed how I approach data. Before, I would look at numbers as just figures without much meaning. Now, I see patterns, relationships, and stories behind the data.
Working with functions like SUMIF() and tools like pivot tables has made me realize how quickly large amounts of data can be transformed into useful insights. It has also improved my attention to detail, especially when cleaning and organizing data, something I now understand is just as important as the analysis itself.