The Applied SQL Data Analytics Workshop - Second Edition

By : Matt Goldwasser, Upom Malik, Benjamin Johnston

3.5 (2)

Buy this Book

The Applied SQL Data Analytics Workshop - Second Edition

3.5 (2)

By: Matt Goldwasser, Upom Malik, Benjamin Johnston

Buy this Book

Overview of this book

Every day, businesses operate around the clock and a huge amount of data is generated at a rapid pace. Hidden in this data are key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. Are you ready to enter the exciting world of data analytics and unlock these useful insights? Written by a team of expert data scientists who have used their data analytics skills to transform businesses of all shapes and sizes, The Applied SQL Data Analytics Workshop is a great way to get started with data analysis, showing you how to effectively sieve and process information from raw data, even without any prior experience. The book begins by showing you how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you'll learn how to write SQL queries to aggregate, calculate and combine SQL data from sources outside of your current dataset. You'll also discover how to work with different data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you'll finally be able to understand your business at a deeper level. Finally, the book lets you in on the secret to getting information faster and more effectively by using advanced techniques like profiling and automation. By the end of The Applied SQL Data Analytics Workshop, you'll have the skills you need to start identifying patterns and unlocking insights in your own data. You will be capable of looking and assessing data with the critical eye of a skilled data analyst.

Preface

About the Book

1. Introduction to SQL for Analytics

Introduction

The World of Data

Methods of Descriptive Statistics

Statistical Significance Testing

Relational Databases and SQL

Basic Data Types of SQL

Reading Tables: The SELECT Query

Creating Tables

Updating Tables

Deleting Data and Tables

SQL and Analytics

Summary

Free Chapter

2. SQL for Data Preparation

Introduction

Assembling Data

Transforming Data

Summary

3. Aggregate and Window Functions

Introduction

Aggregate Functions

Aggregate Functions with GROUP BY

The HAVING Clause

Using Aggregates to Clean Data and Examine Data Quality

Window Functions

Statistics with Window Functions

Summary

4. Importing and Exporting Data

Introduction

The COPY Command

Using R with Our Database

Using Python with Our Database

Best Practices for Importing and Exporting Data

Summary

5. Analytics Using Complex Data Types

Introduction

Date and Time Data Types for Analysis

Performing Geospatial Analysis in PostgreSQL

Using Array Data Types in PostgreSQL

Using JSON Data Types in PostgreSQL

Text Analytics Using PostgreSQL

Summary

6. Performant SQL

Introduction

Database Scanning Methods

Performant Joins

Functions and Triggers

Summary

7. The Scientific Method and Applied Problem Solving

Introduction

Case Study

Summary

Appendix

1. Introduction to SQL for Analytics

2. SQL for Data Preparation

3. Aggregate and Window Functions

4. Importing and Exporting Data

5. Analytics Using Complex Data Types

6. Performant SQL

7. The Scientific Method and Applied Problem Solving

Customer Reviews

3.5 (2)

5 star

4 star

50%

3 star

50%

2 star

1 star

3. Aggregate and Window Functions

Activity 3.01: Analyzing Sales Data Using Aggregate Functions

Solution

Open your favorite SQL client and connect to the sqlda database.
Calculate the number of unit sales the company has achieved using the COUNT function:
```
SELECT 
  COUNT(*)
FROM 
  sales;
```
You should get 37,711 sales.
Determine the total sales amount in dollars for each state; we can use the SUM aggregate function here:
```
SELECT 
  c.state, SUM(sales_amount) as total_sales_amount
FROM 
  sales s
INNER JOIN 
  customers c 
    ON c.customer_id=s.customer_id
GROUP BY
   1
ORDER BY
   1;
```
You will get the following output:
Figure 3.30: Total sales in dollars by US state
Determine the top five dealerships in terms of most units sold using the GROUP BY clause. Set the LIMIT to 5:
```
SELECT 
  s.dealership_id, 
  COUNT(*)
FROM 
  sales s
WHERE 
  ...
```

The Applied SQL Data Analytics Workshop - Second Edition

By : Matt Goldwasser, Upom Malik, Benjamin Johnston

The Applied SQL Data Analytics Workshop - Second Edition

By: Matt Goldwasser, Upom Malik, Benjamin Johnston

Overview of this book

Related Content you might be interested in

Current Title:

The Applied SQL Data Analytics Workshop - Second Edition

Data Wrangling with SQL

The SQL Workshop

3. Aggregate and Window Functions

Activity 3.01: Analyzing Sales Data Using Aggregate Functions