smoothsetr.blogg.se

Redshift window functions
Redshift window functions











redshift window functions
  1. REDSHIFT WINDOW FUNCTIONS HOW TO
  2. REDSHIFT WINDOW FUNCTIONS PLUS
  3. REDSHIFT WINDOW FUNCTIONS FREE

REDSHIFT WINDOW FUNCTIONS FREE

Step 2: Mark Starts and End Dates of Free Call Periods

REDSHIFT WINDOW FUNCTIONS PLUS

He topped-up €20 or more twice on the same day, but the next top-up is 45 days away, so we expect one free call interval from to (which is plus 28 days). The second occurrence is for the customer with id 5 and is coloured in green. Similarly, if the next top-up is more than 28 days ahead, then we know that the current top-up is the last of the free call interval. We know that if the previous top-up was more than 28 days ago, the current date is the starting date of a new interval.

redshift window functions

This interval starts with his first top-up date () and ends with the date plus 28 days, which is. The first is a free call interval of the customer with id 4, coloured in blue. Step 1: Calculating Top-Up Intervals with LAG/LEADįirst, let’s filter out top-ups with less than €20 and enrich the dataset to include columns with the days since the previous top-up and days to the next top-up.

  • We reduce the output from the previous step to just one row to get the start and end dates for each free call interval per customer.
  • We also compute ending dates of intervals as its last top-up date plus 28 days. One for the start and the other for the end of the free call interval.

    redshift window functions

  • In the third step, we filter out those rows with top-ups that fall inside the free call period, which gives us two rows.
  • Next, we will use these values to flag those rows with the start and end dates for the free call interval.
  • For each top-up, we compute the number of days between the current top up and the previous/next top up.
  • Initially, we include only top-ups of €20 or greater.
  • Outputs will be saved as SQL tables for clarity. We will present the query in four steps, where the output of each step will be input for the next step. What makes this use case a challenge is the fact that for any number of intersecting periods (applying the top-up multiple times), we want to produce just one interval.

    redshift window functions

    Let’s assume all the top-ups are of value €20 or more. The first three top-ups are chronologically close enough to be merged into an interval, whereas the fourth top-up is not and starts a new interval. To better understand the process of merging periods into intervals, see the picture below. The free call interval starts on and ends on. As a result, the free call period is not extended and ends on (28 days after the last top up above €20). Notice that the top-up on is below the free call threshold of €20. Hence, the free call interval extends for another 28 days and the interval end date is plus 28, which is. He tops-up again for €25 on, which is 25 days after the first top-up and therefore, still within the free call period. His free call interval begins on this date. On the customer tops-up for €25 (above our threshold of €20). The customer in our sample data has made three top-ups: Our final goal is to compute start and end dates of free call intervals for each customer. Customers can apply the promotion multiple times within the 28 calendar days and extend the free call period. In the scenario we go through today, the mobile operator runs promotions for pay as you go customers: if you top up for at least €20 you get free calls for the next 28 calendar days. It is available here and contains information about mobile credit top-ups of five customers: We will work with the same dataset as in the first article.

    REDSHIFT WINDOW FUNCTIONS HOW TO

    We will show how to achieve the result in SQL using a combination of multiple window functions and other features of SQL. Today, we introduce more advanced use cases, which complexity would usually require computation in the application instead of SQL engine. Thanks to our previous posts about the window functions, Introduction to Window functions on Redshift and Data exploration with Window functions on Redshift, you should now be familiar with the most common functions that can be used in the OVER clause and how to apply them to your data.













    Redshift window functions