# Hands-On Tutorial: Window Recipe (Deep Dive)[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#hands-on-tutorial-window-recipe-deep-dive "Permalink to this headline")

For all the potential that they offer, window functions can be tricky to code successfully. The visual Window recipe in Dataiku, however, allows you to take advantage of these powerful features without coding.

## Let’s Get Started![¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#let-s-get-started "Permalink to this headline")

The previous hands-on tutorial provided an introduction to the Window recipe, but mastering all of the possible variations of this recipe can be challenging.

Accordingly, this tutorial includes **five** more examples using the same credit card transactions use case–each of which demonstrates a different aspect:

* basic grouped aggregation,

* ranking,

* a cumulative sum,

* a moving average, and

* a lagged calculation.

Note

We encourage completing this tutorial to strengthen your ability to use the Window recipe. However, future lessons do not depend on the specific work done here, and so for that reason, it is optional.

This lesson assumes that you have basic knowledge of working with Dataiku DSS datasets and recipes.

Note

If not already on the Advanced Designer learning path, completing the Core Designer Certificate is recommended.

To complete the Advanced Designer learning path, you’ll need access to an instance of Dataiku DSS (version 8.0 or above) with the following plugins installed:

* Census USA (minimum version 0.3)

* Reverse geocoding

These plugins are available through the Dataiku Plugin store, and you can find the instructions for installing plugins in the reference documentation. To check whether the plugin is already installed on your instance, go to the **Installed** tab in the Plugin Store to see a list of all installed plugins.

Note

If your goal is to complete **only** the tutorials in Visual Recipes 102, the Census USA plugin is not required.

In order to get the most out of this lesson, we recommend completing the following lessons beforehand:

* Concept: Window Recipe

### Workflow Overview[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#workflow-overview "Permalink to this headline")

In this tutorial, we’ll be demonstrating five Window recipes with different window definitions to answer various kinds of questions.

## Create Your Project[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#create-your-project "Permalink to this headline")

* Click **+New Project > DSS Tutorials > Advanced Designer > Window Recipe (Tutorial)**.

Note

If you’ve already completed the other Window recipe hands-on tutorial, you can use the same project.

Note

You can also download the starter project from this website and import it as a zip file.

Aside from the input datasets, all of the others are empty managed filesystem datasets.

You are welcome to leave the storage connection of these datasets in place, but you can also use another storage system depending on the infrastructure available to you.

To use another connection, such as a SQL database, follow these steps:

* Select the empty datasets from the Flow. (On a Mac, hold Shift to select multiple datasets).

* Click **Change connection** in the “Other actions” section of the Actions sidebar.

* Use the dropdown menu to select the new connection.

* Click **Save**.

Note

For a dataset that is already built, changing to a new connection clears the dataset so that it would need to be rebuilt.

Note

Another way to select datasets is from the **Datasets** page (G+D). There are also programmatic ways of doing operations like this that you’ll learn about in the Developer learning path.

The screenshots below demonstrate using a PostgreSQL database.

* Whether starting from an existing or fresh project, ensure that the dataset *transactions\_analyze\_windows* is built. It is the same as the *transactions\_joined\_prepared* dataset, but with a smaller selection of columns for convenience.

* From the Flow, select the end dataset required for this tutorial: *transactions\_analyze\_windows*

* Choose **Build** from the Actions sidebar.

* Choose **Recursive > Smart reconstruction**.

* Click **Build** to start the job, or click **Preview** to view the suggested job.

* If previewing, in the **Jobs** tab, you can see all the activities that Dataiku will perform.

* Click **Run**, and observe how Dataiku progresses through the list of activities.

## Basic Grouped Aggregation with the Window Recipe[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#basic-grouped-aggregation-with-the-window-recipe "Permalink to this headline")

The Window recipe has many similarities with the Group recipe.

For example, we could find out the average purchase amount of every *card\_id* with a simple Group recipe. However, the output would dataset would be reduced to the group key and the aggregated columns. The structure of the original dataset would be lost, which would make it more difficult to use those aggregations as features.

* With the *transactions\_analyze\_windows* dataset selected, choose **Actions > Window** in the right panel to create a new Window recipe.

* Name the output dataset `avg\_purchase\_by\_card`.

Warning

When working with file-based datasets and using the default DSS engine to run recipes, **Order Columns** and **Window Frame** should be activated in order for the Window recipe to output the same results as when working with most SQL-based datasets. For this reason, some of the examples below require slightly different Window definitions.

* On the Window definition step, turn on **Partitioning Columns**, and choose *card\_id*.

* Turn on **Order Columns** (the unique identifier *transaction\_id* should already be selected).

* Turn on **Window Frame**, but do not limit any rows.

* On the Window definition step, turn on **Partitioning Columns**, and choose *card\_id*.

Note

When we partition by columns in the Window recipe, we are not creating separate datasets. We are partitioning the dataset by the selected column(s), in our case, by *card\_id*. Dataiku DSS will create partitions, or groups, for each credit card, while keeping the data as a single dataset.

