AI 原生开放教材

外观

AI Engineering Lab（24 周自学课程）系统课程·英文原文 ↗仓库 ↗

Week 02: Quiz (10 questions, 8/10 to pass) ​

Answer all ten, then check against the answer key. (see Concepts §X) points at a README subsection; (see notebook cell Y) points at a cell in 01-pandas-cleaning.ipynb or 02-sql-with-duckdb.ipynb.

  1. Multiple choice. Under seed 42, the raw shipments table has 100,200 rows and the cleaning notebook's final silver row count is 100,000. What accounts for the 200-row difference? (see Concepts §6, Step 4) a. 200 NaN weights were dropped b. 200 duplicate rows were removed with drop_duplicates() c. 200 non-positive weights were dropped d. 200 bad transit rows were dropped

  2. Short answer. Why does the notebook impute NaN weight_kg with the median per commodity rather than the global median? (see Concepts §2, Step 4)

  3. Multiple choice. Which single SQL expression computes the company-wide on-time rate? (see notebook "on-time rate" cell) a. COUNT(is_on_time) b. SUM(is_on_time) / COUNT(*) c. AVG(CAST(is_on_time AS INT)) d. MAX(is_on_time)

  4. Short answer. Explain the difference between GROUP BY and a window function, in one sentence each. (see Concepts §4)

  5. Multiple choice. In the window-function query, what does QUALIFY rnk <= 2 do? (see notebook "window functions" cell) a. Limits the whole result to 2 rows b. Filters to rows whose window-computed rank is 2 or less, the top 2 carriers per month c. Sorts carriers alphabetically d. Deduplicates the monthly CTE

  6. Short answer. Name three of the 12 validation checks in Step 5, and state the property each guards. (see notebook Step 5)

  7. Multiple choice. What is referential integrity in this dataset, and which check enforces it? (see Concepts §5) a. Every weight_kg is positive, enforced by (ships["weight_kg"] > 0).all() b. Every shipments.carrier_id/lane_id matches an existing master key, enforced by isin(...) c. Every shipment_id is unique, enforced by duplicated().sum() == 0 d. Every delay_hours is within range, enforced by between(-48, 240)

  8. Multiple choice. DuckDB runs SQL directly over the registered pandas DataFrames. What does con.register("shipments", ships) accomplish? (see notebook "register" cell) a. Writes the DataFrame to a permanent .db file b. Exposes the in-memory DataFrame as a queryable table named shipments c. Converts the DataFrame to Parquet d. Starts a DuckDB server process

  9. Short answer. A rate is a mean of a boolean. Given 100,000 silver rows with 79,566 on-time, what is the on-time rate (to 3 decimals), and what does the complement (≈20.4%) mean for Week 3's classification problem? (see Concepts §6, Step 3)

  10. Short answer. Why does the cleaning workflow need to be a re-runnable recipe with a gate, rather than a one-off script? (see Concepts §5 to 6)

Answer key ​

  1. b. The generator plants ~0.2% duplicate rows (200 of 100,200); drop_duplicates() removes them, leaving 100,000. The defensive drops (non-positive weight, bad transit) remove 0 rows because those defects are not present.

  2. Weight varies by cargo type, so a per-commodity median is a more honest estimate than one global number; the notebook uses groupby("commodity")["weight_kg"].transform("median"). The habit (impute within groups) matters even when the medians happen to be close.

  3. c. Casting the boolean to INT (0/1) and averaging yields the fraction on time: AVG(CAST(is_on_time AS INT)) → ≈0.7957. Options a, b, d do not compute a rate.

  4. GROUP BY collapses rows into one row per group and reduces them (count/avg/sum). A window function computes a value across a group while keeping every original row, e.g. ranking each carrier within its month.

  5. b. RANK() OVER (PARTITION BY month ORDER BY on_time_rate DESC) assigns a per-month rank; QUALIFY rnk <= 2 keeps only ranks 1 to 2, the top two carriers within each month.

  6. Any three of: no duplicate rows; no NaN weight_kg; all weights positive; no NaN delay_hours; no NaN is_on_time; value_usd non-negative; carrier_id/lane_id referential integrity; is_on_time boolean; delay_hours in [-48, 240]; arrival after departure; lanes have no NaN distance. Each guards one invariant the "silver" contract requires.

  7. b. Referential integrity means every foreign-key value (carrier_id, lane_id) points to a real master record; the isin(carriers["carrier_id"]) / isin(lanes["lane_id"]) checks enforce it.

  8. b. con.register binds the in-memory DataFrame to a table name so SQL FROM shipments works with no file or server; the data stays in memory.

  9. 0.796 (79.6%). The complement is the late share ≈ 20.4%, which means Week 3's "on-time vs late" target is imbalanced, a classifier that always says "on time" would be ~79.6% accurate while missing every late shipment, so accuracy is banned as the headline metric.

  10. Data keeps arriving, so a one-off fix decays into a dirty table on the next batch. A recipe + gate (the 12-check suite) lets anyone re-run raw → silver and prove the result is clean; a check that can't be re-run is a hope, not an artifact.