LeetCode SQL - Filter

Posted on Edited on

1527. Patients With a Condition

REGEXP_LIKE

1
2
3
4
5
6
SELECT
    patient_id,
    patient_name,
    conditions
FROM Patients
WHERE REGEXP_LIKE(conditions, "([:space:]|^)DIAB1.*")

LIKE

1
2
3
4
5
6
7
SELECT
    patient_id,
    patient_name,
    conditions
FROM Patients
WHERE conditions LIKE "DIAB1%"
    OR conditions LIKE "% DIAB1%"

1873. Calculate Special Bonus

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE if EXISTS Employees;
CREATE TABLE
    If NOT EXISTS Employees (employee_id INT, name VARCHAR(30), salary INT);
TRUNCATE TABLE Employees;
INSERT INTO
    Employees (employee_id, name, salary)
VALUES
    ('2', 'Meir', '3000');
INSERT INTO
    Employees (employee_id, name, salary)
VALUES
    ('3', 'Michael', '3800');
INSERT INTO
    Employees (employee_id, name, salary)
VALUES
    ('7', 'Addilyn', '7400');
INSERT INTO
    Employees (employee_id, name, salary)
VALUES
    ('8', 'Juan', '6100');
INSERT INTO
    Employees (employee_id, name, salary)
VALUES
    ('9', 'Kannon', '7700');

REGEXP

1
2
3
4
5
SELECT
    employee_id, 
    salary * (employee_id % 2) * (NOT name regexp '^M.*') AS bonus
FROM Employees
ORDER BY 1;
1
2
3
4
5
6
7
8
SELECT
    employee_id,
    CASE
        WHEN (employee_id % 2 = 1) AND (NOT name regexp '^M.*') THEN salary
        ELSE 0
    END AS bonus
FROM Employees
ORDER BY 1;
1
2
3
4
5
SELECT
    employee_id,
    IF((employee_id % 2 = 1) AND (NOT name regexp '^M.*'), salary, 0) AS bonus
FROM Employees
ORDER BY 1;

LIKE

1
2
3
4
5
SELECT
    employee_id, 
    salary * (employee_id % 2) * (NOT name LIKE 'M%') AS bonus
FROM Employees
ORDER BY 1;
1
2
3
4
5
6
7
8
SELECT
    employee_id,
    CASE
        WHEN (employee_id % 2 = 1) AND (NOT name like 'M%') THEN salary
        ELSE 0
    END AS bonus
FROM Employees
ORDER BY 1;
1
2
3
4
5
SELECT
    employee_id,
    IF((employee_id % 2 = 1) AND (NOT name LIKE 'M%'), salary, 0) AS bonus
FROM Employees
ORDER BY 1;

1517. Find Users With Valid E-Mails

1
2
3
4
5
6
SELECT
    *
FROM
    Users
WHERE
    REGEXP_LIKE(mail, "^[a-zA-Z][a-zA-Z0-9_.-]*(@leetcode\\.com)$")
1
2
3
4
5
6
import pandas as pd

def valid_emails(users: pd.DataFrame) -> pd.DataFrame:
    df = users[users.mail.str.contains(r'^[a-zA-Z][a-zA-Z0-9_.-]*(@leetcode\.com)$', regex=True)]

    return df