MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE cohort_units (
  cohort_id INTEGER,
  unit VARCHAR(3),
  part VARCHAR(1)
);

INSERT INTO cohort_units
  (cohort_id, unit, part)
VALUES
  (235, 'ABC', 'A'),
  (235, 'ABC', 'B'),
  (246, 'DEF', 'A'),
  (246, 'DEF', 'B'),
  (246, 'DEF', 'C');

CREATE TABLE cohort_students (
  user_id INTEGER,
  cohort_id INTEGER
);

INSERT INTO cohort_students
  (user_id, cohort_id)
VALUES
  (21, 235),
  (24, 235),
  (43, 235),
  (53, 246);

CREATE TABLE assignments (
  user_id INTEGER,
  cohort_id INTEGER,
  unit VARCHAR(4),
  draft1recdt DATE
);

INSERT INTO assignments
  (user_id, cohort_id, unit, draft1recdt)
VALUES
  (21, 235, 'ABCA', '2023-01-03'),
  (24, 235, 'ABCA', '2023-02-01'),
  (24, 235, 'ABCB', '2023-02-02');

Records: 5  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part)


user_id
cohort_id
unit
part
draft1recdt


24
235
ABC
A
2023-02-01

21
235
ABC
A
2023-01-03

24
235
ABC
B
2023-02-02


WITH completed AS (
 SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part) 
),
partcount AS (
SELECT COUNT(*) num_parts,
       cohort_id,
	   unit
  FROM cohort_units
 GROUP BY cohort_id, unit
)
SELECT completed.user_id, cohort_units.cohort_id, cohort_units.unit, 
       GROUP_CONCAT(completed.part) parts,
       COUNT(*) completed_parts,
       partcount.num_parts
  FROM cohort_units
  JOIN partcount
         ON cohort_units.cohort_id = partcount.cohort_id
        AND cohort_units.unit = partcount.unit
  JOIN completed
         ON completed.cohort_id = cohort_units.cohort_id
        AND completed.unit = cohort_units.unit
        AND completed.part = cohort_units.part
GROUP BY completed.user_id, cohort_units.cohort_id, cohort_units.unit, num_parts
HAVING COUNT(*) < partcount.num_parts


user_id
cohort_id
unit
parts
completed_parts
num_parts


21
235
ABC
A
1
2