DNA Sequence Pattern Signal Audit

Medium

A genomics analytics team stores raw sequence reads and needs a fast pattern-audit view for downstream quality workflows. Table: Samples sample_id: unique sample identifier dna_sequence: nucleotide sequence string species: species label attached to the sample Compute four case-sensitive diagnostic flags for every row: has_start = 1 when dna_sequence starts with 'ATG', else 0. has_stop = 1 when dna_sequence ends with 'TAA', 'TAG', or 'TGA', else 0. has_atat = 1 when dna_sequence contains contiguous substring 'ATAT', else 0. has_ggg = 1 when dna_sequence contains at least three consecutive uppercase 'G', else 0. Task: Return every sample row with these four flags. Output requirements: Return columns in this exact order: sample_id, dna_sequence, species, has_start, has_stop, has_atat, has_ggg Sort by sample_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Samples:
| sample_id | dna_sequence     | species   |
|-----------|------------------|-----------|
| 1         | ATGCCCTAA        | Human     |
| 2         | GGGATCCTTC       | Human     |
| 3         | TATATCGCATAG     | Mouse     |
| 4         | ATGATATGGGCTA    | Zebrafish |

Output

[
  {"sample_id":1,"dna_sequence":"ATGCCCTAA","species":"Human","has_start":1,"has_stop":1,"has_atat":0,"has_ggg":0},
  {"sample_id":2,"dna_sequence":"GGGATCCTTC","species":"Human","has_start":0,"has_stop":0,"has_atat":0,"has_ggg":1},
  {"sample_id":3,"dna_sequence":"TATATCGCATAG","species":"Mouse","has_start":0,"has_stop":1,"has_atat":1,"has_ggg":0},
  {"sample_id":4,"dna_sequence":"ATGATATGGGCTA","species":"Zebrafish","has_start":1,"has_stop":0,"has_atat":1,"has_ggg":1}
]

Explanation

Each output flag is computed independently from the same sequence, then rows are ordered by sample_id.

Example 2

Input

Samples:
| sample_id | dna_sequence   | species |
|-----------|----------------|---------|
| 10        | atgCCCTAA      | Human   |
| 11        | ATGCCCTaa      | Mouse   |
| 12        | ATATGGGTTTAG   | Human   |
| 13        | CCGTGCGTACCC   | Rat     |

Output

[
  {"sample_id":10,"dna_sequence":"atgCCCTAA","species":"Human","has_start":0,"has_stop":1,"has_atat":0,"has_ggg":0},
  {"sample_id":11,"dna_sequence":"ATGCCCTaa","species":"Mouse","has_start":1,"has_stop":0,"has_atat":0,"has_ggg":0},
  {"sample_id":12,"dna_sequence":"ATATGGGTTTAG","species":"Human","has_start":0,"has_stop":1,"has_atat":1,"has_ggg":1},
  {"sample_id":13,"dna_sequence":"CCGTGCGTACCC","species":"Rat","has_start":0,"has_stop":0,"has_atat":0,"has_ggg":0}
]

Explanation

Pattern checks are case-sensitive. For example, lowercase 'atg' does not satisfy the start rule.

Example 3

Input

Samples:
| sample_id | dna_sequence | species     |
|-----------|--------------|-------------|
| 20        | ATG          | Human       |
| 21        | TAA          | Human       |
| 22        | ATAT         | Mouse       |
| 23        | GGG          | Zebrafish   |
| 24        | ATGGGGTGA    | Arabidopsis |

Output

[
  {"sample_id":20,"dna_sequence":"ATG","species":"Human","has_start":1,"has_stop":0,"has_atat":0,"has_ggg":0},
  {"sample_id":21,"dna_sequence":"TAA","species":"Human","has_start":0,"has_stop":1,"has_atat":0,"has_ggg":0},
  {"sample_id":22,"dna_sequence":"ATAT","species":"Mouse","has_start":0,"has_stop":0,"has_atat":1,"has_ggg":0},
  {"sample_id":23,"dna_sequence":"GGG","species":"Zebrafish","has_start":0,"has_stop":0,"has_atat":0,"has_ggg":1},
  {"sample_id":24,"dna_sequence":"ATGGGGTGA","species":"Arabidopsis","has_start":1,"has_stop":1,"has_atat":0,"has_ggg":1}
]