* Once the window is defined, on the Aggregations step, add the **Avg** of *purchase\_amount*.

* **Run** the recipe, updating the schema.

In the output dataset, filter for a single *card\_id* to more easily see that the new *purchase\_amount\_avg* column is the same for every unique *card\_id*.

We could have calculated the same average purchase results with a Group recipe. However, in this format, it would be easier to compute, for example, the difference between the average amount for a given card and the amount of an individual transaction, to get a notion of whether any particular transaction may be unusually high for this card.

## Compute Ranks[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#compute-ranks "Permalink to this headline")

The example above made use of a partitioning column, but not an order column. An **order column** makes possible many new kinds of aggregations.

For example, we might also be interested in questions like, “What is the most expensive purchase for every *card\_id*?” To answer this question, we’ll use a partitioning column, an order column, a rank aggregation, and finally a post-filter step.

* With the *transactions\_analyze\_windows* dataset selected, choose **Actions > Window** to create a new Window recipe.

* Name the output dataset `ranked\_purchases\_by\_card`.

Let’s start by defining the correct window.

* Just as in the above example, we are still interested in information per *card\_id*, and so choose this as the **Partitioning Column**.

* Turn on **Order Columns**. We want the most expensive purchase per *card\_id* to have a rank of 1, and so we want to order rows by *purchase\_amount* in descending order .

Then set the window frame depending on the connection in your project:

* Turn on the **Window Frame**, but do not limit any rows.

* Leave the **Window Frame** turned off.

* On the Window aggregations step, we only want to focus on one aggregation. Enable **Rank**, and then run the recipe.

Note

**Rank** is the “index” of the current row within its window. In other words, since we have a window partitioned by *card\_id*, and ordered by decreasing *purchase\_amount*, the most expensive transaction of each *item\_category* will have rank=1, the second most expensive transaction of each group will have rank=2, and so on.

In the output dataset, once again filter for a single *card\_id* to more easily notice how ranks are assigned according to the most expensive purchase per *card\_id*.

Tip

To find the most expensive purchase per card, return to the parent recipe, and apply a post-filter of *rank* == `1`.

## Compute Cumulative Sums[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#compute-cumulative-sums "Permalink to this headline")

None of the previous examples required actually limiting the window frame. Calculating a cumulative sum, however, does.

Imagine we’d like to know the proportion of the total purchase amounts made over time. In other words, we want to compute the cumulative distribution of the summed purchase amount. We’re no longer interested in partitioning by *card\_id*; we want global information.

* With the *transactions\_analyze\_windows* dataset selected, choose **Actions > Window** to create a new Window recipe.

* Name the output dataset `cumulative\_purchase\_sum`.

In the Window definition step, we’ll now use an order column and window frame, but not a partitioning column.

* Leave **Partitioning Columns** off.

* Under **Order Columns**, select to order by *purchase\_date*, and ensure that dates are sorted in ascending (chronological) order .

* Add *transaction\_id* as a second Order column.

* Activate the **Window Frame**, and limit the number of following rows to `0`.

Note

*transaction\_id* is included as a second order column because *purchase\_date* on its own does not guarantee a unique order (we have many purchases on the same day), and we are defining a window frame based on the number of preceding/following rows.

* On the Aggregations step, check the **Sum** box for *purchase\_amount*.

* Then **Run** the recipe.

Since the window is ordered by purchase date and framed so as to not take into account following rows, this will compute the sum of the purchase amount of all previous transactions, plus the current one, for each transaction.

Let’s create a chart to visualize the cumulative sum.

* Navigate to the **Charts** tab of *cumulative\_purchase\_sum*.

* Drag *purchase\_amount\_sum* to the Y-axis and *purchase\_date* as the X-axis.

* Change the Y-axis aggregation to **MAX**.

* Change the date range aggregation to “Month”.

To get the chart we want though, we’ll need to adjust the sample.

* Go to **Sampling & Engine** on the left panel

* Un-select **Use same sample as Explore**, and instead choose **No sampling (whole data)**.

* Then save and refresh the sample.

Since we have ordered the window by purchase date, and there are multiple transactions for each date, the *purchase\_amount\_sum* value will increase with each new transaction. By selecting MAX as the aggregation, we look at the evolution in the cumulative sum of all purchase amounts at the end of each period.

## Compute Moving Averages[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#compute-moving-averages "Permalink to this headline")

Now we’re finally ready to see a partitioning column, an order column, and a window frame all working together at once.

Instead of a single average purchase per card like in the first example, let’s create a moving average of the three most recent purchases on a given card (in addition to the present purchase). To do so, we’ll adjust the window frame setting to limit the window to the three preceding rows.

* With the *transactions\_analyze\_windows* dataset selected, choose **Actions > Window** to create a new Window recipe.

* Name the output dataset `moving\_avg\_purchase\_by\_card`.

Once you have created the recipe:

* Set *card\_id* as the **Partitioning Column**.

* Just like for the cumulative sum, set *purchase\_date* and *transaction\_id* in ascending order  as the **Order Column**.

