Gain insights into advanced SQL data analysis techniques. Explore producing descriptive statistics, handling missing data, and analyzing time series. Discover aggregating results and preparing data for analysis.

SQL databases have been around since the 1970s. Some of the smartest people in the world worked on making it easy to slice, dice, fetch and manipulate data quickly and efficiently. SQL databases have come such a long way that many developers and data scientists have lost track of what they can do with plain SQL.

In this course, you will learn advanced concepts and techniques for analyzing data using SQL. You will obtain hands-on experience in producing descriptive statistics, breaking a large query into multiple steps and samples, and cleaning and preparing data for analysis. You will learn how to produce aggregate results and subtotals, analyze a time series using cumulative and window frames, how to handle and fill missing data, and how to produce buckets and histograms.

After completing this course, you will be able to gain actionable insight from your data using nothing but SQL!

Practical Data Analysis with SQL

# Common clauses

These are the common clauses of an SQL query:

```sql
SELECT <expressions>
FROM <tables>
JOIN <to other table> ON <join condition>
WHERE <predicates>
GROUP BY <expressions>
HAVING <predicate>
ORDER BY <expressions>
LIMIT <number of rows>
```

Here is a quick overview of each clause:

`SELECT`: It lists the columns and expressions to appear in the query result output.

In PostgreSQL only the `SELECT` clause is mandatory.

`FROM`: It lists the tables to get data from.

`JOIN`: It describes the relations between tables in the `FROM` clause.

A query can access data from multiple related tables. For example, a `product` table can have a `category_id` column referencing a `category` table. Using the `JOIN` clause, we can describe how these tables are related.

`WHERE`: It filters data based on a condition.

`GROUP BY`: It lists columns and expressions to categorize the results. This clause is only necessary if the output of the query is grouped. Otherwise, you can omit this clause.

`HAVING`: It filters results based on the result of an aggregation. To use this clause you must also use the `GROUP BY` clause.

`ORDER BY`: It lists columns and expressions to sort the results by.

`LIMIT`: It restricts the number of rows in the results.

# Order of execution

The SQL clauses described above are written in that particular order, but surprisingly enough, the database is not executing them in this order.

Instead, the database executes SQL queries in the following order:


1. `FROM`: It finds the tables to access and apply joins in the `JOIN` clause.
2. `WHERE`: It applies predicates and filters the results.
3. `SELECT`: It finds columns and evaluates expressions.
4. `GROUP BY`: It groups the data and calculates aggregates.
5. `HAVING`: It applies conditions on the calculated aggregates from the previous step.
6. `ORDER BY`: It's ordering the result set.
7. `LIMIT`: It restricts the number of rows in the result.

Knowing the order in which the database executes SQL can be helpful for understanding and remembering what each clause does.

Get a brief introduction to basic clauses in SQL.

Introduction

Basic SQL for Data Analysis

Descriptive Statistics

Grouping and Subtotals

Running and Cumulative Aggregation

Interpolation

Binning

Conclusion

Anatomy of an SQL query

Common clauses