Basic Joins¶

Table of Contents¶

1378. Replace Employee ID With The Unique Identifier (Easy)
1068. Product Sales Analysis I (Easy)
1581. Customer Who Visited but Did Not Make Any Transactions (Easy)
197. Rising Temperature (Easy)
1661. Average Time of Process per Machine (Easy)
577. Employee Bonus (Easy)
1280. Students and Examinations (Easy)
570. Managers with at Least 5 Direct Reports (Medium)
1934. Confirmation Rate (Medium)

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;