Section 1 of 3 · 34 lessons

SQL

Botie's maintenance team has years of inspections, equipment details and sensor readings. How can we use those records to answer a specific question without counting the wrong visits or mixing up assets? Begin with a table and a small query, then work toward analytical queries and dependable databases. The lessons also follow the data into machine learning, where the time a record became available matters as much as its value.

0 of 34 finished

Reading the Records

Botie has a table of inspections, but the team needs answers about particular pumps and visits. Start by defining what each row records, then choose the rows and columns that answer the question.

  1. 01What Does One Row Actually Describe?Botie has an asset list and a stack of inspection notes. Decide what belongs on each row before asking the database a question.
    Build with Botie →Read the lesson
  2. 02Which Columns Answer the Question?A report needs the kinds of equipment at each site. Choose the relevant fields and decide exactly which repeated rows should disappear.
    Build with Botie →Read the lesson
  3. 03Which Inspection Records Need Our Attention?Translate a maintenance question into conditions on individual rows, and make the grouping of those conditions explicit.
    Build with Botie →Read the lesson
  4. 04Which Two Visits Took the Longest?Build a reproducible top list, including what happens when two records have the same value.
    Build with Botie →Read the lesson

Missing Values and Decisions

An empty measurement is different from a measurement of zero. Follow what happens when an unknown value enters a comparison, then write decisions that handle those gaps deliberately.

  1. 05Was the Visit Instant, or Is Its Duration Missing?Keep an unknown measurement distinct from zero, and follow SQL’s true, false and unknown conditions.
    Build with Botie →Read the lesson
  2. 06What Should Botie Label Each Reading?Express a row-level decision with CASE while keeping unknown measurements visible and using fallbacks deliberately.
    Build with Botie →Read the lesson

Summarising Maintenance Work

A list of visits does not tell us which site is falling behind. Collect measurements into summaries, keeping the population and missing values visible as the question changes.

  1. 07How Much Inspection Time Did We Actually Record?Turn visit rows into a summary without confusing the number of visits with the number of known durations.
    Build with Botie →Read the lesson
  2. 08How Much Work Belongs to Each Asset?Form a separate group for each asset, then calculate summaries without mixing visits from different equipment.
    Build with Botie →Read the lesson
  3. 09Which Assets Keep Returning with the Same Finding?Filter individual inspections before grouping, then filter the groups according to the evidence they contain.
    Build with Botie →Read the lesson

Connecting Tables Without Changing the Question

Asset details and inspection visits live in separate tables. Match the records that belong together, then investigate why a reasonable-looking join can count the same work several times.

  1. 10Which Site Does Each Inspection Belong To?Connect inspection facts to the asset register by a declared key, then inspect the row pairs the join actually produces.
    Build with Botie →Read the lesson
  2. 11Which Assets Have No Matching Inspection?Keep the whole asset register while attaching whatever qualifying inspection evidence exists.
    Build with Botie →Read the lesson
  3. 12Why Did Joining Readings Double the Inspection Total?Trace a many-to-many multiplication, then summarise each event stream at the intended grain before combining it.
    Build with Botie →Read the lesson

Queries Built from Queries

Some questions depend on the answer to another question. Give each intermediate result a clear job, from checking whether a visit exists to following the equipment hierarchy.

  1. 13Which Inspections Took Longer Than the Usual Visit?Use one query’s result inside another without losing track of whether that result is one value, several rows, or a value belonging to the current asset.
    Build with Botie →Read the lesson
  2. 14Which Pumps Still Have No Completed Visit?Ask whether a related row exists, keep the outer row’s identity, and avoid treating an unknown key as evidence that every candidate is present.
    Build with Botie →Read the lesson
  3. 15Which Pumps Appear on Either List but Are Still Unfinished?Combine, compare and subtract query results while keeping duplicate policy, column meaning and final ordering explicit.
    Build with Botie →Read the lesson
  4. 16Can We Read the Query in the Order We Explain the Job?Use common table expressions to name meaningful query stages while keeping grain, scope and execution assumptions explicit.
    Build with Botie →Read the lesson
  5. 17How Far Does the Failed Supply Line Reach?Follow a hierarchy or dependency graph using an anchor, a repeated join and explicit safeguards against cycles and excessive traversal.
    Build with Botie →Read the lesson

Analysing Change Across Rows

