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 summarize the smallest and largest values. The grain describes what one output row means. Adding GROUP BY changes that grain from one overall row to one row per group.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  MIN(watch_seconds) AS shortest_watch,
  MAX(watch_seconds) AS longest_watch
FROM watch_events;

Watch for: A maximum value alone does not identify every other column from that row.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Scan the watch lengths. Each event has watch_seconds. One overall MIN/MAX summarizes the whole table; GROUP BY later changes the grain.

   step-1.sql Run

   sql›

   SELECT id, video_id, watch_seconds
   FROM watch_events
   ORDER BY id
   LIMIT 10;

2. 2

   Summarize at the overall grain. Without GROUP BY, MIN and MAX return one row for the entire result. That grain is “the whole table.”

   step-2.sql Run

   sql›

   SELECT
     MIN(watch_seconds) AS shortest_watch,
     MAX(watch_seconds) AS longest_watch
   FROM watch_events;