Elevating Your Data Skills with Window Functions, Regex, and CTEs
During my tenure as a lead data analyst at Chime, three crucial SQL techniques— Window Functions, Regex, and CTEs — significantly advanced my capabilities, propelling me from intermediate proficiency to the expertise required for a lead analyst role. This article details these so you can up-level your skills and unlock new dimensions in data exploration.
A window function (or analytic function) makes a calculation across multiple rows that are related to the current row, and lets you calculate things like:
- Rankings
- Running totals
- 7-day moving averages (i.e. average values from 7 rows before the current row)
Creating rankings with window functions is an extremely powerful tecnique in analytics and data science. Consider for this transactions dataset, where we have transactions made by customers.
