Go back

LeetCode SQL - Math

Table of contents

Open Table of contents

Median

569. Median Employee Salary((returns one or two values))

/*Select the median records
- Employee PK: Id

- SOLUTION: use ROW_NUMBER to rank ascending and descending the records, then take the middle one.
 */
WITH
    cte AS (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY
                    company
                ORDER BY
                    salary asc
            ) AS salary_r,
            ROW_NUMBER() OVER (
                PARTITION BY
                    company
                ORDER BY
                    salary desc
            ) AS salary_dr
        FROM
            Employee
    )
    -- select * from cte
SELECT
    MIN(id) AS id,
    company,
    salary
FROM
    cte
WHERE
    salary_r = salary_dr
    OR salary_r = salary_dr + 1
    OR salary_r = salary_dr - 1
GROUP BY
    2,
    3;

571. Find Median Given Frequency of Numbers

DROP TABLE if EXISTS Numbers;
CREATE TABLE
    If NOT EXISTS Numbers (num INT, frequency INT);
TRUNCATE TABLE Numbers;
INSERT INTO
    Numbers (num, frequency)
VALUES
    ('0', '7');
INSERT INTO
    Numbers (num, frequency)
VALUES
    ('1', '1');
INSERT INTO
    Numbers (num, frequency)
VALUES
    ('2', '3');
INSERT INTO
    Numbers (num, frequency)
VALUES
    ('3', '1');
SELECT
    AVG(n.num) AS median
FROM
    Numbers n
WHERE
    n.Frequency >= ABS(
        (
            SELECT
                SUM(Frequency)
            FROM
                Numbers
            WHERE
                num <= n.num
        ) - (
            SELECT
                SUM(Frequency)
            FROM
                Numbers
            WHERE
                num >= n.num
        )
    );

Euclidean distance

612. Shortest Distance in a Plane

DROP TABLE if EXISTS Point2D;
CREATE TABLE
    If NOT EXISTS Point2D (x INT NOT NULL, y INT NOT NULL);
TRUNCATE TABLE Point2D;
INSERT INTO
    Point2D (x, y)
VALUES
    ('-1', '-1');
INSERT INTO
    Point2D (x, y)
VALUES
    ('0', '0');
INSERT INTO
    Point2D (x, y)
VALUES
    ('-1', '-2');
SELECT
    MIN(ROUND(SQRT(pow (p1.x - p2.x, 2) + pow (p1.y - p2.y, 2)), 2)) AS shortest
FROM
    Point2D p1
INNER JOIN Point2D p2 ON p1.x != p2.x
    OR p1.y != p2.y;

613. Shortest Distance in a Line

SELECT MIN(ABS(p1.x - p2.x)) AS shortest
FROM
    Point p1
    JOIN Point p2 ON p1.x < p2.x

Game Scores

1841. League Statistics

DROP TABLE if EXISTS Teams;
DROP TABLE if EXISTS Matches;
CREATE TABLE
    If NOT EXISTS Teams (team_id INT, team_name VARCHAR(20));
CREATE TABLE
    If NOT EXISTS Matches (home_team_id INT, away_team_id INT, home_team_goals INT, away_team_goals INT);
TRUNCATE TABLE Teams;
INSERT INTO
    Teams (team_id, team_name)
VALUES
    ('1', 'Ajax');
INSERT INTO
    Teams (team_id, team_name)
VALUES
    ('4', 'Dortmund');
INSERT INTO
    Teams (team_id, team_name)
VALUES
    ('6', 'Arsenal');
TRUNCATE TABLE Matches;
INSERT INTO
    Matches (home_team_id, away_team_id, home_team_goals, away_team_goals)
VALUES
    ('1', '4', '0', '1');
INSERT INTO
    Matches (home_team_id, away_team_id, home_team_goals, away_team_goals)
VALUES
    ('1', '6', '3', '3');
INSERT INTO
    Matches (home_team_id, away_team_id, home_team_goals, away_team_goals)
VALUES
    ('4', '1', '5', '2');
INSERT INTO
    Matches (home_team_id, away_team_id, home_team_goals, away_team_goals)
VALUES
    ('6', '1', '0', '0');
WITH
    cte AS (
        SELECT
            home_team_id AS team_id,
            home_team_goals AS goal_for,
            away_team_goals AS goal_against,
            home_team_goals - away_team_goals AS goal_diff
        FROM
            Matches
        UNION ALL
        SELECT
            away_team_id AS team_id,
            away_team_goals AS goal_for,
            home_team_goals AS goal_against,
            away_team_goals - home_team_goals AS goal_diff
        FROM
            Matches
    )
