#13 Recursion

SQL Crash Course #13

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

Hi everyone! Cornellius and Josep Ferrer from DataBites 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 Thursday, we already saw:

📌 #11 – Subqueries
📌 #12 – CTEs

Today, we will continue our learning by exploring two new brand issues for the advanced SQL:

📌 #13 – Recursive: the course you are currently learning, we will explore how to handle hierarchical data and repeated query logic efficiently in CTEs.

📌 #14 - Views: Josep Ferrer in DataBites will teach everything about saved SQL query that acts like a virtual table (View).

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

---

🧱 What is a Recursion?

We have learn previously about CTEs, which act as named temporary results that we can reference. When we discuss recursion, it often refers to Recursive Common Table Expressions (CTEs).

A Recursive CTE is a CTE that references itself, enabling you to handle hierarchical or sequential data in a loop-like manner. It has two parts:

1. Anchor Member: The starting point (base case).

2. Recursive Member: The part that references the CTE itself (iterative step).

In basic recursion, it often follows the structure query below:

WITH RECURSIVE cte_name AS (
  -- Anchor Member (Starting Point)
  SELECT initial_columns
  FROM base_table
  WHERE initial_condition
  
  UNION ALL
  
  -- Recursive Member (Looping Step)
  SELECT recursive_columns
  FROM base_table
  JOIN cte_name ON recursive_condition
)
SELECT * FROM cte_name;

🔍 What Happens Here?

1. Anchor Member (Initial Query):

   • This is the base case.

   • It selects the initial row(s) to begin recursion.

   • It is executed once and provides the starting dataset.

2. Recursive Member (Recursive Query):

   • This is the looping part of the CTE.

   • It uses the result of the previous step (including itself!) and continues processing.

   • It is repeatedly executed until it returns no new rows.

3. UNION ALL:

   • Combines anchor and recursive queries.

   • You can use UNION if you want to eliminate duplicates, but UNION ALL is preferred for Performance unless duplicates are an issue.

4. Termination Condition:

   • Built into the recursive part via the WHERE clause.

   • The recursion stops when the recursive part produces no new rows.

✅ When to Use SQL Recursion:

👉 Working with hierarchical data (organizational charts, family trees)

👉 Calculating running totals

👉 Traversing paths and networks (routes, navigation trees)

👉 Implementing iterative logic directly in SQL

Let’s see how recursion is in action👇

---

#1. Dynamic Sequence Construction

Specific Example: Recursive Date Sequence of Interaction Days

🔍 Generate a continuous range of dates between the first and last interaction. Useful for time-series dashboards, filling missing days, or aligning daily engagement metrics.

WITH RECURSIVE InteractionDates AS (
    SELECT MIN(DATE(datetime)) AS interaction_date
    FROM interactions

    UNION ALL

    SELECT DATE_ADD(interaction_date, INTERVAL 1 DAY)
    FROM InteractionDates
    WHERE interaction_date < (SELECT MAX(DATE(datetime)) FROM interactions)
)
SELECT interaction_date
FROM InteractionDates;

🧠 Why Recursion: Recursive logic enables SQL to dynamically construct sequences (such as dates or steps) without relying on pre-existing tables or static loops.

---

#2. Recursive Accumulation Logic

Specific Example: Running Total of Points per Post

🔍 Calculate a running total of interaction points per post over time, allowing visibility into engagement growth.

WITH RECURSIVE PointsRanking AS (
    SELECT 
        id,
        post_id,
        datetime,
        points,
        points AS running_total,
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY datetime) AS rn
    FROM interactions

    UNION ALL

    SELECT
        i.id,
        i.post_id,
        i.datetime,
        i.points,
        pr.running_total + i.points AS running_total,
        pr.rn + 1
    FROM interactions i
    JOIN PointsRanking pr 
      ON i.post_id = pr.post_id
     AND i.datetime > pr.datetime
    WHERE NOT EXISTS (
        SELECT 1 FROM interactions i2
        WHERE i2.post_id = pr.post_id
          AND i2.datetime > pr.datetime
          AND i2.datetime < i.datetime
    )
)
SELECT post_id, datetime, points, running_total
FROM PointsRanking
ORDER BY post_id, datetime;

🧠 Why Recursion: Recursive accumulation simulates a gradual buildup of values, enabling logic that simple aggregation alone cannot solve, such as conditional running totals or chained calculations.

---

#3. Progressive Ordering and Comparison

Specific Example: Recursive Post Order Timeline for a Newsletter

🔍 Recursively order posts from a newsletter by their publication date and measure gaps between releases.

WITH RECURSIVE post_sequence AS (
  -- Anchor: Earliest post in the newsletter
  SELECT 
    id,
    name,
    published_at,
    1 AS post_order,
    CAST(NULL AS SIGNED) AS days_since_previous
  FROM posts
  WHERE newsletter_id = '1112A'
    AND published_at = (
      SELECT MIN(published_at) 
      FROM posts 
      WHERE newsletter_id = '1112A'
    )

  UNION ALL

  -- Recursive step: find next-later post
  SELECT 
    p.id,
    p.name,
    p.published_at,
    ps.post_order + 1,
    DATEDIFF(p.published_at, ps.published_at) AS days_since_previous
  FROM posts p
  JOIN post_sequence ps ON p.newsletter_id = '1112A'
  WHERE p.published_at > ps.published_at
    AND NOT EXISTS (
        SELECT 1 
        FROM posts p2
        WHERE p2.newsletter_id = '1112A'
          AND p2.published_at > ps.published_at
          AND p2.published_at < p.published_at
    )
)
SELECT * 
FROM post_sequence
ORDER BY post_order;

🧠 Why Recursion: Recursion enables SQL to navigate through data chronologically or hierarchically by iteratively comparing rows.

---

🎯 Summary: Why You Should Use Recursion

• Dynamic Generation: Create custom sequences (like dates) without depending on external tables.

• Logical Flow: Imitate loops in SQL for step-by-step calculations.

• Readable Iteration: Substitute messy subqueries with clear, recursive logic.

• Layered Computation: Chain intermediate steps while maintaining clarity.

• No Extra Tables: Generate what you need on the fly, directly in the query.

💡 Pro Tip: Recursive CTE vs Loops or Subqueries

While loops and nested subqueries can handle complex logic, prefer Recursive CTEs when:

✅ You need to generate sequences or navigate hierarchies

✅ You want SQL-native iteration without procedural code

✅ You’re modeling step-by-step logic, such as running totals or rankings

✅ You want clean, readable recursion within a single query block

👉🏻 SQL playground with the Recursion example

---

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