20 Must-Know SQL Queries for Data Analysts - Real-World Examples

Why SQL for Data Analysis?

In today's data-driven world, SQL (Structured Query Language) stands out as a fundamental skill for data analysts. It's the language that allows you to communicate directly with databases, the хранилище of valuable information. Whether you are dealing with large datasets, cleaning messy data, or extracting insights for informed decision-making, SQL is your go-to tool.

Even with the rise of AI and other advanced tools, SQL remains essential. It empowers you to efficiently retrieve, manipulate, and analyze data. Mastering SQL queries not only saves time but also significantly boosts your efficiency as a data analyst. In essence, SQL proficiency makes you the person teams rely on when data challenges arise.

This blog will guide you through 20 must-know SQL queries, equipping you with practical skills to tackle real-world data analysis scenarios. Let's dive in and unlock the power of SQL for data analysis.

The SELECT Statement

The SELECT statement is the most fundamental query in SQL. It's your go-to tool for retrieving data from one or more tables. Think of it as the starting point for almost every data analysis task you'll perform with SQL. It allows you to specify which columns you want to see and from which table you want to retrieve them.

Basic Syntax

The simplest form of a SELECT statement looks like this:

        
SELECT column1, column2
FROM table_name;
        
    

• SELECT column1, column2: This part specifies the columns you want to retrieve. Replace column1, column2 with the actual names of the columns you are interested in. To select all columns, you can use the asterisk * (e.g., SELECT *).
• FROM table_name: This indicates the table from which you want to fetch the data. Replace table_name with the name of your table.

Example

Let's say you have a table named Customers with columns like CustomerID, FirstName, LastName, and Email. To retrieve only the first and last names of all customers, you would use the following query:

        
SELECT FirstName, LastName
FROM Customers;
        
    

This query will return a result set containing only the FirstName and LastName columns from the Customers table. If you wanted to get all the information from the Customers table, you could use:

        
SELECT *
FROM Customers;
        
    

The SELECT statement is the foundation for more complex queries, and understanding it well is crucial for data analysis with SQL. In the following sections, we'll explore how to refine your data retrieval using clauses like WHERE, ORDER BY, and more.

Filtering with WHERE

The WHERE clause in SQL is your essential tool for data filtering. It allows you to specify conditions to retrieve only the rows that meet your criteria. Think of it as a sieve for your data, letting you extract precisely what you need for analysis.

With WHERE, you can compare columns to values, check for ranges, match patterns, and combine multiple conditions. This focused retrieval is crucial for efficient data analysis, especially when dealing with large datasets.

For instance, if you have a table of customer orders, you can use WHERE to find:

• Orders placed within a specific date range.
• Orders with a total value exceeding a certain amount.
• Customers from a particular city or region.
• Products belonging to a specific category.

By mastering the WHERE clause, you gain precise control over your data queries, enabling you to extract meaningful insights efficiently. It's a fundamental building block for more complex SQL operations and a must-know for any data analyst.

Sorting with ORDER BY

When analyzing data, seeing it in a sorted manner often provides better insights. SQL's ORDER BY clause is your go-to tool for arranging query results. It lets you sort data in ascending or descending order based on one or more columns.

Ascending Order

By default, ORDER BY sorts data in ascending order (from smallest to largest, or A to Z). You don't even need to specify ASC for this.

For example, to see a list of customers sorted by their names from A to Z, you would use:

        
SELECT customer_name
FROM customers
ORDER BY customer_name;
        
    

Descending Order

To sort data in reverse order (from largest to smallest, or Z to A), you use the DESC keyword.

If you wanted to see the customers with the highest order values first, you might use:

        
SELECT customer_name, order_value
FROM orders
ORDER BY order_value DESC;
        
    

Sorting by Multiple Columns

You can sort by more than one column. The sorting order will be determined by the order of columns listed in the ORDER BY clause. For example, to sort customers first by their city and then by name within each city:

        
SELECT customer_name, city
FROM customers
ORDER BY city, customer_name;
        
    

This sorts primarily by city (alphabetically) and then for customers in the same city, it sorts by customer_name.

Real-World Use

• Ranking Sales: Identify top-performing products or salespersons by sorting sales figures in descending order.
• Analyzing Trends Over Time: Order data by date to see how metrics change chronologically.
• Customer Segmentation: Sort customers by purchase frequency or value to identify different customer segments.

Mastering ORDER BY is crucial for making sense of your data and presenting it effectively in your analysis.

Joining Tables

In the world of databases, information is often spread across multiple tables. To get a complete picture, especially for data analysis, you need to combine data from these tables. This is where JOIN operations come into play. They are essential for linking related data based on common columns.

Imagine you have two tables: one with customer information and another with order details. To analyze which customers placed which orders, you'd need to join these tables using a common column like customer ID. Joining tables allows you to retrieve combined datasets, enabling more insightful analysis and reporting.

Types of Joins

SQL offers several types of joins, each serving different purposes. Understanding these types is crucial for effective data retrieval:

