SQL stands for Structured Query Language. Unfortunately, this does not mean that you will naturally come up with a perfectly structured piece of code once you start programming in this computer language.

As a Data Analyst, I keep applying myself three simple rules to better structure my SQL queries. They allow me not only to avoid mistakes in the way that I query from databases, but also to save time — for myself as well as for the colleagues I share my queries with. Let me present you these tricks and illustrate them with a real-life example.

1. Visualize your query output

Efficiently structuring your SQL code starts by having in mind the result you want to achieve. Ask yourself this simple question: How do I want my output table to look like?

In particular, pay attention to the following:

• Granularity level: at what level of aggregation do you want your output table to be? In other words, what should each row correspond to?
• Output fields: which fields will you be using based on the table generated by your query? In other words, what should each column correspond to?
• Input variables: where does your data come from? In other words, what is the nature of the variables that you will process in your query?

Let’s use an example to put this first tip into practice. Imagine that your boss just asked you to analyze the evolution of last month’s sales volume. To do so, you will need to retrieve the number of products (output field) per day (granularity level), knowing that you have access to a database table summarizing the purchases done by customers in the last months (input variables).

#data-science #tricks #programming #data-analysis #sql