SELECT
    team_name,
    COUNT(*) AS matches_played,
    SUM(
        CASE
            WHEN goal_diff = 0 THEN 1
            WHEN goal_diff > 0 THEN 3
            ELSE 0
        END
    ) AS points,
    SUM(goal_for) AS goal_for,
    SUM(goal_against) AS goal_against,
    SUM(goal_diff) AS goal_diff
FROM
    cte
    INNER JOIN Teams USING (team_id)
GROUP BY
    team_name
ORDER BY
    points desc,
    goal_diff desc,
    team_name asc;

1212. Team Scores in Football Tournament

WITH cte AS (
    SELECT
        host_team AS team_id,
        CASE
            WHEN host_goals > guest_goals THEN 3
            WHEN host_goals < guest_goals THEN 0
            ELSE 1
        END AS num_points
    FROM
        Matches
    UNION ALL
    SELECT
        guest_team AS team_id,
        CASE
            WHEN host_goals > guest_goals THEN 0
            WHEN host_goals < guest_goals THEN 3
            ELSE 1
        END AS num_points
    FROM
        Matches
)
SELECT
    t.team_id,
    t.team_name,
    COALESCE(SUM(num_points), 0) AS num_points
FROM
    Teams t
    LEFT JOIN cte c ON t.team_id = c.team_id
GROUP BY
    t.team_id,
    t.team_name
ORDER BY
    num_points DESC,
    team_id ASC

Rate

1934. Confirmation Rate

DROP TABLE if EXISTS Signups;
DROP TABLE if EXISTS Confirmations;
CREATE TABLE
    If NOT EXISTS Signups (user_id INT, time_stamp datetime);
CREATE TABLE
    If NOT EXISTS Confirmations (user_id INT, time_stamp datetime, action ENUM ('confirmed', 'timeout'));
TRUNCATE TABLE Signups;
INSERT INTO
    Signups (user_id, time_stamp)
VALUES
    ('3', '2020-03-21 10:16:13');
INSERT INTO
    Signups (user_id, time_stamp)
VALUES
    ('7', '2020-01-04 13:57:59');
INSERT INTO
    Signups (user_id, time_stamp)
VALUES
    ('2', '2020-07-29 23:09:44');
INSERT INTO
    Signups (user_id, time_stamp)
VALUES
    ('6', '2020-12-09 10:39:37');
TRUNCATE TABLE Confirmations;
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('3', '2021-01-06 03:30:46', 'timeout');
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('3', '2021-07-14 14:00:00', 'timeout');
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('7', '2021-06-12 11:57:29', 'confirmed');
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('7', '2021-06-13 12:58:28', 'confirmed');
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('7', '2021-06-14 13:59:27', 'confirmed');
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('2', '2021-01-22 00:00:00', 'confirmed');
INSERT INTO
    Confirmations (user_id, time_stamp, action)
VALUES
    ('2', '2021-02-28 23:59:59', 'timeout');
SELECT
    s.user_id,
    ifnull (ROUND(AVG(if (action = 'confirmed', 1, 0)), 2), 0) AS confirmation_rate
FROM
    Signups s
    LEFT JOIN Confirmations c USING (user_id)
GROUP BY
    s.user_id;

First and Last

1972. First and Last Call On the Same Day

DROP TABLE if EXISTS Calls;
CREATE TABLE
    If NOT EXISTS Calls (caller_id INT, recipient_id INT, call_time datetime);
TRUNCATE TABLE Calls;
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("8", "4", "2021-08-24 17:46:07");
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("4", "8", "2021-08-24 19:57:13");
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("5", "1", "2021-08-11 05:28:44");
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("8", "3", "2021-08-17 04:04:15");
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("11", "3", "2021-08-17 13:07:00");
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("8", "11", "2021-08-17 22:22:22");
INSERT INTO
    Calls (caller_id, recipient_id, call_time)
VALUES
    ("8", "3", "2021-08-17 23:22:22");
WITH
    cte AS (
        SELECT
            caller_id AS a,
            recipient_id AS b,
            call_time
        FROM
            Calls
        UNION ALL
        SELECT
            recipient_id AS a,
            caller_id AS b,
            call_time
        FROM
            Calls
    ),
    cte1 AS (
        SELECT
            a,
            b,
            DATE (call_time) AS call_date,
            DENSE_RANK() OVER (
                PARTITION BY
                    a,
                    DATE (call_time)
                ORDER BY
                    call_time
            ) AS call_ar,
            DENSE_RANK() OVER (
                PARTITION BY
                    a,
                    DATE (call_time)
                ORDER BY
                    call_time desc
            ) AS call_dr
        FROM
            cte
    )
SELECT DISTINCT
    a AS user_id
FROM
    cte1
WHERE
    call_ar = 1
    OR call_dr = 1
GROUP BY
    1,
    call_date