Explanation

Short sequences are valid inputs. Each rule applies directly without requiring joins or grouping.

Constraints

1 <= number of rows in Samples <= 200000
1 <= sample_id <= 1000000000
sample_id is unique
1 <= length of dna_sequence <= 500
dna_sequence uses characters from A/T/G/C and may contain lowercase variants from upstream ingest
species is a non-empty text label
Pattern checks are case-sensitive
has_start checks exact prefix 'ATG'
has_stop checks exact suffix in {'TAA', 'TAG', 'TGA'}
has_atat checks contiguous substring 'ATAT'
has_ggg checks contiguous substring 'GGG' (or longer runs such as 'GGGG')
Return columns exactly as: sample_id, dna_sequence, species, has_start, has_stop, has_atat, has_ggg
Order result by sample_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required columns

Samples: | sample_id | dna_sequence | species | |-----------|------------------|-----------| | 1 | ATGCCCTAA | Human | | 2 | GGGATCCTTC | Human | | 3 | TATATCGCATAG | Mouse | | 4 | ATGATATGGGCTA | Zebrafish |

[ {"sample_id":1,"dna_sequence":"ATGCCCTAA","species":"Human","has_start":1,"has_stop":1,"has_atat":0,"has_ggg":0}, {"sample_id":2,"dna_sequence":"GGGATCCTTC","species":"Human","has_start":0,"has_stop":0,"has_atat":0,"has_ggg":1}, {"sample_id":3,"dna_sequence":"TATATCGCATAG","species":"Mouse","has_start":0,"has_stop":1,"has_atat":1,"has_ggg":0}, {"sample_id":4,"dna_sequence":"ATGATATGGGCTA","species":"Zebrafish","has_start":1,"has_stop":0,"has_atat":1,"has_ggg":1} ]

Samples: | sample_id | dna_sequence | species | |-----------|----------------|---------| | 10 | atgCCCTAA | Human | | 11 | ATGCCCTaa | Mouse | | 12 | ATATGGGTTTAG | Human | | 13 | CCGTGCGTACCC | Rat |

[ {"sample_id":10,"dna_sequence":"atgCCCTAA","species":"Human","has_start":0,"has_stop":1,"has_atat":0,"has_ggg":0}, {"sample_id":11,"dna_sequence":"ATGCCCTaa","species":"Mouse","has_start":1,"has_stop":0,"has_atat":0,"has_ggg":0}, {"sample_id":12,"dna_sequence":"ATATGGGTTTAG","species":"Human","has_start":0,"has_stop":1,"has_atat":1,"has_ggg":1}, {"sample_id":13,"dna_sequence":"CCGTGCGTACCC","species":"Rat","has_start":0,"has_stop":0,"has_atat":0,"has_ggg":0} ]

Samples: | sample_id | dna_sequence | species | |-----------|--------------|-------------| | 20 | ATG | Human | | 21 | TAA | Human | | 22 | ATAT | Mouse | | 23 | GGG | Zebrafish | | 24 | ATGGGGTGA | Arabidopsis |

[ {"sample_id":20,"dna_sequence":"ATG","species":"Human","has_start":1,"has_stop":0,"has_atat":0,"has_ggg":0}, {"sample_id":21,"dna_sequence":"TAA","species":"Human","has_start":0,"has_stop":1,"has_atat":0,"has_ggg":0}, {"sample_id":22,"dna_sequence":"ATAT","species":"Mouse","has_start":0,"has_stop":0,"has_atat":1,"has_ggg":0}, {"sample_id":23,"dna_sequence":"GGG","species":"Zebrafish","has_start":0,"has_stop":0,"has_atat":0,"has_ggg":1}, {"sample_id":24,"dna_sequence":"ATGGGGTGA","species":"Arabidopsis","has_start":1,"has_stop":1,"has_atat":0,"has_ggg":1} ]