“Mars is there, waiting to be reached.” — BUZZ ALDRIN

Traveling and vacationing on Mars — it’s the stuff of science fiction. Mars, a neighboring plant to Earth, has deep valleys and the tall mountains. One mountain is Olympus Mons — with a staggering height of 72,000 ft (two and a half times Mount Everest’s height above sea level). It is a shield volcano, much like the the large volcanoes making up the Hawaiian Islands.

Although Mars’ environment is hostile to humans (one characteristic is the amount of radiation received on the surface could pose health risks), I’m going to create a scenario that allows us to**_ imagine_** vacationing on this red planet. Suppose we spent 4 years on a spacecraft traveling to the planet. Among seeing many sites on the planet, we are visiting Olympus Mons this week. At the mountain base, a food stand is selling astronaut ice cream and other freeze dried foods.

Today’s SQL post uses fake data for the purpose of showcasing the `LAG` window function. Let’s begin.

# Creating the data

Since this is a hypothetical scenario, I am going to create the monthly sales imaginary data for the food stand.

Here, I create the table named `sales`:

``````CREATE TABLE sales (
months INT,
net_sales FLOAT
);
``````

And, then I insert data into the table:

``````INSERT INTO sales(months, net_sales)
VALUES (1, 8964),
(2, 4212),
(3, 5618),
(4, 7319),
(5, 9282);
``````

I finally view my work with a `SELECT` statement:

So, there are 5 records of `net_sales`, each for a month that the food stand is open.

# LAG window function

Suppose I wanted to look at the net sales from the previous month while at the same time viewing the current month. This is possible with the `LAG` window function. This allows us to access a row at a specific offset that comes before the current row. In terms of our example, if the current row is the 4th `month` and the`net_sales` value is 7319, then with an offset of 1 I expect to see 5618 in the next column for that row. Similarly for the other records, we can expect the following.

#sql #lag #function

1.05 GEEK