• INNER JOIN: Returns rows only when there is a match in both tables based on the join condition. It excludes rows where there's no match.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If there's no match in the right table, it returns NULL values for columns from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN, but it returns all rows from the right table and matching rows from the left table. NULL values are used for columns from the left table when no match is found.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or the right table. It combines the results of both LEFT JOIN and RIGHT JOIN. If there are no matches in either table, NULL values are used for the missing side.

Choosing the right type of join depends on the specific analysis you're performing and the data you need to retrieve. For instance, if you need to see all customers and their order information (if any), a LEFT JOIN might be appropriate. If you are interested only in orders that have corresponding customer information, then INNER JOIN would be the way to go.

Aggregate Functions

Aggregate functions in SQL are essential tools for data analysis. They allow you to perform calculations on sets of rows to return a single summary value. Understanding these functions is crucial for gaining insights from your data.

These functions are commonly used to:

• Summarize large datasets into meaningful metrics.
• Calculate key performance indicators (KPIs).
• Identify trends and patterns in data.
• Generate reports and dashboards.

Here are some of the most frequently used aggregate functions:

• COUNT: Counts the number of rows.
• SUM: Calculates the sum of values in a column.
• AVG: Computes the average value of a column.
• MIN: Finds the minimum value in a column.
• MAX: Determines the maximum value in a column.

By mastering aggregate functions, you can efficiently analyze data and extract valuable information for data-driven decision-making.

Understanding Subqueries

Subqueries, also known as inner or nested queries, are queries embedded within another SQL query. Think of them as queries within queries. They are powerful tools for performing complex data retrieval operations in SQL.

Why Use Subqueries?

Subqueries are used to solve problems that cannot be solved with a single query. They break down complex queries into simpler, manageable parts. The inner query executes first, and its result is used by the outer query. This allows you to:

• Filter rows based on conditions derived from another query.
• Calculate values that are then used in the main query.
• Check for existence of data based on another query's results.

Basic Subquery Structure

A subquery is typically placed within the WHERE clause, FROM clause, or SELECT clause of an outer query. Let's look at a common example in the WHERE clause:

        
SELECT customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_total > 100
);
        
    

In this example:

• The inner query SELECT customer_id FROM orders WHERE order_total > 100 finds all customer_ids from the orders table where the order_total is greater than 100.
• The outer query SELECT customer_name FROM customers WHERE customer_id IN (...) then selects customer_name from the customers table where the customer_id is in the list of customer_ids returned by the inner query.

Essentially, this query retrieves the names of customers who have placed orders with a total greater than 100.

Understanding subqueries is crucial for writing more advanced and efficient SQL queries for data analysis. They allow you to perform complex filtering and data manipulation, unlocking deeper insights from your datasets.

Window Functions Basics

Window functions are a powerful feature in SQL that allow you to perform calculations across a set of rows that are related to the current row. Unlike aggregate functions that group rows into a single output row, window functions operate on each row individually while still having access to a "window" of related rows. This window is defined by clauses such as PARTITION BY and ORDER BY.

Think of window functions as a way to add context to your data within a query. For each row, you can calculate things like running totals, ranks, or moving averages, based on the data in the window. This eliminates the need for complex subqueries or self-joins in many cases, making your SQL code cleaner and more efficient.

For example, you can use window functions to:

• Calculate a rank for each product based on its sales within each category.
• Find the moving average of sales over the last three months for each store.
• Identify the percentage contribution of each order to a customer's total spending.

In essence, window functions provide a flexible and efficient way to perform complex data analysis directly within your SQL queries, opening up new possibilities for insightful reporting and data exploration. They are a must-know tool for any data analyst working with SQL.

Data Manipulation

Data manipulation is a core skill for any data analyst using SQL. It involves modifying data within your database to keep it accurate, relevant, and useful for analysis. Think of it as the way you refine and shape raw data into insights.

In SQL, data manipulation is primarily achieved through these key operations, often referred to as CRUD operations:

• CREATE: Adding new data into your database. This is done using the INSERT statement.
• READ: Retrieving data for analysis. While technically data manipulation focuses on changes, SELECT statements are crucial for viewing data before and after manipulations.
• UPDATE: Modifying existing data in your database. The UPDATE statement is used for this purpose.
• DELETE: Removing data that is no longer needed or is incorrect. This is accomplished using the DELETE statement.

Why is data manipulation essential for data analysts? Because real-world data is rarely perfect. You might need to correct errors, standardize formats, remove duplicates, or enrich your datasets to make them analysis-ready. Mastering data manipulation in SQL empowers you to clean, prepare, and transform data effectively, leading to more reliable and insightful analysis outcomes.

Real-World SQL Examples

Understanding SQL queries is essential for data analysts. But seeing how these queries apply in real-world situations makes learning truly effective. Let's explore practical examples that demonstrate the power of SQL in various data analysis scenarios.

Real-world examples bridge the gap between theory and practice. By examining specific use cases, you'll gain a clearer understanding of how to apply SQL to solve actual data challenges. This section will set the stage for exploring such examples throughout this blog.