DNA Sequence Pattern Signal Audit

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Samples =

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

DNA Sequence Pattern Signal Audit

Hints

DNA Sequence Pattern Signal Audit

Hints