awesome-leetcode-sql/leetcode/medium/3475. DNA Pattern Recognition.sql at 6ee63c9ea414241fed1b9e3034b3246a6d0fcab4 · Ivanbyone/awesome-leetcode-sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/*
Question 3475. DNA Pattern Recognition
Link: https://leetcode.com/problems/dna-pattern-recognition/description/?envType=problem-list-v2&envId=database

Table: Samples

+----------------+---------+
| 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.
Biologists are studying basic patterns in DNA sequences. Write a solution to identify sample_id with the following patterns:

Sequences that start with ATG (a common start codon)
Sequences that end with either TAA, TAG, or TGA (stop codons)
Sequences containing the motif ATAT (a simple repeated pattern)
Sequences that have at least 3 consecutive G (like GGG or GGGG)
Return the result table ordered by sample_id in ascending order.
*/

SELECT
    sample_id,
    dna_sequence,
    species,
    (CASE
        WHEN LEFT(dna_sequence, 3) = 'ATG'
            THEN 1
        ELSE 0
    END) AS has_start,
    (CASE
        WHEN RIGHT(dna_sequence, 3) IN ('TAA', 'TAG', 'TGA')
            THEN 1
        ELSE 0
    END) AS has_stop,
    (CASE
        WHEN dna_sequence ~ 'ATAT'
            THEN 1
        ELSE 0
    END) AS has_atat,
    (CASE
        WHEN dna_sequence ~ 'GGG'
            THEN 1
        ELSE 0
    END) AS has_ggg
FROM Samples
ORDER BY sample_id ASC