lesson 74 Warehouse & reporting projects · advanced

EDA: explore before telling the story

Explore distributions and relationships before reporting.

the idea

Exploratory data analysis (EDA) checks the shape of the data before reporting on it: the date range, the number of rows, totals of the main measures and how many entities are active. These numbers become the reference points every later report must agree with.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count sales lines per month. Each row is one month’s line count on fact_sales—you see seasonality before you build any formal report.the pattern

   example.sql Run

   sql›

   SELECT substr(order_date, 1, 7) AS month, COUNT(*) AS lines
   FROM fact_sales
   GROUP BY month;

2. 2

   See the largest sale lines. The top ten rows by sales flag extreme quantities and amounts worth checking before you trust aggregates.

   step-2.sql Run

   sql›

   SELECT sale_key, quantity, sales
   FROM fact_sales
   ORDER BY sales DESC
   LIMIT 10;

common mistake

COUNT(*) on fact_sales counts lines, not orders or customers; name each metric by what it counts.