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

**(True or False)** The following expression will find the maximum value `MAX(value) OVER (PARTITION BY 1)`. 

(True or False) The following expression will find the maximum value <code>MAX(value) OVER (PARTITION BY 1)</code>.

**(True or False)** To use a `ROW` frame we must include `ORDER BY` in the aggregate expression.

(True or False) To use a <code>ROW</code> frame we must include <code>ORDER BY</code> in the aggregate expression.

**(True or False)** A ROW frame can only include rows prior to the current row. 

(True or False) A ROW frame can only include rows prior to the current row.

**(True or False)** The following frame will always include exactly 3 rows: `ROWS BETWEEN 3 PRECEDING AND CURRENT ROW`. 

(True or False) The following frame will always include exactly 3 rows: <code>ROWS BETWEEN 3 PRECEDING AND CURRENT ROW</code>.

**(True or False)** The `LAG(value)` and `LAG(value, 1)` expressions are equal. 

(True or False) The <code>LAG(value)</code> and <code>LAG(value, 1)</code> expressions are equal.

**(True or False)** The  `LAG(value, -1)` and `LEAD(value)` are equal. 

(True or False) The <code>LAG(value, -1)</code> and <code>LEAD(value)</code> are equal.

**(True or False)** The functions `LAG` and `LEAD` will always return a value. 

(True or False) The functions <code>LAG</code> and <code>LEAD</code> will always return a value.

What will be the ranking of the value 5 in series 1,2,2,5,6 using the `ROW_NUMBER` function?

What will be the ranking of the value 5 in series 1,2,2,5,6 using the <code>ROW_NUMBER</code> function?

What will be the ranking of the value 5 in the series 1,2,2,5,6 using the  `RANK` function?

What will be the ranking of the value 5 in the series 1,2,2,5,6 using the <code>RANK</code> function?

What will be the ranking of the value 5 in the series 1,2,2,5,6 using the `DENSE_RANK` function?

What will be the ranking of the value 5 in the series 1,2,2,5,6 using the <code>DENSE_RANK</code> function?

**(True or False)** If there are no duplicates in the ranking sorting key, the ranking of `ROW_NUMBER`, `RANK`, and `DENSE_RANK` will be the same. 

(True or False) If there are no duplicates in the ranking sorting key, the ranking of <code>ROW_NUMBER</code>, <code>RANK</code>, and <code>DENSE_RANK</code> will be the same.

Test your knowledge about running and cumulative aggregation in PostgreSQL.

Test your knowledge about running and cumulative aggregation in PostgreSQL.

Test yourself for running and cumulative aggregation in PostgreSQL.

Introduction

Basic SQL for Data Analysis

Descriptive Statistics

Grouping and Subtotals

Running and Cumulative Aggregation

Interpolation

Binning

Conclusion

Quiz: Running and Cumulative Aggregation