#18 Generating Modular Code

SQL Crash Course #18

All the course material is stored in the SQL Crash Course repository.

Hi everyone! Josep and Cornellius Yudha Wijaya from Non-Brand Data here 👋🏻

As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀

I am sure you are here to continue our SQL Crash Course Journey!📚

If this is your first time or you’ve forgotten what we’ll cover, we will examine seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites.

📚 Previously in Advanced SQL…

Remember last time we already saw:

📌 #15 – CRUD Operations:
📌 #16 – Database Modification

In today’s topic, we will finish up the Database Operations topic with:

📌 #17 – Indexing and Optimization in

Non-Brand Data

by

Cornellius Yudha Wijaya

.

Today, we will also explore an exciting new topic in our SQL learning:

Modular Code ♻️

Which we will start with the exciting article:

📌 #18 – Generating Modular Code with CTEs

So don’t miss out—let’s keep the SQL momentum going!

🧱 Why Modular SQL Matters

SQL queries can become messy. Fast.

Without structure, they become:

• Hard to debug

• Painful to maintain

• Impossible to collaborate on

That’s where Common Table Expressions (CTEs) come in. They help you:

• Break down logic into readable steps

• Standardize metrics

• Improve collaboration with teammates

Let’s explore how to use CTEs with our newsletter schema! 👇🏻

What Are CTEs?

We first introduced Common Table Expressions (CTEs) back in Issue #12, where we learned how to break down complex queries into logical, manageable steps.

Missed it? You can check it out here 👇🏻

#12 Common Table Expressions (CTEs)

But just as a quick refresher… 💥

A CTE is a temporary, named result set defined using the WITH clause at the beginning of a SQL query. It acts like a mini-table that exists just for the duration of your query.

Think of CTEs as your best allies for writing modular SQL—they help you:

• isolate logic into clean blocks,

• reuse intermediate results,

• and write queries that are actually pleasant to read (yes, that’s possible!).

Today, we’re taking this one step further: not just what CTEs are, but how to structure entire queries around them to make your SQL modular, reusable, and collaborative.

Core Pattern for Modular SQL with CTEs

Here’s the generic structure you can apply to almost any SQL task:

WITH 
-- FIRST MODULE
step_one AS (
  -- Perform first transformation or filtering
  SELECT ...
  FROM ...
  WHERE ...
),
-- SECOND MODULE
step_two AS (
  -- Build on step one
  SELECT ...
  FROM step_one
  JOIN ...
  WHERE ...
)

-- FINAL SELECTION OF ALL THE DATA
SELECT *
FROM step_two;

🧠 Think of each WITH block as a module: focused, readable, and reusable.

Once you adopt this mindset, your SQL becomes dramatically easier to manage.

Scenario: Ranking Newsletter Posts by Total Engagement

Let’s say we want to know:

➡️ What are the top-performing posts per newsletter based on engagement (likes, shares, comments)?
➡️ How does each newsletter rank in terms of average post performance?

We could do all this in one big query—but that would be chaos.

Instead, let’s break it into four modular steps or modules using CTEs:

#1. Create a CTE for Post-Level Points

WITH post_points AS (
  SELECT 
    p.id AS post_id,
    p.name AS post_name,
    p.newsletter_id,
    SUM(i.points) AS total_points
  FROM posts p
  JOIN interactions i ON p.id = i.post_id
  GROUP BY p.id, p.name, p.newsletter_id
)

🧠 Why this matters: This isolates the aggregation logic and gives us a clean, reusable post_points table to reference later.

#2. Create a CTE to Rank Posts Within Each Newsletter

WITH 

post_points AS (...), # MODULE 1

ranked_posts AS (
  SELECT 
    pp.*,
    RANK() OVER (PARTITION BY newsletter_id ORDER BY total_points DESC) AS rank_within_newsletter
  FROM post_points pp
)

🧠 Why this matters: You can now easily query for “top 3 posts per newsletter” by filtering rank_within_newsletter.

#3. Create a CTE for Newsletter-Level Averages

WITH 

post_points AS (...), # MODULE 1
ranked_posts AS (...), # MODULE 2

newsletter_avg_points AS (
  SELECT 
    newsletter_id,
    AVG(total_points) AS avg_post_score
  FROM post_points
  GROUP BY newsletter_id
)

🧠 Why this matters: Instead of repeating AVG(SUM(...)) logic later, we modularize it for reuse and clarity.

#4. Final Output: Join Everything Together

WITH 

post_points AS (...), # MODULE 1
ranked_posts AS (...), # MODULE 2
newsletter_avg_points AS (...) # MODULE 3

SELECT 
  n.name AS newsletter_name,
  rp.post_name,
  rp.total_points,
  rp.rank_within_newsletter,
  nap.avg_post_score
FROM ranked_posts rp
JOIN newsletters n ON n.id = rp.newsletter_id
JOIN newsletter_avg_points nap ON nap.newsletter_id = rp.newsletter_id
WHERE rp.rank_within_newsletter <= 3
ORDER BY n.name, rp.rank_within_newsletter;

🧠 Final Output: Clean. Clear. Collaborative.

You now have a query that:

• Is readable and logically structured

• Avoids deeply nested subqueries

• Can be reused and adapted easily

🔑 Why the WITH Clause is a Game-Changer

Each temporary table (CTE) lets you:

• Modularize logic like functions in other languages

• Prevent duplication of logic (e.g., total points calculation)

• Improve performance in some engines with materialization

• Debug individual steps by running them separately

Just like writing good Python or R code, clean SQL pays off 💡

Best Practices for Modular SQL

• Name your CTEs descriptively (e.g., post_points, newsletter_avg_points)

• Keep each CTE short and focused

• Avoid subqueries when they can be broken out into CTEs

• Document steps in comments if sharing queries with teams

• Always test each CTE independently before chaining them

---

Conclusions – Think Like a Developer

You can write SQL like spaghetti 🍝 — a tangled mess of subqueries and logic.

Or like LEGO 🧱 — modular, understandable blocks that click together.

CTEs help you break big problems into manageable parts:

• Reusable

• Maintainable

• Debuggable

• Scalable

Once you start thinking in modules, your SQL transforms from painful to powerful.

And once you get the hang of it—you’ll never go back.

👉🏻 SQL playground with all modular examples

---

How to Get Started 🚀

Over the coming weeks, we’ll guide you through:

✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries

Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.

What’s Next? ➡️

This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.

To get the full experience and fully immersed in the learning:

👉 Subscribe to Databites.tech (By Josep)

👉 Subscribe to Non-Brand Data (By Cornellius)

👉 Check out the SQL Crash Course GitHub repo

👉 Share with your friend and whoever needs it!

🗓️ Every Thursday, you will have two new issues in your inbox!

Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀

Josep & Cornellius