HAVING
    COUNT(DISTINCT (b)) = 1;

Max

1867. Orders With Maximum Quantity Above Average

 TABLE if EXISTS OrdersDetails;
CREATE TABLE
    If NOT EXISTS OrdersDetails (order_id INT, product_id INT, quantity INT);
TRUNCATE TABLE OrdersDetails;
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('1', '1', '12');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('1', '2', '10');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('1', '3', '15');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('2', '1', '8');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('2', '4', '4');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('2', '5', '6');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('3', '3', '5');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('3', '4', '18');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('4', '5', '2');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('4', '6', '8');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('5', '7', '9');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('5', '8', '9');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('3', '9', '20');
INSERT INTO
    OrdersDetails (order_id, product_id, quantity)
VALUES
    ('2', '9', '4');
DROPwith cte AS (
    SELECT
        order_id,
        AVG(quantity) AS avg_quantity
    FROM
        OrdersDetails
    GROUP BY
        order_id
)
SELECT DISTINCT
    order_id
FROM
    OrdersDetails
WHERE
    quantity > (
        SELECT
            MAX(avg_quantity)
        FROM
            cte
    );

Avg

1132. Reported Posts II

DROP TABLE if EXISTS Actions;
DROP TABLE if EXISTS Removals;
CREATE TABLE
    If NOT EXISTS Actions (
        user_id INT,
        post_id INT,
        action_date DATE,
        action ENUM ('view', 'like', 'reaction', 'comment', 'report', 'share'),
        extra VARCHAR(10)
    );
CREATE TABLE
    if NOT EXISTS Removals (post_id INT, remove_date DATE);
TRUNCATE TABLE Actions;
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('1', '1', '2019-07-01', 'view', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('1', '1', '2019-07-01', 'like', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('1', '1', '2019-07-01', 'share', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('2', '2', '2019-07-04', 'view', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('2', '2', '2019-07-04', 'report', 'spam');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('3', '4', '2019-07-04', 'view', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('3', '4', '2019-07-04', 'report', 'spam');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('4', '3', '2019-07-02', 'view', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('4', '3', '2019-07-02', 'report', 'spam');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('5', '2', '2019-07-03', 'view', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('5', '2', '2019-07-03', 'report', 'racism');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('5', '5', '2019-07-03', 'view', 'None');
INSERT INTO
    Actions (user_id, post_id, action_date, action, extra)
VALUES
    ('5', '5', '2019-07-03', 'report', 'racism');
TRUNCATE TABLE Removals;
INSERT INTO
    Removals (post_id, remove_date)
VALUES
    ('2', '2019-07-20');
INSERT INTO
    Removals (post_id, remove_date)
VALUES
    ('3', '2019-07-18');
WITH
    cte AS (
        SELECT
            action_date,
            COUNT(DISTINCT r.post_id) / COUNT(DISTINCT a.post_id) * 100 AS daily_percent
        FROM
            Actions a
            LEFT JOIN Removals r ON a.post_id = r.post_id
        WHERE
            extra = 'spam'
        GROUP BY
            action_date
    )
SELECT
    ROUND(AVG(daily_percent), 2) AS average_daily_percent
FROM
    cte;

Cumulative

579. Find Cumulative Salary of an Employee

DROP TABLE if EXISTS Employee;
CREATE TABLE
    If NOT EXISTS Employee (id INT, MONTH INT, salary INT);
TRUNCATE TABLE Employee;
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('1', '1', '20');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('2', '1', '20');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('1', '2', '30');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('2', '2', '30');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('3', '2', '40');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('1', '3', '40');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('3', '3', '60');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('1', '4', '60');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('3', '4', '70');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('1', '7', '90');
INSERT INTO
    Employee (id, MONTH, salary)
VALUES
    ('1', '8', '90');
WITH
    cte AS (
        SELECT
            id,
            MONTH,
            SUM(salary) OVER (
                PARTITION BY
                    id
                ORDER BY
                    MONTH ASC RANGE BETWEEN 2 PRECEDING
                    AND CURRENT ROW
            ) AS salary,
            ROW_NUMBER() OVER (
                PARTITION BY
                    id
                ORDER BY
                    MONTH desc
            ) AS rn
        FROM
            Employee
    )
SELECT
    id,
    MONTH,
    salary
FROM
    cte
WHERE
    rn > 1;

Geometric

610. Triangle Judgement

SELECT
    *,
    CASE
        WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
        ELSE 'No'
    END AS triangle
FROM Triangle
Share this post on:
Share this post via WhatsAppShare this post on FacebookShare this post on XShare this post via TelegramShare this post on PinterestShare this post via email
Previous Post
LeetCode SQL - Missing Values
Next Post
LeetCode SQL - JOIN