“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 thenet_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