Skip to content

Basic Joins

Table of Contents

1378. Replace Employee ID With The Unique Identifier

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    1378. Replace Employee ID With The Unique Identifier
    -- Replace employee id with the unique identifier
SELECT
    unique_id,
    name
FROM
    employees
LEFT JOIN employeeuni ON employees.id = employeeuni.id;

1068. Product Sales Analysis I

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    1068. Product Sales Analysis I
    SELECT
    p.product_name,
    s.year,
    s.price
FROM
    sales AS s
LEFT JOIN product AS p ON s.product_id = p.product_id;

1581. Customer Who Visited but Did Not Make Any Transactions

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    1581. Customer Who Visited but Did Not Make Any Transactions
    -- 1. Left Join
SELECT
    v.customer_id,
    COUNT(v.visit_id) AS count_no_trans
FROM
    visits AS v
LEFT JOIN transactions AS t ON v.visit_id = t.visit_id
WHERE
    t.transaction_id IS NULL
GROUP BY
    v.customer_id;

-- 2. Subquery
SELECT
    customer_id,
    COUNT(DISTINCT visit_id) AS count_no_trans
FROM
    visits
WHERE
    visit_id NOT IN (
        SELECT visit_id
        FROM
            transactions
    )
GROUP BY
    customer_id;

197. Rising Temperature

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    197. Rising Temperature
    SELECT w1.id
FROM
    weather AS w1
LEFT JOIN weather AS w2 ON w1.recorddate - INTERVAL 1 DAY = w2.recorddate
WHERE
    w1.temperature > w2.temperature;

1661. Average Time of Process per Machine

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    1661. Average Time of Process per Machine
    SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
    -- ROUND(SUM(a2.timestamp - a1.timestamp) / COUNT(*), 3) AS processing_time
FROM
    activity AS a1
INNER JOIN activity AS a2
    ON
        a1.machine_id = a2.machine_id
        AND a1.process_id = a2.process_id
        AND a1.timestamp < a2.timestamp
GROUP BY
    a1.machine_id;

577. Employee Bonus

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    577. Employee Bonus
    -- 1.
SELECT
    e.name,
    b.bonus
FROM
    employee AS e
LEFT JOIN bonus AS b ON e.empid = b.empid
WHERE
    b.bonus < 1000
    OR b.bonus IS NULL;

-- 2.
SELECT
    e.name,
    b.bonus
FROM
    employee AS e
LEFT JOIN bonus AS b ON e.empid = b.empid
WHERE
    COALESCE(b.bonus, 0) < 1000;

1280. Students and Examinations

  • LeetCode | LeetCode CH (Easy)

  • Tags: database

    1280. Students and Examinations
    SELECT
    st.student_id,
    st.student_name,
    su.subject_name,
    COUNT(ex.student_id) AS attended_exams
FROM
    students AS st
CROSS JOIN subjects AS su
LEFT JOIN examinations AS ex
    ON
        st.student_id = ex.student_id
        AND su.subject_name = ex.subject_name
GROUP BY
    st.student_id,
    st.student_name,
    su.subject_name
ORDER BY
    st.student_id,
    su.subject_name;

570. Managers with at Least 5 Direct Reports

  • LeetCode | LeetCode CH (Medium)

  • Tags: database

    570. Managers with at Least 5 Direct Reports
    SELECT e2.name
FROM
    employee AS e1
INNER JOIN employee AS e2 ON e1.managerid = e2.id
GROUP BY
    e2.id,
    e2.name
HAVING
    COUNT(e1.id) >= 5;

1934. Confirmation Rate

  • LeetCode | LeetCode CH (Medium)

  • Tags: database

    1934. Confirmation Rate
    SELECT
    s.user_id,
    ROUND(
        SUM(IF(c.action = 'confirmed', 1, 0)) / COUNT(*),
        2
    ) AS confirmation_rate
FROM
    signups AS s
LEFT JOIN confirmations AS c ON s.user_id = c.user_id
GROUP BY
    s.user_id;

Comments