LeetCode SQL - JOIN

Table of contents

LEFT JOIN

175. Combine Two Tables

SELECT
    firstName,
    lastName,
    city,
    "state"
FROM Person
LEFT JOIN Address USING (personId)

import pandas as pd

def combine_two_tables(person: pd.DataFrame, address: pd.DataFrame) -> pd.DataFrame:
    # O(n + m), O(1)
    return person.merge(address, on="personId", how="left")[["firstName", "lastName", "city", "state"]]

1988. Find Cutoff Score for Each School

SELECT
    school_id,
    COALESCE(MIN(score), -1) AS score
FROM
    Schools
    LEFT JOIN Exam ON capacity >= student_count
GROUP BY school_id

import pandas as pd

def find_cutoff_score(schools: pd.DataFrame, exam: pd.DataFrame) -> pd.DataFrame:
    cross_join = schools.merge(exam, how="cross")
    valid = cross_join[cross_join["capacity"] >= cross_join["student_count"]]
    res = valid.groupby("school_id")["score"].min()
    res = schools[["school_id"]].merge(result, on="school_id", how="left")
    res.fillna({"score": -1}, inplace=True)

    return res

577. Employee Bonus

SELECT
    e.name,
    b.bonus
FROM
    Employee e
    LEFT JOIN Bonus b USING (empId)
WHERE
    b.bonus < 1000
    OR b.bonus IS NULL

import pandas as pd

def employee_bonus(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
    df = employee.merge(bonus, on="empId", how="left")
    filter = (df.bonus < 1000) | (df.bonus.isna())

    return df[filter][["name", "bonus"]]

2020. Number of Accounts That Did Not Stream

LeetCode DB 2020: Validator Bugs + Typical Logic Mistakes You May Have

INCORRECT Queries can pass the testcases

SELECT
    COUNT(account_id) AS accounts_count
FROM
    subscriptions
    JOIN streams USING (account_id)
WHERE
    EXTRACT(YEAR FROM end_date) = 2021
    AND EXTRACT(YEAR FROM stream_date) != 2021

CORRECT Queries

Safe method

SELECT COUNT(*) AS accounts_count
FROM Subscriptions su
WHERE
    -- ✅: Active subscription during 2021
    (s.start_date <= '2021-12-31' AND s.end_date >= '2021-01-01')
    -- ✅: Only contains 2021 streams and no NULL records
    AND NOT EXISTS (
        SELECT 1
        FROM Streams st
        WHERE st.account_id = su.account_id
            AND EXTRACT(YEAR FROM st.stream_date) = 2021
    )

More difficult

SELECT COUNT(su.account_id) AS accounts_count
FROM 
	subscriptions su
    LEFT JOIN streams st ON su.account_id = st.account_id
        -- ✅: Only join 2021 streams
        AND EXTRACT(YEAR FROM st.stream_date) = 2021
WHERE
    -- ✅: Active subscriptions during 2021
    (su.start_date <= '2021-12-31' AND su.end_date >= '2021-01-01')
    -- ✅: No 2021 streams found
    AND st.account_id IS NULL;

SELF JOIN

181. Employees Earning More Than Their Managers

SELECT e.name AS Employee
FROM
    Employee e
    INNER JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary

import pandas as pd

def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
    df = employee.merge(employee, left_on="managerId", right_on="id", suffixes=("_emp", "_mgr"))
    filter = df["salary_emp"] > df["salary_mgr"]
    df.rename(columns={"name_emp": "Employee"}, inplace=True)

    return df[filter][["Employee"]]

197. Rising Temperature

SELECT
    w2.id
FROM
    Weather w1
    INNER JOIN Weather w2 ON w1.recordDate + 1 = w2.recordDate
WHERE
    w1.temperature < w2.temperature

import pandas as pd

def rising_temperature(weather: pd.DataFrame) -> pd.DataFrame:
    df = weather.merge(
        weather, 
        left_on="recordDate",
        right_on=weather["recordDate"] + pd.Timedelta(days=1),
        suffixes=("_today", "_yesterday")
        )
    filter = df["temperature_today"] > df["temperature_yesterday"]

    return df[filter].rename(columns={"id_today": "id"})[["id"]]

INNER JOIN

1501. Countries You Can Safely Invest In

See LeetCode SQL Analysis - 1501. Countries You Can Safely Invest In

1440. Evaluate Boolean Expression

SELECT
    e.*,
    CASE
        WHEN e.operator = '=' AND v1.value = v2.value THEN 'true'
        WHEN e.operator = '>' AND v1.value > v2.value THEN 'true'
        WHEN e.operator = '<' AND v1.value < v2.value THEN 'true'
        ELSE 'false'
    END AS value
FROM
    Expressions e
    JOIN Variables v1 ON e.left_operand = v1.name
    JOIN Variables v2 ON e.right_operand = v2.name

FULL OUTER JOIN

Notice MySQL doesn’t have FULL OUTER JOIN, you must use LEFT JOIN + RIGHT JOIN to achieve the similar functionality. FULL OUTER JOIN is particularly used for searching missing values.

See LeetCode SQL Missing Values

CROSS JOIN

Notice MySQL doesn’t have FULL OUTER JOIN, you must use UNION ALL to achieve the similar functionality. CROSS JOIN is particularly used for searching missing values.

See LeetCode SQL Missing Values