lesson 12 Calculations & data quality · beginner

MIN, MAX & the grain of a result

Find extremes and define what each output row represents.

the idea

MIN and MAX return the smallest and largest known values. Without GROUP BY they describe the whole table; with GROUP BY they describe each group. The grain you choose decides what the extreme is an extreme of.

Dataset: Weather

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

   See daily weather rows. Run this. Each row is one station on one date with min and max temperature in Celsius.

   step-1.sql Run

   sql›

   SELECT station_id, observation_date, tmin_c, tmax_c
   FROM daily_weather
   LIMIT 10;

2. 2

   Find the first and last dates. Run this. MIN and MAX on observation_date show how far the dataset spans.the pattern

   example.sql Run

   sql›

   SELECT
     MIN(observation_date) AS first_day,
     MAX(observation_date) AS last_day
   FROM daily_weather;

common mistake

MIN(tmin_c) per station is that station’s coldest day. The coldest day overall is a single row; per-station extremes are 100 rows.