7. The Scientific Method and Applied Problem Solving

Overview

By the end of this chapter, you will be able to solve real-world problems outside of those described within this book by using the skills that you have acquired. Using the scientific method and critical thinking, you will be able to analyze your data and convert it into actionable tasks and information. To accomplish these goals, we will examine an extensive and detailed real-world case study of sales data. This case study will not only demonstrate the processes used in SQL analysis to find solutions for actual problems but will also provide you with confidence and experience in solving such problems.

Introduction

Throughout The Applied SQL Data Analytics Workshop, Second Edition, you have learned a range of new skills (including basic descriptive statistics, SQL commands, and importing and exporting data in PostgreSQL) as well as more advanced methods to optimize and automate SQL (such as functions and triggers). In this final chapter of this workshop, we will combine these new skills with the scientific method and critical thinking to solve a real-world problem and determine the cause of an unexpected drop in sales.

This chapter provides a case study and will help you build your confidence in applying your new SQL skillset to your own problem domains. To solve the problem presented in this use case, you will use the complete range of your newly developed skills, from using basic SQL searches to filter out the available information to aggregating and joining multiple sets of information and using windowing methods to group the data in a logical manner. By completing case studies...

Case Study

Throughout this chapter, we will cover the following case study. The new ZoomZoom Bat Scooter is now available for sale exclusively through its website. Sales are looking good, but suddenly, preorders start plunging by 20% after a couple of weeks. What's going on? As the best data analyst at ZoomZoom, you've been assigned to figure this out.

The Scientific Method

In this case study, we will be following the scientific method to help solve our problem, which, at its heart, is about testing guesses (or hypotheses) using objectively collected data. We can decompose the scientific method into the following key steps:

1. Define the question to answer what caused the drop in sales of the Bat Scooter after approximately 2 weeks.
2. Complete background research to gather sufficient information to propose an initial hypothesis for the event or phenomenon.
3. Construct a hypothesis to explain the event or answer the question.
4. Define and execute an objective...

Summary

You have just completed your first real-world data analysis problem using SQL. In this chapter, you built the skills necessary to develop hypotheses for problems and systematically gather the data required to support or reject them. You started this case study with a reasonably difficult problem of explaining an observed discrepancy in sales data and discovered two possible sources (launch timing and marketing campaign) for the difference while rejecting one alternative explanation (sales price).

While being a required skill for any data analyst, being able to understand and apply the scientific method in our exploration of problems will allow you to be more effective and find interesting threads of investigation. In this chapter, you used the SQL skills you've developed throughout this book; from simple SELECT statements to aggregating complex data types, as well as windowing methods. After completing this chapter, you will be able to continue and repeat this type...