* Activate the **Window Frame**, and limit the preceding rows to `3` (to include the three prior purchases) and the following rows to `0` (which includes the present row in the window frame).

* On the Aggregations step, check the **Avg** and **Sum** (for easier verification) box for *purchase\_amount*.

* Then **Run** the recipe.

In the output dataset, filter for a specific *card\_id* to observe how *card\_purchase\_amount\_avg* and *card\_purchase\_amount\_sum* change along the window frame.

For the output dataset, we can chart the moving average along with the monthly average.

* In the Charts tab of the *moving\_avg\_purchase\_by\_card* dataset, choose **Lines** as the chart type.

* Drag *purchase\_amount* and *purchase\_amount\_avg* to the Y-axis. (The aggregation should already be set to **AVG**).

* Drag *purchase\_date* to the X-axis.

* Change the date range from **Automatic** to **Month**.

* Once again, adjust the sample to include the entire dataset.

### Limit the Window Frame on a Value Range of the Order Column[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#limit-the-window-frame-on-a-value-range-of-the-order-column "Permalink to this headline")

Instead of finding the average and sum of the **three most recent purchases** for every card, we might instead want to know about the sum and average of any number of purchases in the **past three days** on that card.

We can find this answer with a slight adjustment to the window frame.

* Return to the Window definition step of the recipe that computes *moving\_avg\_purchase\_by\_card*.

* Remove *transaction\_id* as the second order column.

* Change the Window Frame setting to **Limit window on a value range of the order column**.

* Set the lower bound to `3` and the upper bound this time to `-1` days (This will exclude the present row).

* **Run** the recipe again.

When filtering the output dataset for a single *card\_id*, it’s possible to see the difference between these two window frames. For any card, there is no guarantee of a purchase every day, and so, the three previous rows and the three most recent days are not necessarily the same.

Note

When working with an irregular time series like this one, you might **resample** the data so no dates in the series are missing. This kind of operation can most easily be done with the Time Series Preparation Plugin, which you can learn about in this Academy course.

## Compute Lag[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#compute-lag "Permalink to this headline")

One final example to introduce the very handy **Lag** and **LagDiff** aggregations!

Perhaps we are interested in whether certain merchants attract more fraud. We might want to investigate questions like, for any failed transaction:

* How many days have passed since a merchant’s last transaction failed authorization?

* How many failed transactions has a merchant had in the prior three days?

The **Lag** and **LagDiff** aggregations can help answer these kinds of questions.

* From the *transactions\_analyze\_windows* dataset, create a new **Window** recipe.

* Name the output `lagged\_merchant\_failures`.

For the questions above, we are only interested in transactions that have failed authorization so we’ll need to use a pre-filter.

* On the **Pre-filter** step, activate the filter, and add the condition to keep only rows where *authorized\_flag* == `0`.

Our questions concern merchants and time so this determines our partitioning and order columns.

* On the Window definitions step, add *merchant\_id* instead of *card\_id* as the **Partitioning Column**.

* Add *purchase\_date* in ascending order  as the **Order Column** to put transactions in chronological order (per *merchant\_id*).

* We’ll use the same **Window Frame** as the previous example. Limit the window on the order column with a lower bound of `3` days and an upper bound of `-1` to exclude the present day.

Now let’s move on to the aggregations.

* For simplicity, turn off **Retrieve** for unneeded columns: *transaction\_id*, *authorized\_flag*, and *card\_id*.

* Include the following four aggregations:

+ the “Sum” of *authorized\_flag* as a sanity check (it should always be 0 if our pre-filter is working);

+ the “Count” of *authorized\_flag* to report the number of failed transactions for that merchant within the window frame;

+ the “Lag” of *purchase\_date* to report the date of that merchant’s last failed transaction (the default offset of 1 is correct);

+ the “LagDiff” of *purchase\_date* in days to report the number of days since that merchant’s last failed transaction.

After running the recipe, explore the output dataset, and apply a filter for one *merchant\_id*. Confirm the following for yourself:

* Rows are in increasing chronological order for every distinct merchant.

* After the pre-filter, the column *authorized\_flag* should always be 0, and so its sum should also be 0.

* *purchase\_date\_lag* is the date of the previous row. Having access to a lagged value can be useful on its own or for further manipulation in a Formula step later.

* *purchase\_date\_lag\_diff* is the difference between the date of the previous and present rows. We could have calculated this ourselves with *purchase\_date* and *purchase\_date\_lag* in a Custom Aggregation or Formula, but the LagDiff function saves us the trouble.

To give one specific example, on Jan 16, the three prior days (13, 14, 15) had a combined six transactions that failed authorization!

## What’s Next?[¶](https://knowledge.dataiku.com/latest/courses/advanced-data-prep/visual-recipes-102/window/window-deep-dive.html#whats-next "Permalink to this headline")

The Window recipe opens a world of possibilities. As shown here, it can be used to add grouped calculations as a column, compute cumulative sums or moving averages, as well as lagging differences.

You can get even more practice with another Window recipe tutorial in this article.
