DNA Pattern Recognition 
<p>Table: <code>Samples</code></p>

<pre>
+----------------+---------+
| Column Name    | Type    | 
+----------------+---------+
| sample_id      | int     |
| dna_sequence   | varchar |
| species        | varchar |
+----------------+---------+
sample_id is the unique key for this table.
Each row contains a DNA sequence represented as a string of characters (A, T, G, C) and the species it was collected from.
</pre>

<p>Biologists are studying basic patterns in DNA sequences. Write a solution to identify <code>sample_id</code> with the following patterns:</p>

<ul>
	<li>Sequences that <strong>start</strong> with <strong>ATG</strong>&nbsp;(a common <strong>start codon</strong>)</li>
	<li>Sequences that <strong>end</strong> with either <strong>TAA</strong>, <strong>TAG</strong>, or <strong>TGA</strong>&nbsp;(<strong>stop codons</strong>)</li>
	<li>Sequences containing the motif <strong>ATAT</strong>&nbsp;(a simple repeated pattern)</li>
	<li>Sequences that have <strong>at least</strong> <code>3</code> <strong>consecutive</strong> <strong>G</strong>&nbsp;(like <strong>GGG</strong>&nbsp;or <strong>GGGG</strong>)</li>
</ul>

<p>Return <em>the result table ordered by&nbsp;</em><em>sample_id in <strong>ascending</strong> order</em>.</p>

<p>The result format is in the following example.</p>

<p>&nbsp;</p>
<p><strong class="example">Example:</strong></p>

<div class="example-block">
<p><strong>Input:</strong></p>

<p>Samples table:</p>

<pre class="example-io">
+-----------+------------------+-----------+
| sample_id | dna_sequence     | species   |
+-----------+------------------+-----------+
| 1         | ATGCTAGCTAGCTAA  | Human     |
| 2         | GGGTCAATCATC     | Human     |
| 3         | ATATATCGTAGCTA   | Human     |
| 4         | ATGGGGTCATCATAA  | Mouse     |
| 5         | TCAGTCAGTCAG     | Mouse     |
| 6         | ATATCGCGCTAG     | Zebrafish |
| 7         | CGTATGCGTCGTA    | Zebrafish |
+-----------+------------------+-----------+
</pre>

<p><strong>Output:</strong></p>

<pre class="example-io">
+-----------+------------------+-------------+-------------+------------+------------+------------+
| sample_id | dna_sequence     | species     | has_start   | has_stop   | has_atat   | has_ggg    |
+-----------+------------------+-------------+-------------+------------+------------+------------+
| 1         | ATGCTAGCTAGCTAA  | Human       | 1           | 1          | 0          | 0          |
| 2         | GGGTCAATCATC     | Human       | 0           | 0          | 0          | 1          |
| 3         | ATATATCGTAGCTA   | Human       | 0           | 0          | 1          | 0          |
| 4         | ATGGGGTCATCATAA  | Mouse       | 1           | 1          | 0          | 1          |
| 5         | TCAGTCAGTCAG     | Mouse       | 0           | 0          | 0          | 0          |
| 6         | ATATCGCGCTAG     | Zebrafish   | 0           | 1          | 1          | 0          |
| 7         | CGTATGCGTCGTA    | Zebrafish   | 0           | 0          | 0          | 0          |
+-----------+------------------+-------------+-------------+------------+------------+------------+
</pre>

<p><strong>Explanation:</strong></p>

<ul>
	<li>Sample 1 (ATGCTAGCTAGCTAA):
	<ul>
		<li>Starts with ATG&nbsp;(has_start = 1)</li>
		<li>Ends with TAA&nbsp;(has_stop = 1)</li>
		<li>Does not contain ATAT&nbsp;(has_atat = 0)</li>
		<li>Does not contain at least 3 consecutive &#39;G&#39;s (has_ggg = 0)</li>
	</ul>
	</li>
	<li>Sample 2 (GGGTCAATCATC):
	<ul>
		<li>Does not start with ATG&nbsp;(has_start = 0)</li>
		<li>Does not end with TAA, TAG, or TGA&nbsp;(has_stop = 0)</li>
		<li>Does not contain ATAT&nbsp;(has_atat = 0)</li>
		<li>Contains GGG&nbsp;(has_ggg = 1)</li>
	</ul>
	</li>
	<li>Sample 3 (ATATATCGTAGCTA):
	<ul>
		<li>Does not start with ATG&nbsp;(has_start = 0)</li>
		<li>Does not end with TAA, TAG, or TGA&nbsp;(has_stop = 0)</li>
		<li>Contains ATAT&nbsp;(has_atat = 1)</li>
		<li>Does not contain at least 3 consecutive &#39;G&#39;s (has_ggg = 0)</li>
	</ul>
	</li>
	<li>Sample 4 (ATGGGGTCATCATAA):
	<ul>
		<li>Starts with ATG&nbsp;(has_start = 1)</li>
		<li>Ends with TAA&nbsp;(has_stop = 1)</li>
		<li>Does not contain ATAT&nbsp;(has_atat = 0)</li>
		<li>Contains GGGG&nbsp;(has_ggg = 1)</li>
	</ul>
	</li>
	<li>Sample 5 (TCAGTCAGTCAG):
	<ul>
		<li>Does not match any patterns (all fields = 0)</li>
	</ul>
	</li>
	<li>Sample 6 (ATATCGCGCTAG):
	<ul>
		<li>Does not start with ATG&nbsp;(has_start = 0)</li>
		<li>Ends with TAG&nbsp;(has_stop = 1)</li>
		<li>Starts with ATAT&nbsp;(has_atat = 1)</li>
		<li>Does not contain at least 3 consecutive &#39;G&#39;s (has_ggg = 0)</li>
	</ul>
	</li>
	<li>Sample 7 (CGTATGCGTCGTA):
	<ul>
		<li>Does not start with ATG&nbsp;(has_start = 0)</li>
		<li>Does not end with TAA, &quot;TAG&quot;, or &quot;TGA&quot; (has_stop = 0)</li>
		<li>Does not contain ATAT&nbsp;(has_atat = 0)</li>
		<li>Does not contain at least 3 consecutive &#39;G&#39;s (has_ggg = 0)</li>
	</ul>
	</li>
