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.
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.
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.
0 kommentar(er)
