ST0018 As an SSR I would like to see sample runs that have failed Data QC in the report so that the failure rates are easily visible.

[TWJW-SANGER]

User story
As an SSR I would like to see sample runs that have failed Data QC in the report so that the failure rates are easily visible.

Who are the primary contacts for this story
Rich C

Who is the nominated tester for UAT
Rich C

Acceptance criteria
To be considered successful the solution must allow:

• Add a new column sequence_qc_fail_count after column sequencing_qc_complete_count, that counts the number of sequencing_complete events for samples from the manifest plate that have 'failed' for the associated metadata value of the metadata key 'result'. (See example SQL below)
• Change the existing column sequencing_qc_complete_count to sequencing_qc_pass_count and to count the number of sequencing_complete events for samples from the manifest plate that have 'passed' for the associated metadata value of the metadata key 'result'.
• Update the Confluence page describing the table columns. Note that the total count of sequencing_complete events (either passed or failed) can exceed the number of samples, when samples or libraries are re-run.

Notes
We are currently showing the count of sequencing_complete events for all samples associated with the original manifest plate in the Sequencing QC Complete column. This actually includes Data QC failures and passes. These can be distinguished by looking at the metadata key/value pair 'result' associated with the event.

Example SQL:

SELECT e.id
, et.key AS "event_type"
, e.occured_at AS 'Date'
, mt.key
, mt.value 
 
FROM mlwh_events.events e
JOIN mlwh_events.event_types et ON (e.event_type_id=et.id)
 

-- look for event metadata
JOIN mlwh_events.metadata mt ON (mt.event_id=e.id)
 
WHERE
et.key = 'sequencing_complete'
AND mt.key = 'result'
AND e.occured_at > DATE('2024-06-15')
LIMIT 10

Generates

+---------+---------------------+---------------------+--------+--------+
| id      | event_type          | Date                | key    | value  |
+---------+---------------------+---------------------+--------+--------+
| 4650446 | sequencing_complete | 2024-06-17 21:00:20 | result | passed |
| 4650447 | sequencing_complete | 2024-06-17 21:00:22 | result | passed |
| 4650448 | sequencing_complete | 2024-06-17 21:00:23 | result | passed |
| 4650449 | sequencing_complete | 2024-06-17 21:00:25 | result | passed |
| 4650450 | sequencing_complete | 2024-06-17 21:00:26 | result | failed |
| 4650451 | sequencing_complete | 2024-06-17 21:00:28 | result | passed |
| 4650452 | sequencing_complete | 2024-06-17 21:00:29 | result | passed |
| 4650453 | sequencing_complete | 2024-06-17 21:00:31 | result | passed |
| 4650454 | sequencing_complete | 2024-06-17 21:00:32 | result | passed |
| 4650455 | sequencing_complete | 2024-06-17 21:00:34 | result | passed |
+---------+---------------------+---------------------+--------+--------+

[khelwood]

@TWJW-SANGER It looks like by using count distinct subject_uuid_bin, the view can actually count samples instead of counting events. I assume this is preferable?

---

Agreed that this is preferable.