LeetCode SQL - Window Function

Window Function

1204. Last Person to Fit in the Bus

DROP TABLE if EXISTS Queue;
CREATE TABLE
    If NOT EXISTS Queue (person_id INT, person_name VARCHAR(30), weight INT, turn INT);
TRUNCATE TABLE Queue;
INSERT INTO
    Queue (person_id, person_name, weight, turn)
VALUES
    ('5', 'Alice', '250', '1');
INSERT INTO
    Queue (person_id, person_name, weight, turn)
VALUES
    ('4', 'Bob', '175', '5');
INSERT INTO
    Queue (person_id, person_name, weight, turn)
VALUES
    ('3', 'Alex', '350', '2');
INSERT INTO
    Queue (person_id, person_name, weight, turn)
VALUES
    ('6', 'John Cena', '400', '3');
INSERT INTO
    Queue (person_id, person_name, weight, turn)
VALUES
    ('1', 'Winston', '500', '6');
INSERT INTO
    Queue (person_id, person_name, weight, turn)
VALUES
    ('2', 'Marie', '200', '4');

WITH
    cte AS (
        SELECT
            *,
            SUM(weight) OVER (ORDER BY turn ASC) AS total_weight
        FROM Queue
    )
SELECT
    person_name
FROM
    cte
WHERE
    total_weight <= 1000
ORDER BY
    total_weight DESC
LIMIT
    1;

---

Window Function + Group

1082. Sales Analysis I

DROP TABLE if EXISTS Product;
DROP TABLE if EXISTS Sales;
CREATE TABLE
    If NOT EXISTS Product (product_id INT, product_name VARCHAR(10), unit_price INT);
CREATE TABLE
    If NOT EXISTS Sales (
        seller_id INT,
        product_id INT,
        buyer_id INT,
        sale_date DATE,
        quantity INT,
        price INT
    );
TRUNCATE TABLE Product;
INSERT INTO
    Product (product_id, product_name, unit_price)
VALUES
    ('1', 'S8', '1000');
INSERT INTO
    Product (product_id, product_name, unit_price)
VALUES
    ('2', 'G4', '800');
INSERT INTO
    Product (product_id, product_name, unit_price)
VALUES
    ('3', 'iPhone', '1400');
TRUNCATE TABLE Sales;
INSERT INTO
    Sales (seller_id, product_id, buyer_id, sale_date, quantity, price)
VALUES
    ('1', '1', '1', '2019-01-21', '2', '2000');
INSERT INTO
    Sales (seller_id, product_id, buyer_id, sale_date, quantity, price)
VALUES
    ('1', '2', '2', '2019-02-17', '1', '800');
INSERT INTO
    Sales (seller_id, product_id, buyer_id, sale_date, quantity, price)
VALUES
    ('2', '2', '3', '2019-06-02', '1', '800');
INSERT INTO
    Sales (seller_id, product_id, buyer_id, sale_date, quantity, price)
VALUES
    ('3', '3', '4', '2019-05-13', '2', '2800');

WITH
    cte AS (
        SELECT
            seller_id,
            DENSE_RANK() OVER (
                ORDER BY
                    SUM(price) desc
            ) AS sales_dr
        FROM
            Sales
        GROUP BY
            seller_id
    )
SELECT
    seller_id
FROM
    cte
WHERE
    sales_dr = 1;

/*Query the best seller by total sales price. If there is a tie, report them all.
- Product PK: product_id
 */
WITH
    cte AS (
        SELECT
            seller_id,
            SUM(price) AS price
        FROM
            sales
        GROUP BY
            seller_id
    )
SELECT
    seller_id
FROM
    cte
WHERE
    price = (
        SELECT
            MAX(price)
        FROM
            cte
    );

---

1355. Activity Participants

DROP TABLE if EXISTS Friends;
DROP TABLE if EXISTS Activities;
CREATE TABLE
    If NOT EXISTS Friends (id INT, name VARCHAR(30), activity VARCHAR(30));
CREATE TABLE
    If NOT EXISTS Activities (id INT, name VARCHAR(30));
TRUNCATE TABLE Friends;
INSERT INTO
    Friends (id, name, activity)
VALUES
    ('1', 'Jonathan D.', 'Eating');
INSERT INTO
    Friends (id, name, activity)
VALUES
    ('2', 'Jade W.', 'Singing');
INSERT INTO
    Friends (id, name, activity)
VALUES
    ('3', 'Victor J.', 'Singing');
INSERT INTO
    Friends (id, name, activity)
VALUES
    ('4', 'Elvis Q.', 'Eating');
INSERT INTO
    Friends (id, name, activity)
VALUES
    ('5', 'Daniel A.', 'Eating');
INSERT INTO
    Friends (id, name, activity)
VALUES
    ('6', 'Bob B.', 'Horse Riding');
TRUNCATE TABLE Activities;
INSERT INTO
    Activities (id, name)
VALUES
    ('1', 'Eating');
INSERT INTO
    Activities (id, name)
VALUES
    ('2', 'Singing');
INSERT INTO
    Activities (id, name)
VALUES
    ('3', 'Horse Riding');

WITH
    cte AS (
        SELECT
            activity,
            RANK() OVER (
                ORDER BY
                    COUNT(id) asc
            ) AS ar_activity,
            RANK() OVER (
                ORDER BY
                    COUNT(id) desc
            ) dr_activity
        FROM
            Friends
        GROUP BY
            activity
    )
SELECT
    activity
FROM
    cte
WHERE
    ar_activity != 1
    AND dr_activity != 1;