Window functions are the windows to the world of SQL… Well sort of!
Window Functions is one of the not so simple concepts to master especially when you are just starting out with learning SQL.
Window function is structured as:
SELECT RANK() OVER (PARTITION BY USER_ID ORDER BY ACTIVITY_DATE ASC) AS user_rank
We will focus on the bold parts first:
Function: RANK() is a function.
There are many types of functions we can use for a window function e.g. - ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), AVG(A), SUM(B), COUNT(C). We will explore this in detail in the subsequent posts.
OVER() : w’ll get over it for now
PARTITION BY: does the same thing as GROUP BY
ORDER BY: sorts the rows after you've grouped them using PARTITION BY
PARTITION BY and ORDER BY apply only to the window function since they're inside the OVER() statement.
Now, let’s build a Window Function:
1. Function + OVER(): Once you've created the window by grouping and sorting your data with PARTITION BY and ORDER BY…this syntax gets executed on the data last.
2. PARTITION BY: It is used to group the data on a specific column.
3. ORDER BY: It is used to sort the results either ASC or DESC after the data has been grouped using PARTITION BY on a particular column let’s say order by date.
In the example query above, it creates a window function that ranks activity of a user. Once the rank is calculated, we can filter on rank=1 so that we can get the first_activity for each user.
detailed post on different kinds of functions coming soon!…