An average hides the sequence of visits. Keep individual records while comparing neighbouring measurements, ranking results and following totals through time.

  1. 18Keep Every Reading While Looking at Its NeighboursPartition and order individual records for row numbers, ranks and previous-reading comparisons without collapsing them into groups.
    Build with Botie →Read the lesson
  2. 19Which Readings Belong in This Rolling Total?Choose ROWS, peer groups and time ranges deliberately so rolling calculations include the observations the question actually describes.
    Build with Botie →Read the lesson

Preparing Records for Analysis

Dates, notes and nested records bring their own ambiguities. Work out what each value represents before turning it into something a query can compare.

  1. 20Where Did the Quiet Day Go?Define UTC timestamps, half-open reporting intervals and a calendar spine so missing days do not disappear or midnight events get counted twice.
    Build with Botie →Read the lesson
  2. 21Why Did a Pump Search Return the Wrong Equipment?Clean and compare text under an explicit identifier policy, with literal pattern characters, NULL handling and honest Unicode limits.
    Build with Botie →Read the lesson
  3. 22Is That Temperature a Number or Just Text?Extract structured payloads with explicit JSON validity and type checks while preserving the distinction between missing, null, text and numeric data.
    Build with Botie →Read the lesson

Features from Historical Data

A prediction needs the records available when the decision was made. Rebuild that boundary for each training example, including measurements that arrived after the event they describe.

  1. 23What Could the Model Have Known at That Moment?Build one feature row per historical decision using event time, availability time and explicit label boundaries, without leaking later evidence into the inputs.
    Build with Botie →Read the lesson

Designing Reliable Tables

If an asset appears under two conflicting site names, which record should a query trust? Design the stored relationships and constraints that keep these contradictions from becoming ordinary data.

  1. 24What Does One Equipment Record Promise?A column called cost can hold cents, pounds or an unexplained string. Give the maintenance team a table whose identities and units mean the same thing on every row.
    Build with Botie →Read the lesson
  2. 25Can a Job Refer to a Pump We Have Never Recorded?A row can have the right types and still contain an impossible reference. Put identity, domain and relationship rules at the database boundary.
    Build with Botie →Read the lesson
  3. 26Why Must a Pump Move Be Edited on Every Job?Repeated asset details make updates disagree. Find which identity determines each fact and give that fact one deliberate home.
    Build with Botie →Read the lesson

Changing Shared Data

Recording a visit may also change a work order. Keep those changes consistent, then examine what happens when two technicians update the same records at once.

  1. 27Change the Requested Jobs Without Rewriting the RestNew inspections arrive, existing ones change, and cancelled drafts leave the queue. Give each write a precise target and preserve newer information.
    Build with Botie →Read the lesson
  2. 28Move Spare Parts Without Losing Half the TransferA transfer changes two stock balances and its audit record. Make those changes one unit of work, and learn what a rollback can actually undo.
    Build with Botie →Read the lesson
  3. 29What If Someone Changes the Job While Botie Is Reading It?Two valid edits can overwrite each other. Make stale assumptions visible, and distinguish version checks from the database’s isolation guarantees.
    Build with Botie →Read the lesson

Making Queries Practical

The query works on a handful of rows. What happens when the table contains years of inspections? Read its plan, choose useful indexes, and pass user inputs without changing the query's instructions.

  1. 30Find One Pump’s Readings Without Checking Every PumpThe reading table keeps growing. Arrange useful lookup keys so the database can find a small candidate region, then account for the cost of maintaining that structure.
    Build with Botie →Read the lesson
  2. 31Why Does a Short Query Repeat So Much Work?A compact query can scan the same table repeatedly. Read the access plan, preserve the answer, and compare work on data that resembles the real job.
    Build with Botie →Read the lesson
  3. 32Let a Search Value Stay a ValueA search box should select an asset, not rewrite the query or change whose records are visible. Keep values, query structure and permissions at separate boundaries.
    Build with Botie →Read the lesson

Keeping Learning Data Trustworthy

A scheduled dataset refresh has to cope with corrections and late records. Build checks that explain failures, then update the dataset without quietly duplicating work or losing older changes.

  1. 33Which Sensor Records Should Reach the Training Table?A query can run successfully over duplicate identities, missing readings and impossible timestamps. Turn the dataset’s assumptions into visible checks before building model features.
    Build with Botie →Read the lesson
  2. 34Pick Up New Changes Without Losing Late ReadingsRepeated full imports become expensive, but a naive timestamp filter misses corrections and deletions. Track source change position, merge versions and commit the checkpoint with the result.
    Build with Botie →Read the lesson