Distinction Apprentices

Problem

An apprenticeship scheme records data in apprentices(apprentice_id, full_name, trade), units(unit_id, title, weeks, trade, core) where core is 'Yes' or 'No', and records(apprentice_id, unit_id, cohort, mark, score). An apprentice earns a distinction when they meet ALL of these conditions within their own trade: they took every core unit of their trade and scored mark 'A' in each; they took at least two optional (non-core) units of their trade and scored at least 'B' (i.e. 'A' or 'B') in each of those optional units they took; and their overall average score across all their records is at least 2.5. Return the apprentice_id of each qualifying apprentice, ordered ascending.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns apprentice_id.

Starter code (SQL)

SELECT *
FROM apprentices;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard