---
title: "Challenge: Integrals in SQL?"
description: Last week I brought you a challenge on how to make derivatives in SQL. Evidently, this week we are looking into how to make integrals in SQL!
---

[DDBM Blog ](https://www.ddbm.com/en/blog)

# [Challenge: Integrals in SQL?](https://www.ddbm.com/en/blog/challenge-integrals-in-sql)

 Written by [Eloi Sanchez](https://www.ddbm.com/en/blog/author/eloi-sanchez) | 14 Apr, 2023

Last week I brought you a challenge on how to make derivatives in SQL. Evidently, this week we are looking into how to make integrals in SQL!

## The Challenge

Let's say that we have a table such as the following one

| x | y |
| --- | --- |
| 0 | 1 |
| 0.01672240803 | 1.001673601 |
| 0.03344481605 | 1.003349453 |
| 0.05016722408 | 1.005026144 |
| … | … |
| … | … |
| 4.949832776 | 1.323710024 |
| 4.966555184 | 1.467952331 |
| 4.983277592 | 1.57260469 |
| 5 | 1.634217159 |

The question is easy. Find the integral of `y(x)` from `x=0` to `x=5`. You should get a simple scalar for each integral that you make. For a reminder on what integrals are and how to calculate them you can watch [this amazing series by 3b1b](https://www.youtube.com/watch?v=rfG8ce4nNh0).

You can get the data here to generate the table here.

[integral_data](https://nimbusintelligence.com/wp-content/uploads/2023/04/integral_data.txt) [Download](https://nimbusintelligence.com/wp-content/uploads/2023/04/integral_data.txt)

In this table there are the following three functions.

`y1 = cos(x * x) * exp(0.1 * x)`

`y2 = 3 * x - 7`

`y3 = exp(- x ** 2)`

And the results of their integrations from 0 to 5 are

## Solution

In my case, I will use the [trapezoidal rule](https://en.wikipedia.org/wiki/Trapezoidal_rule). Basically, the area of each of the trapezoids below the known points of the function can be calculated using the following equation.

After that, it is just a matter of creating the correct query using the lead functions in SQL.

```
with traps_res as (
            select
                0.5 * (
                    lead(x) OVER (ORDER BY x ASC) * y1
                    - x * y1
                    + lead(x) OVER (ORDER BY x ASC) * lead(y1) OVER (ORDER BY x ASC)
                    - x * lead(y1) OVER (ORDER BY x ASC)
                    )
             from funcs
        ) select sum(*) as integration_result from traps_res;
```

Note that we first have to create a CTE that stores the area of each of the trapezoids and then we have to sum all of them. I have also created a procedure (in Snowflake) that takes the column name as an argument and returns the result of the integral.

```
CREATE OR REPLACE procedure integrate(colname varchar)
    returns table(integration_result float)
    language sql
    AS
    DECLARE
        sql_query VARCHAR;
        res RESULTSET;
    BEGIN
        sql_query := 'with traps_res as (
            select
                0.5 * (
                    lead(x) OVER (ORDER BY x ASC) * ' || colname || '
                    - x * ' || colname || '
                    + lead(x) OVER (ORDER BY x ASC) * lead(' || colname || ') OVER (ORDER BY x ASC)
                    - x * lead(' || colname || ') OVER (ORDER BY x ASC)
                    )
             from funcs
        ) select sum(*) as integration_result from traps_res';
        res := (EXECUTE IMMEDIATE :sql_query);
        return TABLE(res);
    END
    ;
```

Calling this for our three functions, we obtain the following results

```
call integrate('y1');  --> 0.6016285595
call integrate('y2'); --> 2.5
call integrate('y3'); --> 0.8862269255
```

Do you have another solution? Send it over to [eloi.sanchez@ddbm.com](mailto:eloi.sanchez@ddbm.com)!

### Bonus question

Do you know why the integral of y1 produces the approximate result but the integral of y2 produces the *exact* one?

[View full post](https://www.ddbm.com/en/blog/challenge-integrals-in-sql)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Eloi Sanchez"
  },
  "dateModified" : "2026-01-12T12:54:06.978Z",
  "datePublished" : "2023-04-14T07:15:00Z",
  "headline" : "Challenge: Integrals in SQL?",
  "image" : {
    "@type" : "ImageObject",
    "height" : 655,
    "url" : "https://139739125.fs1.hubspotusercontent-eu1.net/hubfs/139739125/Imported_Blog_Media/integrals_results-1024x655.png",
    "width" : 1024
  },
  "mainEntityOfPage" : "https://www.ddbm.com/en/blog/challenge-integrals-in-sql",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "DDBM Blog"
  }
}
```