Skip to main content

Today I bring you a challenge! Just a fun little exercise to train your SQL skills. We are going to try to make derivatives in SQL.

The Challenge

Let’s say that you are working in a company and you are given a time series with two columns, the day and the revenue per day. Then, your boss comes in and says that they actually want to analyze the growth of the revenue at every day. After a bit of thinking, you get the best idea: use derivatives!

In our case you can copy and paste the following to get some mock data, though you can youse any x and f(x) that you want. Make sure you edit the CREATE statements to fit the flavour of SQL that you are using. In my case, I am running this in Snowflake.

CREATE DATABASE math;

CREATE TABLE funcs(
    x float,
    y float
);

INSERT INTO math.public.funcs VALUES
(0.0, 1.0),
(0.016722408026755852, 1.0016736006126223),
(0.033444816053511704, 1.003349452945014),
(0.05016722408026755, 1.00502614428687),
(0.06688963210702341, 1.0067013077936056),
...
-- You can get the full statement in the file below
...
(4.949832775919732, 1.3237100235281762),
(4.966555183946488, 1.4679523308472862),
(4.983277591973244, 1.5726046898410695),
(5.0, 1.6342171594970862);

Reminder: What is a derivative?

For those that finished school some time ago, you can get a refresher on what a derivative is in this incredible series on introductory calculus by 3blue1brown.

For those more pragmatic that just whant to be able to do the challenge, here are the formulas that we are most interested in.

That’s it. You should now be able to provide a view that produces the columns x, y (same as f(x)) and the forward, backward and central derivatives.

What you should get

You should get a view that when queried produced the following table

xyforwardbackwardcentral
010.1000813166nullnull
0.016722408031.0016736010.10021596950.10008131660.1001486431
0.033444816051.0033494530.10026614220.10021596950.1002410559
0.050167224081.0050261440.10017477770.10026614220.10022046
4.9498327761.3237100248.62568997810.7260048399.675847408
4.9665551841.4679523316.2582110688.6256899787.441950523
4.9832775921.572604693.6844256856.2582110684.971318377
51.634217159null3.684425685null

Solution

Well, if you look at the formulas that we have to use, you will notice the i, i+1 and i-1 subindices on the values. Of course, this is the perfect scenario for the LAG and LEAD function.

In fact, this is the only thing that we have to think about. The rest are just simple subtraction and division operations. Below you can see my solution with the full code to get the derivatives in SQL.

CREATE OR REPLACE VIEW derivatives AS
    WITH calculations AS (
        SELECT
            -- x and y values
            x,
            y,
            -- forward calculation
            (LEAD(y, 1) OVER (ORDER BY x ASC) - y) as fdy,
            (LEAD(x, 1) OVER (ORDER BY x ASC) - x) as fdx,
            fdy / fdx as fdydx,
            -- central calculation
            (LEAD(y, 1) OVER (ORDER BY x ASC) -  LAG(y, 1) OVER (ORDER BY x ASC)) as cdy,
            (LEAD(x, 1) OVER (ORDER BY x ASC) - LAG(x, 1) OVER (ORDER BY x ASC)) as cdx,
            cdy / cdx as cdydx,
            -- backward calculation
            (y - LAG(y, 1) OVER (ORDER BY x ASC)) as bdy,
            (x - LAG(x, 1) OVER (ORDER BY x ASC)) as bdx,
            bdy / bdx as bdydx
        FROM funcs
    )
    SELECT
        x,
        y,
        fdydx AS forward,
        bdydx AS backward,
        cdydx AS central
    FROM calculations;

SELECT * FROM derivatives;

Do you have another solution? Send it over to eloi.sanchez@nimbusintelligence.com!

Are you into Python as well? Make sure to check another interesting blog about Python’s tricky stuff. Do you tend to like more visual stuff? Well, maybe try out this one about data visualization in Streamlit!

Auteur

  • Eloi Sanchez

    Eloi studied Chemistry at the University of Barcelona, but rapidly leaned on the mathematical and physical part of the field, called Physical Chemistry. Later on, he studied a Master's in Atomistic and Multiscale Computational Modelling, were he focused on Computational Quantum Physics. During the last years, he is been mostly interested in the Data field and is currently studying a Master's in Data Science. He likes to spend its time in nature and he is an animal lover. There are no specific hobbies that define him because he is usually always trying new things, although bouldering, playing chess and videogames and hiking are recurrent in his daily life. When looking for new opportunities in the Data job market, he found the Nimbus Intelligence Academy. It is the perfect step for him in order to formalize the switch from academy to the private sector, and it is an incredible opportunity for professional and personal growth.

Eloi Sanchez

Eloi studied Chemistry at the University of Barcelona, but rapidly leaned on the mathematical and physical part of the field, called Physical Chemistry. Later on, he studied a Master's in Atomistic and Multiscale Computational Modelling, were he focused on Computational Quantum Physics. During the last years, he is been mostly interested in the Data field and is currently studying a Master's in Data Science. He likes to spend its time in nature and he is an animal lover. There are no specific hobbies that define him because he is usually always trying new things, although bouldering, playing chess and videogames and hiking are recurrent in his daily life. When looking for new opportunities in the Data job market, he found the Nimbus Intelligence Academy. It is the perfect step for him in order to formalize the switch from academy to the private sector, and it is an incredible opportunity for professional and personal growth.