DEV Community: Abdi Yussuf

SQL Deep Dive: Subqueries vs. CTEs — Which One Should You Use?

Abdi Yussuf — Sun, 19 Apr 2026 22:37:29 +0000

When your data questions get harder, you need to perform calculations before you perform your final SELECT. This is where Subqueries and Common Table Expressions (CTEs) come in.

1. What is a Subquery?

A subquery is a query nested inside another query. It acts as a temporary result set that the outer query uses to filter or calculate data.

The 4 Types of Subqueries

A. Scalar Subquery

Returns exactly one value (one row and one column).

Use Case: When you need a single number, like an average or a maximum, to compare against other rows.

SELECT first_name, mark
FROM exam_results
WHERE mark > (SELECT AVG(mark) FROM exam_results);

B. Multi-Row Subquery

Returns multiple rows but only one column. Usually used with IN, ANY, or ALL.

Use Case: To filter results based on a list of IDs from another table.

SELECT first_name
FROM students
WHERE student_id IN (
    SELECT student_id 
    FROM exam_results 
    WHERE mark < 40
);

C. Correlated Subquery

A subquery that references a column from the outer query. It runs once for every row the outer query processes.

Use Case: When the inner calculation depends on the specific row being looked at (e.g., comparing a student's mark to their class average, not the global average).

SELECT student_id, mark
FROM exam_results e1
WHERE mark > (
    SELECT AVG(mark)
    FROM exam_results e2
    WHERE e1.class_id = e2.class_id
);

D. Table Subquery (Derived Table)

Returns multiple rows and multiple columns. It is used in the FROM clause like a temporary table.

SELECT AVG(temp.total_marks)
FROM (
    SELECT student_id, SUM(mark) AS total_marks
    FROM exam_results
    GROUP BY student_id
) AS temp;

2. What are CTEs (Common Table Expressions)?

A CTE is a temporary result set defined before your main query using the WITH keyword. Think of it like creating a "virtual table" that exists only for that query.

Types & Use Cases

Standard CTE: Best for breaking down long, "spaghetti" code into readable steps
Recursive CTE: Used for hierarchical data, like an organization chart or a category tree

Example of a Standard CTE

WITH HighAchievers AS (
    SELECT student_id, AVG(mark) AS avg_score
    FROM exam_results
    GROUP BY student_id
)
SELECT s.first_name, ha.avg_score
FROM students s
JOIN HighAchievers ha 
    ON s.student_id = ha.student_id
WHERE ha.avg_score > 80;

3. Subqueries vs. CTEs: The Comparison

Feature	Subqueries	CTEs
Readability	Becomes hard to read when deeply nested	Very clean; reads top-to-bottom like a story
Performance	Great for simple, scalar lookups	Modern optimizers treat them similar to subqueries
Reusability	Must be rewritten if used multiple times	Can be referenced multiple times in one query
Recursion	Not supported	Supported (Recursive CTEs)

When to Use Which?

Use a Subquery for very simple, one-off logic inside a WHERE clause
Use a CTE for everything else. If your query has more than one join or involves multiple steps of logic, a CTE will make your life (and your teammates' lives) much easier

Conclusion

While both tools allow you to perform complex logic, CTEs are the winner for modern SQL development due to their organization and readability.

Next time you find yourself nesting a query inside another, try rewriting it as a CTE!

Stop Collapsing Your Data: Why Window Functions Are a Game Changer

Abdi Yussuf — Sun, 19 Apr 2026 22:02:54 +0000

If you've been learning SQL for a few weeks, you're probably comfortable with GROUP BY. It's the bread and butter of data analysis. But have you ever noticed that the moment you use it, your individual rows disappear?

Today, we're going to talk about Window Functions — the secret weapon that lets you keep your data and calculate it, too.

The Problem with `GROUP BY`

Imagine you have a table of student exam marks. Your boss asks for a report showing each student's ID, their mark, and the class average.

If you use GROUP BY class, SQL collapses all those individual exam rows into one single row per class. You get the average, but you lose the student IDs and the individual marks.

To get both, you'd have to write a messy subquery or a complex join.

The Solution: The `OVER()` Clause

Window functions (using the OVER clause) perform a calculation across a set of rows, but unlike GROUP BY, they do not collapse the rows. Every record remains visible.

1. The "Magic" Average

Instead of grouping, we use PARTITION BY. Think of this as "grouping without the shrinking."

SELECT
    student_id,
    mark,
    AVG(mark) OVER (PARTITION BY class) AS class_avg
FROM exam_results;

Result:

You see Student A, their score of 85, and the class average of 78 right next to it. No data was hidden.

2. Ranking Without Gaps

Have you ever tried to rank students using just ORDER BY? It's hard to handle ties. Window functions like RANK() and DENSE_RANK() handle this automatically.

RANK() → If two people tie for 1st, the next person is 3rd (it leaves a gap)
DENSE_RANK() → If two people tie for 1st, the next person is 2nd (no gaps)

SELECT
    student_id,
    mark,
    DENSE_RANK() OVER (ORDER BY mark DESC) AS position
FROM exam_results;

3. Looking Back with `LAG()`

One of the coolest things you can do is compare a row to the one above it. This is perfect for seeing if a student improved since their last exam.

LAG(mark) reaches back to the previous record for that specific student and pulls the value forward.

SELECT
    student_id,
    mark,
    LAG(mark) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_mark
FROM exam_results;

Summary

Use GROUP BY when you only need the high-level summary

(e.g., "What was the total revenue?")
Use Window Functions when you need to compare individual rows against a total or a previous value

(e.g., "How does this student compare to the average?")

Once you start using OVER(), you'll realize that SQL isn't just about fetching data — it's about telling a story with it.

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

Abdi Yussuf — Fri, 10 Apr 2026 15:41:02 +0000

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

In today’s data-driven world, organizations rely heavily on tools that transform raw data into meaningful insights. Power BI, developed by Microsoft, is one of the most powerful business intelligence tools for data visualization and reporting.

After building dashboards in Power BI Desktop, the next step is sharing them. In this guide, you’ll learn how to:

Publish a Power BI report
Generate an embed code
Embed it into a website using an iframe

What is Power BI Publishing?

Publishing means uploading your .pbix report from Power BI Desktop to the Power BI Service (cloud platform).

Once published, your report can be:

Shared online
Embedded into websites
Accessed from anywhere

🔹 Step 1: Create a Workspace

A workspace is where your reports and datasets live in Power BI Service.

Steps:

Go to https://app.powerbi.com
Log in
Click Workspaces → New workspace
Add a name and description
Click Save

Screenshot: Creating Workspace

🔹 Step 2: Publish Your Report

Steps:

Open your report in Power BI Desktop
Click Home → Publish
Sign in
Select your workspace
Click Select

✔ You’ll see: “Successfully published”

Screenshot: Publishing Report

🔹 Step 3: Access Report in Power BI Service

Steps:

Go to Power BI Service
Open your workspace
Click your report

Screenshot: Report in Service

🔹 Step 4: Generate Embed Code

Important: This method makes your report public.

Steps:

Open your report
Click File → Embed → Publish to web
Click Create embed code
Confirm
Copy iframe code

Screenshot: Generate Embed Code

Example IFrame Code

<iframe 
    width="800" 
    height="600" 
    src="https://app.powerbi.com/view?r=YOUR_REPORT_ID" 
    frameborder="0" 
    allowFullScreen="true">
</iframe>

Step 5: Embed in a Website

Example

<!DOCTYPE html>
<html>
<head>
    <title>Power BI Report</title>
</head>
<body>

    <h2>Sales Dashboard</h2>

    <iframe 
        width="100%" 
        height="600" 
        src="https://app.powerbi.com/view?r=YOUR_REPORT_ID"
        frameborder="0"
        allowFullScreen="true">
    </iframe>

</body>
</html>

Explanation

The iframe allows your Power BI report to appear inside your website. Users can interact with filters, charts, and visuals directly.

Understanding IFrames

An iframe (inline frame) is an HTML element used to embed another webpage inside your webpage.

Benefits:

Easy to use
No backend required
Fully interactive
Works on any website

Secure Alternatives

Avoid Publish to Web for sensitive data.

🔹 Use instead:

Power BI Embedded (Azure)
Secure Embed

Limitations of Publish to Web

Public access
No authentication
Not suitable for confidential data

Best Practices

Use width="100%" for responsiveness
Keep height between 500–800px
Optimize your report before publishing
Add slicers for better interactivity
Use clear titles and labels

Real-World Use Cases

Portfolio projects
Business dashboards
Data storytelling
Reporting platforms

Key Insights

Publishing connects Power BI Desktop to the cloud
Workspaces help organize reports
IFrames simplify embedding
Best suited for public dashboards

Conclusion

Publishing and embedding Power BI reports is a crucial skill for modern data analysts. It allows you to transform your work into an interactive, shareable product.

By following this guide, you can:

Publish reports
Generate embed codes
Embed dashboards into websites

This makes your project not just an analysis, but a complete solution ready for real-world use.

How Excel is Used in Real-World Data Analysis

Abdi Yussuf — Tue, 24 Mar 2026 18:33:58 +0000

Introduction

When I first started learning Excel, I thought it was just a simple tool for typing numbers and doing basic calculations. But as I progressed to an intermediate level, I realized that Excel is actually a powerful tool used in real-world data analysis. Today, many organizations rely on Excel to make decisions, track performance, and understand data better.

In this article, I’ll explain what Excel is, how it is used in real life, and share some examples of features and formulas I’ve learned along the way.

What is Excel?

Microsoft Excel is a spreadsheet application that allows users to organize data into rows and columns. It also provides tools for performing calculations, analyzing information, and creating charts.

The best part about Excel is that it is easy to start with, but it becomes more powerful as you learn advanced features.

How Excel is Used in Real-World Scenarios

Business and Finance

In businesses, Excel is used to track income, expenses, and budgets. For example, a company can record daily sales and calculate total revenue at the end of the month. This helps managers make better financial decisions.

Data Cleaning and Organization

In real life, data is often messy. Excel helps clean data using tools like:

Remove duplicates
Sorting
Filtering

Data Analysis and Reporting

Excel helps analyze data and generate reports. For example, a company can analyze sales data to identify which products are performing well.

Education and Research

Students and researchers use Excel to analyze survey results, calculate averages, and present findings clearly.
Features and Formulas I Have Learned

IF Function

The IF function helps make decisions in Excel.

Example:

=IF((B2>50) + (C2>50), "Pass", "Fail")

This formula checks if a student scored more than 50.

VLOOKUP

VLOOKUP is used to find data from a table.

Example:

=VLOOKUP(A2, A2:D4, 4, FALSE)

This helps retrieve information like employee department's using his/her ID.

Pivot Tables

Pivot tables summarize large datasets quickly.

For example, I can use a pivot table to calculate sum of salary by department.

Charts and Visualization

Charts make it easier to understand data visually.

Some charts I’ve learned include:

Bar charts
Pie charts
Line graphs

Sorting and Filtering

These features help focus on specific data.

Sorting arranges data (e.g., highest to lowest)
Filtering shows only selected data

Practical Example

Let’s say I am working with sales data in Excel:

I enter the data into a worksheet

I clean it by removing duplicates

I create a pivot table to summarize sales
I use charts to visualize trends

Personal Reflection

Learning Excel has really changed how I see data. Before, I used to look at numbers and feel confused. Now, I can organize data, analyze it, and actually understand what it means.

I’ve learned that data is not just numbers—it tells a story. Excel helps me find that story. It has also given me confidence, especially as I’m interested in moving into a more technical and data-focused career.

Conclusion

Excel is an important tool in today’s world. It is used in business, education, and many other fields to analyze and understand data.

As a student, learning Excel has been a valuable experience for me, and I look forward to learning more advanced tools in data analysis.

DEV Community: Abdi Yussuf

SQL Deep Dive: Subqueries vs. CTEs — Which One Should You Use?

1. What is a Subquery?

The 4 Types of Subqueries

A. Scalar Subquery

B. Multi-Row Subquery

C. Correlated Subquery

D. Table Subquery (Derived Table)

2. What are CTEs (Common Table Expressions)?

Types & Use Cases

Example of a Standard CTE

3. Subqueries vs. CTEs: The Comparison

When to Use Which?

Conclusion

Stop Collapsing Your Data: Why Window Functions Are a Game Changer

The Problem with GROUP BY

The Solution: The OVER() Clause

1. The "Magic" Average

2. Ranking Without Gaps

3. Looking Back with LAG()

Summary

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

What is Power BI Publishing?

🔹 Step 1: Create a Workspace

Steps:

Screenshot: Creating Workspace

🔹 Step 2: Publish Your Report

Steps:

Screenshot: Publishing Report

🔹 Step 3: Access Report in Power BI Service

Steps:

Screenshot: Report in Service

🔹 Step 4: Generate Embed Code

Steps:

Screenshot: Generate Embed Code

Example IFrame Code

Step 5: Embed in a Website

Example

Explanation

Understanding IFrames

Benefits:

Secure Alternatives

🔹 Use instead:

Limitations of Publish to Web

Best Practices

Real-World Use Cases

Key Insights

Conclusion

How Excel is Used in Real-World Data Analysis

Introduction

What is Excel?

How Excel is Used in Real-World Scenarios

The Problem with `GROUP BY`

The Solution: The `OVER()` Clause

3. Looking Back with `LAG()`