Y

SQL · Beginner · 4 h

Selecting, filtering and aggregating

SELECT, WHERE, ORDER BY, GROUP BY and HAVING on a month of real New York taxi trips.

Why employers care: Screening tests for analyst roles open with exactly these questions. If you are slow here, you do not reach the interesting part of the interview.

In the roadmaps for: Data Analyst, Data Scientist, Data Engineer, AI/ML Engineer, Forward Deployed Engineer, Software Engineer (Backend), DevOps / Platform Engineer, Blockchain Developer

Lessons, in order

  1. What is SQL? Databases, tables and your first querySet up a free SQL database on your laptop, load 1.37 million real New York taxi trips, and answer your first questions about them with DESCRIBE, SUMMARIZE and SELECT.12 min
  2. SQL SELECT, WHERE and ORDER BY on real taxi tripsPull exactly the rows you need from 1.37 million New York taxi trips with SELECT, WHERE and ORDER BY, and count what every filter hides before you trust the answer.10 min
  3. SQL NULL values: IS NULL, COALESCE and three-valued logicUnderstand what NULL really means in SQL, why it makes filters, averages and NOT IN quietly wrong, and how to handle it with IS NULL, COALESCE, NULLIF and IS DISTINCT FROM.10 min
  4. SQL data types and CAST: numbers, text, datesPick the right SQL data type for money, IDs, text and timestamps, convert between them with CAST and TRY_CAST, and fix the type problems that appear the moment you load a real CSV.12 min
  5. SQL LIKE, IN and BETWEEN with wildcards and rangesMatch text patterns with LIKE and regex, filter lists with IN, and write date ranges that never double-count a midnight, on real retail and taxi data.11 min
  6. SQL CASE WHEN: conditional logic and bucketsLabel rows, bucket numbers into bands and build one-row-per-group summaries with conditional counts, using CASE WHEN and the FILTER clause on real taxi data.10 min
  7. SQL GROUP BY, aggregate functions and HAVINGSummarise 1.37 million taxi trips by payment type, zone, hour and day with GROUP BY, filter the groups with HAVING, and avoid the averaging and grouping traps that produce wrong dashboards.10 min

Next shelf: Joins, subqueries and set operations