Basic SQL Concepts and Query Strategies

Aspiring data scientist or data analyst? Then one thing you most definitely will need to know for the job is how to extract relevant data from a database. Companies store their data in a structured database, and you as an scientist/analyst have to know how to query it to answer business relevant questions. In this post, I will walk through some basic examples, and some tips and tricks for the basic types of problems that any aspiring data scientist/analyst might encounter.

We will examine a case study where a diner has just hired you to run their analytics department (yes, in 2022 a diner has an analytics department!). Danny’s diner has data about sales, the menu, and members of its frequent customer club. The datasets have the following relationship:

How many unique customers are there?

So, we want to determine how many unique customers there are. This is similar to the problem of just listing out all of the customers that have made a purchase in our diner. Let’s write two queries to find both. The problem solving strategy for SQL queries should be:

Since we want a listing of unique customers, we want to return each individual customer record. We only want to return distinct records. This should give us a record of each customer who made a purchase. Here is the query and result:

A couple of observations:

Now, let’s return the total number of unique customers. We know the result should be 3 from the results, but here is the query we can use:

Notice that the aggregation function COUNT helped count the total records we had from the distinct customers. Also, we returned the result under a new alias using the AS clause. This can be really helpful to label our results.

How many days has each customer visited the diner?

The distinct order dates per customer allow us to examine each customer’s rows in the table, and total up how many days they visited. When we use a GROUP BY clause, it has the effect of first selecting all rows relevant to a single customer, then work with the data all related to that single customer.

Some questions may require us to utilize multiple tables for the information we need. For example, notice that we have a sales table with sales records, but then a menu table with actual data on each menu item a customer might purchase in a sale. So, these tables are linked together, and we can run a JOIN operation to join together the tables for queries.

What is the total amount spent by each customer?

This is another aggregation, and we really want to highlight each customer and understand how much money did they spend. This of course is related to their products they ordered, so we need to connect each sale record to a product record in the menu, and grab the price. Then it’s a matter of summing the prices for each product record for each customer:

A few observations:

Some queries might require us to calculate a “subquery” first, and then use it to gather information from that intermediate result. Some queries might also require us to partition the table into rankings or windows of time to aggregate our results over. Let’s take a look at some examples.

What item was the most popular for each customer?

So, here are some observations:

Notice that customer B actually has a tie where Ramen, curry, and sushi all were ordered twice as the top ranked order.

Happy queries!

Related posts:

CoinbaseDoes Coinbase Wallet Support Bitcoin Cash?Coinbase Wallet will never again uphold the local tokens related with Bitcoin Money (BCH)Ethereum Exemplary (And so on), Wave’s XRP Record (XRP) and…

Becoming a content writer with no experience can be challenging, but it is possible. Here are some steps you can take to get started: 1. Build your writing skills: Start by practicing your writing…

Most people tell that in order to create a skill, you really need to have passion, or talent, or some other facet that sounds really inspiring. And yet, we see examples of people being successful in…