</ul>

<p><strong>Note:</strong></p>

<ul>
	<li>The result is ordered by sample_id in ascending order</li>
	<li>For each pattern, 1 indicates the pattern is present and 0 indicates it is not present</li>
</ul>
</div>

[]
['CREATE TABLE if not exists Samples (\n    sample_id INT,\n    dna_sequence VARCHAR(255),\n    species VARCHAR(100)\n)', 'Truncate table Samples', "insert into Samples (sample_id, dna_sequence, species) values ('1', 'ATGCTAGCTAGCTAA', 'Human')", "insert into Samples (sample_id, dna_sequence, species) values ('2', 'GGGTCAATCATC', 'Human')", "insert into Samples (sample_id, dna_sequence, species) values ('3', 'ATATATCGTAGCTA', 'Human')", "insert into Samples (sample_id, dna_sequence, species) values ('4', 'ATGGGGTCATCATAA', 'Mouse')", "insert into Samples (sample_id, dna_sequence, species) values ('5', 'TCAGTCAGTCAG', 'Mouse')", "insert into Samples (sample_id, dna_sequence, species) values ('6', 'ATATCGCGCTAG', 'Zebrafish')", "insert into Samples (sample_id, dna_sequence, species) values ('7', 'CGTATGCGTCGTA', 'Zebrafish')"]
[{"value": "mysql", "text": "MySQL", "defaultCode": "# Write your MySQL query statement below\n"}, {"value": "mssql", "text": "MS SQL Server", "defaultCode": "/* Write your T-SQL query statement below */\n"}, {"value": "oraclesql", "text": "Oracle", "defaultCode": "/* Write your PL/SQL query statement below */\n"}, {"value": "pythondata", "text": "Pandas", "defaultCode": "import pandas as pd\n\ndef analyze_dna_patterns(samples: pd.DataFrame) -> pd.DataFrame:\n    "}, {"value": "postgresql", "text": "PostgreSQL", "defaultCode": "-- Write your PostgreSQL query statement below\n"}]
{"mysql": ["CREATE TABLE if not exists Samples (\n    sample_id INT,\n    dna_sequence VARCHAR(255),\n    species VARCHAR(100)\n)"], "mssql": ["CREATE TABLE Samples (\n    sample_id INT,\n    dna_sequence VARCHAR(255),\n    species VARCHAR(100)\n)"], "oraclesql": ["CREATE TABLE Samples (\n    sample_id NUMBER,\n    dna_sequence VARCHAR2(255),\n    species VARCHAR2(100)\n)"], "database": true, "name": "analyze_dna_patterns", "postgresql": ["CREATE TABLE IF NOT EXISTS Samples (\n    sample_id INT,\n    dna_sequence TEXT NOT NULL,\n    species VARCHAR(100) NOT NULL\n);\n"], "pythondata": ["Samples = pd.DataFrame({\n    'sample_id': pd.Series(dtype='int'),        # Equivalent to SERIAL/INTEGER\n    'dna_sequence': pd.Series(dtype='string'),  # Equivalent to TEXT/VARCHAR\n    'species': pd.Series(dtype='string')        # Equivalent to VARCHAR(100)\n})"], "database_schema": {"Samples": {"sample_id": "INT", "dna_sequence": "VARCHAR(255)", "species": "VARCHAR(100)"}}}
{"mysql": ["MySQL", "<p><code>MySQL 8.0</code>.</p>"], "mssql": ["MS SQL Server", "<p><code>mssql server 2019</code>.</p>"], "oraclesql": ["Oracle", "<p><code>Oracle Sql 11.2</code>.</p>"], "pythondata": ["Pandas", "<p>Python 3.10 with Pandas 2.2.2 and NumPy 1.26.4</p>"], "postgresql": ["PostgreSQL", "<p>PostgreSQL 16</p>"]}
['Database']