Mock Exam 09

Instructions

This mock exam focuses on end-to-end ML application design.

Task 1: SQL and analysis workflow, 45 points

1. Load all Olympics CSV files into DataFrames and SQLite.
2. Write SQL queries for:
3. The number of athletes with missing or zero height per country.
4. The top ten disciplines by number of athletes.
5. Countries with more coaches than technical officials.
6. The total number of scheduled medal sessions per venue.
7. The medal count per country and discipline.
8. Explain which results you would move into Pandas for plotting or modeling.

Task 2: ML application workflow, 30 points

1. Draw a full machine learning application workflow.
2. Place data cleansing, feature engineering, scaling, dimensionality reduction, training, evaluation, deployment, and monitoring.
3. Explain where PCA and LDA fit.
4. Explain three common data leakage mistakes.
5. Explain why monitoring matters after deployment.

Task 3: Instruction finetuning, 15 points

1. Explain the three-stage plan from pretrained model to instruction-following model.
2. Explain instruction data format.
3. Explain padding and ignored labels in the loss.
4. Explain LLM-as-judge and one weakness of it.

Answer Key

Task 1 answer

from pathlib import Path
import sqlite3

import pandas as pd

dfs = {path.stem: pd.read_csv(path) for path in Path("Data").glob("*.csv")}
conn = sqlite3.connect(":memory:")
for name, df in dfs.items():
    df.to_sql(name, conn, index=False, if_exists="replace")

Missing or zero height per country:

SELECT country,
       SUM(CASE WHEN height IS NULL OR height = 0 THEN 1 ELSE 0 END) AS missing_or_zero_height,
       COUNT(*) AS athlete_count
FROM athletes
GROUP BY country
ORDER BY missing_or_zero_height DESC;

Top disciplines by athletes:

SELECT disciplines, COUNT(*) AS athlete_count
FROM athletes
GROUP BY disciplines
ORDER BY athlete_count DESC
LIMIT 10;

Countries with more coaches than technical officials:

WITH coach_counts AS (
    SELECT country_code, country, COUNT(*) AS coach_count
    FROM coaches
    GROUP BY country_code, country
),
official_counts AS (
    SELECT organisation_code AS country_code, COUNT(*) AS official_count
    FROM technical_officials
    GROUP BY organisation_code
)
SELECT c.country, c.coach_count, COALESCE(o.official_count, 0) AS official_count
FROM coach_counts AS c
LEFT JOIN official_counts AS o
  ON c.country_code = o.country_code
WHERE c.coach_count > COALESCE(o.official_count, 0)
ORDER BY c.coach_count DESC;

Medal sessions per venue:

SELECT venue, COUNT(*) AS medal_session_count
FROM schedules
WHERE event_medal = 1
GROUP BY venue
ORDER BY medal_session_count DESC;

Medal count per country and discipline:

SELECT country, discipline, COUNT(*) AS medal_count
FROM medals
GROUP BY country, discipline
ORDER BY country, medal_count DESC;

Move aggregated, manageable results into Pandas for plotting, such as medal sessions per venue or medal counts per country and discipline. Keep filtering and aggregation in SQL when the raw tables are large or relational.

Task 2 answer

problem definition
-> data collection
-> data understanding
-> data cleansing
-> feature engineering
-> train / validation / test split
-> scaling fitted on training data
-> optional dimensionality reduction fitted on training data
-> model training
-> validation and tuning
-> final test evaluation
-> deployment
-> monitoring and retraining

PCA fits after numeric feature preparation and scaling when you want variance-preserving compression or visualization without labels. LDA fits after labels are available when you want class-separating dimensions, usually inside supervised training.

Data leakage mistakes include scaling on the full dataset before splitting, using test data during hyperparameter tuning, and creating features that accidentally include future or target information.

Monitoring matters because input data can drift, user behavior can change, model performance can degrade, and operational failures can appear after deployment.

Task 3 answer

The common three-stage plan is pretraining, task adaptation, and instruction finetuning. Pretraining learns language patterns from broad unlabeled text. Task-specific finetuning adapts the model to a narrower task. Instruction finetuning teaches the model to follow prompt-response formats.

Instruction data often contains fields such as instruction, optional input, and expected response. The fields are formatted into a prompt so the model learns to generate the response.

Padding makes examples in a batch the same length. Ignored labels prevent padding tokens, and sometimes prompt tokens, from contributing to the loss. This makes the loss focus on the target response tokens.

LLM-as-judge uses another model to score or compare generated answers. A weakness is that the judge can be biased, inconsistent, or wrong, and its scores are not objective truth.