Top SQL Tips and Timesavers for 2023
Highlighting our top 5 SQL tips for 2023 based on popularity
1. Our most popular SQL tip this year
How to calculate running totals in SQL server
why you would use them, and provides a breakdown of the syntax
The article gives a step by step example of how to create a running total of daily sales, breaking down each aspect of the code
Running totals are an invaluable tool for data analysis, financial accounting, and forecasting. Helping to explain trends, performance and other changes over time
2. our second most popular SQL Tip for 2023
How to use the LEAD and LAG functions in SQL for analysing and optimizing data.
These functions are window functions that allow users to compare and analyse data points between rows in a specified window.
By using these functions, users can identify trends, correlations, and outliers in their data sets more easily than ever before
The post explains that the LEAD function is used to access subsequent rows along with data from the current row, while the LAG function is used to access previous rows along with data from the current row.
These functions are especially useful when analysing time-series data that is stored in SQL databases. Its one of those things that is easy to do in Excel, but needs a bit of knowledge in SQL
3. In Third place
How to use the ROLLUP extension of the GROUP BY clause in SQL Server to generate subtotals and grand totals for multiple dimensions within a dataset.
The post provides a simple example to illustrate the steps for using the ROLLUP function in SQL.
Assuming you have a table of daily sales records, you can use the ROLLUP function to insert sub-total rows for each year, quarter, and month name
The ROLLUP function is a powerful yet simple way to add subtotals to your queries. And the flexibility to use partial rollups allows you more control over when to use them.
The feature can be used for any hierarchical data set to instantly add useful subtotals without the need to write separate subqueries or use window functions
4. The Fourth most popular
How to avoid the common error of dividing by zero in SQL.
We provide a simple logic to make sure the division by zero errors do not get in the way of analysis.
The post explains that dividing by zero makes no sense logically, as the result cannot be determined
The post provides a range of solutions to avoid these issues with simple logic.
The solutions include checking the denominator first, using a safe division type of option which has built-in divide by zero handling, and using the NULLIF command around the denominator value
Although this is one of the most popular SQL posts this year, it also cover Excel, Power Query, Power BI (DAX) and Google BigQuery
5. And concluding our top 5 most popular SQL tips for 2023
A step-by-step guide on how to split a column in SQL Server.
The guide shows how to split one column into multiple columns in SQL Server. This can be a very common problem, but with a few commands, it is easily achieved.
The post provides an example of splitting a column containing full names into two columns, one with the first name and one with the last name. The post explains how to find the position of the delimiter, how to find the length of the full name. Then how to find the characters that make up the first name, and how to find the last name
Subscribe to our channel to see more tips and timesavers
Select Distinct YouTube Channel
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.
Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
