Learn how to use rank, dense rank, row number, cumulative distribution, percentiles rank, quartiles, percentiles, and more — What are numbering functions? Numbering functions assign a number (or decimals) to each record in a table. They are mostly used for ranking or assigning a sequential number to the data for further processing (deduplication, filtering, grouping). They usually require to be ordered by a specific dimension (date, revenue, salary, ID, etc…).

Compare the contribution of a row to the overall sum of all rows — Why use a percentage of the total? Let’s say you have an e-commerce website that sells coffee, and you have three different categories of products: Arabica, Robusta, and Liberica. On a report, you see that for June 2022, you sold for 345k€ of Arabica, 112k€ of Robusta, and 13k€ of Liberica. In absolute terms, you notice that…

Easily segment your data into training, validation, and test sets — Why split our dataset? Splitting data means that we will divide it into subsets. For data science models, datasets are usually partitioned into two or three subsets: training, validation, and test. Each subset of data has a purpose, from creating a model to ensuring its performance. A quick definition of the different segments: Training…

Use Levenshtein distance to discover similar or duplicated values, clean your data, and more — Using the Levenshtein distance method This method can be used among others (Soundex, LIKE statement, Regexp) to perform string similarity or string matching in order to identify two elements (text, strings, inputs) that are similar but not identical. This method can be used for a variety of applications, including identifying duplicates, handling misspelled user input…

Optimize your costs and speed up your queries — What are partitions and clusters? Partitioning and clustering are two very effective techniques to minimize query costs and increase performance (using fewer resources while improving speed). The idea behind these techniques is to limit the amount of data that needs to be read when running a query. Essentially, we will not need to read through…

Smooth out variations, spot trends, and visualize them in Data Studio — The problem When looking at time-series data, decisions can be influenced by random, short-term fluctuations (price of a cryptocurrency, number of Covid-19 cases reported). This is why using a Moving Average (also called Running Average or Rolling Average) helps mostly to smooth out short-term fluctuations and highlight longer-term trends.