Go back

LeetCode SQL - Analysis

Table of contents

Open Table of contents

Sales Analysis

1327. List the Products Ordered in a Period

SELECT
    p.product_name,
    SUM(o.unit) AS unit
FROM
    Orders o
    JOIN Products p ON o.product_id = p.product_id
WHERE to_char (o.order_date, 'YYYY-MM') = '2020-02'
GROUP BY p.product_name
HAVING SUM(unit) >= 100

1251. Average Selling Price

SELECT
    p.product_id,
    ROUND(COALESCE(SUM(p.price * u.units)::NUMERIC / SUM(u.units), 0), 2) AS average_price
FROM
    Prices p
    LEFT JOIN UnitsSold u ON p.product_id = u.product_id
    AND (u.purchase_date BETWEEN p.start_date AND p.end_date)
GROUP BY p.product_id

1068. Product Sales Analysis I

SELECT
    p.product_name,
    s.year,
    s.price
FROM
    Sales s
    JOIN Product p USING (product_id)

1069. Product Sales Analysis II

SELECT
    product_id,
    SUM(quantity) AS total_quantity
FROM Sales
GROUP BY product_id;

1070. Product Sales Analysis III

JOIN

WITH first_years AS (
    SELECT
        product_id,
        MIN(YEAR) AS first_year
    FROM Sales
    GROUP BY product_id
)
SELECT
    s.product_id,
    s.year AS first_year,
    s.quantity,
    s.price
FROM
    Sales s
    JOIN first_years f ON s.product_id = f.product_id
    AND s.year = f.first_year

IN

SELECT
    product_id,
    YEAR AS first_year,
    quantity,
    price
FROM Sales
WHERE
    (product_id, YEAR) IN (
        SELECT
            product_id,
            MIN(YEAR) AS first_year
        FROM Sales
        GROUP BY product_id
    )

2324. Product Sales Analysis IV

WITH cte AS (
    SELECT
        s.user_id,
        s.product_id,
        DENSE_RANK() OVER (PARTITION BY s.user_id ORDER BY SUM(s.quantity * p.price) DESC) AS amount_desc
    FROM
        Sales s
        JOIN Product p ON s.product_id = p.product_id
    GROUP BY
        s.user_id,
        s.product_id
    )
SELECT
    user_id,
    product_id
FROM cte
WHERE amount_desc = 1

2329. Product Sales Analysis V

SELECT
    s.user_id,
    SUM(s.quantity * p.price) AS spending
FROM
    Sales s
    JOIN Product p ON s.product_id = p.product_id
GROUP BY user_id
ORDER BY
    spending DESC,
    user_id ASC

1082. Sales Analysis I

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

1083. Sales Analysis II

GROUP BY

SELECT s.buyer_id
FROM
    Sales s
    JOIN Product p ON s.product_id = p.product_id
GROUP BY s.buyer_id
HAVING
    SUM((product_name = 'iPhone')::INT) = 0
    AND SUM((product_name = 'S8')::INT) > 0
SELECT s.buyer_id
FROM
    Sales s
    JOIN Product p ON s.product_id = p.product_id
GROUP BY s.buyer_id
HAVING
    COUNT(*) FILTER (WHERE product_name = 'iPhone') = 0
    AND COUNT(*) FILTER (WHERE product_name = 'S8') > 0

WHERE

WITH cte AS (
    SELECT
        s.buyer_id,
        p.product_name
    FROM
        Sales s
        JOIN Product p ON s.product_id = p.product_id
)
SELECT DISTINCT buyer_id
FROM cte
WHERE
    product_name = 'S8'
    AND buyer_id NOT IN (
        SELECT buyer_id
        FROM cte
        WHERE product_name = 'iPhone'
    )

1084. Sales Analysis III

Bad Performance:

SELECT
    s.product_id,
    p.product_name
FROM
    Sales s
    JOIN Product p ON s.product_id = p.product_id
GROUP BY
    s.product_id,
    p.product_name
HAVING
    MIN(s.sale_date) >= '2019-01-01'::DATE
    AND MAX(s.sale_date) <= '2019-03-31'::DATE

Better performance

WITH cte AS (
    SELECT s.product_id
    FROM Sales s
    GROUP BY s.product_id
    HAVING
        MIN(s.sale_date) >= '2019-01-01'::DATE
        AND MAX(s.sale_date) < '2019-04-01'::DATE
)
SELECT
    cte.product_id,
    p.product_name
FROM
    cte
    JOIN Product p ON cte.product_id = p.product_id

Best performance

SELECT DISTINCT
    s.product_id,
    p.product_name
FROM Sales s
JOIN Product p ON s.product_id = p.product_id
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sales s2 
    WHERE s2.product_id = s.product_id
      AND (s2.sale_date < '2019-01-01' OR s2.sale_date >= '2019-04-01')
)

WRONG!

SELECT
    s.product_id,
    p.product_name
FROM
    Sales s
    LEFT JOIN Product p ON s.product_id = p.product_id
WHERE s.sale_date BETWEEN '2019-01-01'::DATE AND '2019-03-31'::DATE

1607. Sellers With No Sales

SELECT seller_name
FROM Seller
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Orders
        WHERE
            EXTRACT(YEAR FROM sale_date) = 2020
            AND Seller.seller_id = Orders.seller_id
    )
ORDER BY seller_name

607. Sales Person

SELECT name
FROM SalesPerson s
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            Orders o
            JOIN Company c ON c.name = 'RED'
            AND o.com_id = c.com_id
        WHERE s.sales_id = o.sales_id
    )

1445. Apples & Oranges

Poor solution:

SELECT
    s1.sale_date,
    s1.sold_num - s2.sold_num AS diff
FROM
    Sales s1
    OUTER JOIN Sales s2 ON s1.sale_date = s2.sale_date
    AND s1.fruit = 'apples'
    AND s2.fruit = 'oranges'

Better solutions

WITH cte (sale_date, amount) AS (
    SELECT sale_date, sold_num FROM Sales WHERE fruit = 'apples'
    UNION ALL
    SELECT sale_date, -sold_num FROM Sales WHERE fruit = 'oranges'
)
SELECT
    sale_date,
    SUM(amount) AS diff
FROM cte
GROUP BY sale_date
ORDER BY sale_date

Both SUM and MAX work here

SELECT
    sale_date,
    SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) -
    SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date

10319. Monthly Percentage Difference

WITH cte1 AS (
    SELECT
        TO_CHAR(created_at, 'YYYY-MM') AS year_month,
        SUM(value) AS value
    FROM sf_transactions
    GROUP BY year_month
),
cte2 AS (
    SELECT
        year_month,
        value,
        LAG(value, 1) OVER (ORDER BY year_month) AS prev_value
    FROM cte1
)
SELECT
    year_month,
    ROUND((value / prev_value - 1) * 100, 2) AS revenue_diff_pct
FROM cte2

Brief version (not recommend for interview)

SELECT
    TO_CHAR(created_at, 'YYYY-MM') AS year_month,
    ROUND(
        (SUM(value) / LAG(SUM(value)) OVER w -1) * 100,
        2
    ) AS revenue_diff_pct
FROM sf_transactions
GROUP BY year_month
WINDOW w AS (ORDER BY TO_CHAR(created_at, 'YYYY-MM'))
ORDER BY year_month

User/Customer Analysis

586. Customer Placing the Largest Number of Orders

‘The test cases are generated so that exactly one customer will have placed more orders than any other customer.’

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(order_number) DESC
LIMIT 1

1581. Customer Who Visited but Did Not Make Any Transactions

SELECT
    v.customer_id,
    COUNT(*) AS count_no_trans
FROM
    Visits v
    LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id

If you want to display customer_id with 0 of count_no_trans:

SELECT
    v.customer_id,
    SUM((t.transaction_id IS NULL)::INT) AS count_no_trans
    -- COUNT(*) FILTER(WHERE t.transaction_id IS NULL) AS count_no_trans
FROM
    Visits v
    LEFT JOIN Transactions t ON v.visit_id = t.visit_id
GROUP BY v.customer_id

1511. Customer Order Frequency

WITH cte AS (
    SELECT
        o.customer_id,
        -- DATE_TRUNC('month', o.order_date) AS year_month,
        SUM(p.price * o.quantity) AS amount
    FROM
        Orders o
        JOIN Product p ON o.product_id = p.product_id
    WHERE o.order_date BETWEEN '2020-06-01'::DATE AND '2020-07-31'::DATE
    GROUP BY
        customer_id,
        DATE_TRUNC('month', o.order_date)
)
SELECT
    cte.customer_id,
    c.name
FROM
    cte
    JOIN customers c ON cte.customer_id = c.customer_id
WHERE cte.amount >= 100
GROUP BY
    cte.customer_id,
    c.name
HAVING COUNT(*) >= 2

1407. Top Travellers

SELECT
    u.name,
    COALESCE(SUM(distance), 0) AS travelled_distance
FROM
    Users u
    LEFT JOIN Rides r ON u.id = r.user_id
GROUP BY
    u.id,
    u.name
ORDER BY
    travelled_distance DESC,
    u.name ASC

1398. Customers Who Bought Products A and B but Not C

customer_idcustomer_nameproduct_namepurchased
1DanielAtrue
1DanielBtrue
1DanielCtrue
1DanielDtrue
2DianaAtrue
2DianaBfalse
2DianaCfalse
2DianaDfalse
3ElizabethAtrue
3ElizabethBtrue
3ElizabethCfalse
3ElizabethDtrue
4JhonAfalse
4JhonBfalse
4JhonCtrue
4JhonDfalse

Subquery

SELECT
    customer_id,
    customer_name
FROM Customers
WHERE
    customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'A')
    AND customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'B')
    AND customer_id NOT IN (SELECT customer_id FROM Orders WHERE product_name = 'C')
ORDER BY customer_id

GROUP BY

SELECT
    c.customer_id,
    c.customer_name
FROM
    Customers c
    JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_name
HAVING
    SUM((o.product_name = 'A')::INT) > 0
    AND SUM((o.product_name = 'B')::INT) > 0
    AND SUM((o.product_name = 'C')::INT) = 0
ORDER BY customer_id

1934. Confirmation Rate

SELECT
    COALESCE(s.user_id, c.user_id) AS user_id,
    ROUND(COALESCE(AVG((c.action = 'confirmed')::INT), 0), 2) AS confirmation_rate
FROM
    Signups s
    FULL OUTER JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY 1

1633. Percentage of Users Attended a Contest

This question has a given dominator. Therefore, we can use the easy way.

SELECT
    contest_id,
    ROUND(
        COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM Users) * 100, 2
    ) AS percentage
FROM
    register
GROUP BY
    contest_id
ORDER BY
    percentage DESC,
    contest_id ASC

1141. User Activity for the Past 30 Days I

SELECT
    activity_date AS day,
    COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date between '2019-07-27'::DATE - 29 and '2019-07-27'::DATE
GROUP BY activity_date
ORDER BY day

Market Analysis

1158. Market Analysis I

This one works because LEFT JOIN already have the required number of user_id

SELECT
    u.user_id AS buyer_id,
    u.join_date,
    COUNT(o.order_id) AS orders_in_2019
FROM
    Users u
    LEFT JOIN Orders o ON u.user_id = o.buyer_id
    AND EXTRACT(YEAR FROM o.order_date) = 2019
GROUP BY
    u.user_id,
    u.join_date
ORDER BY u.user_id

Same result in different style

SELECT
    u.user_id AS buyer_id,
    u.join_date,
    COUNT(o.order_id) FILTER (
	    WHERE EXTRACT(YEAR FROM o.order_date) = 2019
	) AS orders_in_2019
FROM
    Users u
    LEFT JOIN Orders o ON u.user_id = o.buyer_id
GROUP BY
    u.user_id,
    u.join_date

Notice we cannot use where clause of WHERE EXTRACT(YEAR FROM o.order_date) = 2019 to filter order made in 2019.

SELECT
    u.user_id AS buyer_id,
    u.join_date,
    -- COALESCE here cannot get any null and convert to 0
    COALESCE(COUNT(o.order_id), 0) AS orders_in_2019
FROM
    Users u
    LEFT JOIN Orders o ON u.user_id = o.buyer_id
WHERE EXTRACT(YEAR FROM o.order_date) = 2019
GROUP BY
    u.user_id,
    u.join_date
ORDER BY u.user_id

Because where cannot get null values

1159. Market Analysis II

WITH cte AS (
    SELECT
        seller_id,
        order_date,
        item_id,
        DENSE_RANK() OVER (PARTITION BY seller_id ORDER BY order_date) AS order_date_asc
    FROM Orders
)
SELECT
    u.user_id AS seller_id,
    CASE
        WHEN u.favorite_brand = i.item_brand THEN 'yes'
        ELSE 'no'
    END AS "2nd_item_fav_brand"
FROM
    Users u
    LEFT JOIN cte c ON u.user_id = c.seller_id
        AND c.order_date_asc = 2
    LEFT JOIN Items i ON s.item_id = i.item_id
ORDER seller_id

Game Play Analysis

511. Game Play Analysis I

SELECT
    player_id,
    MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id

512. Game Play Analysis II

Universal solution

WITH cte AS (
    SELECT
        player_id,
        device_id,
        RANK() OVER (PARTITION BY player_id ORDER BY event_date) AS event_date_asc
    FROM Activity
)
SELECT
    player_id,
    device_id
FROM cte
WHERE event_date_asc = 1

FIRST_VALUE needs to be used with DISTINCT

SELECT DISTINCT
    player_id,
    FIRST_VALUE(device_id) OVER (PARTITION BY player_id ORDER BY event_date) AS device_id
FROM Activity

534. Game Play Analysis III

SELECT
    player_id,
    event_date,
    SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far
FROM Activity
ORDER BY
    player_id,
    event_date

550. Game Play Analysis IV

Hardcoded solution

WITH cte AS (
    SELECT
        player_id,
        -- MIN(event_date) AS day1,
        MIN(event_date) + 1 AS day2
    FROM Activity
    GROUP BY player_id
)
SELECT
    ROUND(COUNT(a.player_id)::NUMERIC / COUNT(c.player_id), 2) AS fraction
FROM
    cte c
    LEFT JOIN Activity a ON c.player_id = a.player_id
    AND c.day2 = a.event_date

1097. Game Play Analysis V

Pretty much the same as 550. Game Play Analysis IV

WITH cte AS (
    SELECT
        player_id,
        MIN(event_date) AS install_dt
    FROM Activity
    GROUP BY player_id
)
SELECT
    c.install_dt,
    COUNT(c.player_id) AS installs,
    ROUND(COUNT(a.player_id)::NUMERIC / COUNT(c.player_id), 2) AS Day1_retention
FROM
    cte c
    LEFT JOIN Activity a ON c.install_dt + 1 = a.event_date
    AND c.player_id = a.player_id
GROUP BY c.install_dt

Order Analysis

1173. Immediate Food Delivery I

SELECT ROUND(AVG((order_date = customer_pref_delivery_date)::INT) * 100, 2) AS immediate_percentage
FROM Delivery

1174. Immediate Food Delivery II

WITH CTE AS (
    SELECT
        customer_id,
        MIN(order_date) AS order_date
    FROM Delivery
    GROUP BY customer_id
)
SELECT ROUND(AVG((c.order_date = d.customer_pref_delivery_date)::INT) * 100, 2) AS immediate_percentage
FROM
    cte c
    JOIN Delivery d ON c.customer_id = d.customer_id
    AND c.order_date = d.order_date

Postgresql Exclusive

WITH cte AS (
    SELECT DISTINCT ON (customer_id)
        customer_id,
        order_date,
        customer_pref_delivery_date
    FROM Delivery
    ORDER BY customer_id, order_date
)
SELECT 
    ROUND(AVG((order_date = customer_pref_delivery_date)::INT) * 100, 2) AS immediate_percentage
FROM cte

2686. Immediate Food Delivery III

SELECT
    order_date,
    ROUND(AVG((order_date = customer_pref_delivery_date)::INT) * 100, 2) AS immediate_percentage
FROM Delivery
GROUP BY order_date
ORDER BY order_date

1549. The Most Recent Orders for Each Product

WITH cte AS (
    SELECT
        p.product_name,
        o.product_id,
        o.order_id,
        o.order_date,
        DENSE_RANK() OVER (PARTITION BY o.product_id ORDER BY o.order_date DESC) AS order_date_desc
    FROM
        Orders o
        JOIN Products p ON o.product_id = p.product_id
)
SELECT
    product_name,
    product_id,
    order_id,
    order_date
FROM cte
WHERE order_date_desc = 1
ORDER BY
    product_name,
    product_id,
    order_id

1596. The Most Frequently Ordered Products for Each Customer

WITH cte AS (
    SELECT
        o.customer_id,
        o.product_id,
        p.product_name,
        DENSE_RANK() OVER (PARTITION BY o.customer_id ORDER BY COUNT(o.order_id) DESC) AS order_count_desc
    FROM
        Orders o
        JOIN Products p ON o.product_id = p.product_id
    GROUP BY
        o.customer_id,
        o.product_id,
        p.product_name
)
SELECT
    customer_id,
    product_id,
    product_name
FROM cte
WHERE order_count_desc = 1
ORDER BY
    product_name,
    product_id,
    customer_id

1532. The Most Recent Three Orders

WITH cte AS (
    SELECT
        c.name AS customer_name,
        c.customer_id,
        o.order_id,
        o.order_date,
        DENSE_RANK() OVER (PARTITION BY c.customer_id ORDER BY o.order_date DESC) AS order_date_desc
    FROM
        Customers c
        JOIN Orders o ON c.customer_id = o.customer_id
)
SELECT
    customer_name,
    customer_id,
    order_id,
    order_date
FROM cte
WHERE order_date_desc <= 3
ORDER BY
    customer_name,
    customer_id,
    order_date DESC

Transactions

1193. Monthly Transactions I

SELECT
    TO_CHAR(trans_date, 'YYYY-MM') AS month,
    country,
    COUNT(id) AS trans_count,
    COUNT(id) FILTER (WHERE "state" = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN "state" = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY month, country
ORDER BY month, country

1205. Monthly Transactions II

WITH all_transactions AS (
    SELECT
        TO_CHAR(trans_date, 'YYYY-MM') AS "month",
        country,
        'approved' AS "state",
        amount
    FROM Transactions t
    WHERE "state" = 'approved'  -- MUST filter approved

    UNION ALL

    SELECT
        TO_CHAR(c.trans_date, 'YYYY-MM') AS "month",
        t.country,
        'chargeback' AS "state",
        t.amount
    FROM
        Chargebacks c
        JOIN Transactions t ON c.trans_id = t.id
)
SELECT
    "month",
    country,
    SUM((state = 'approved')::INT) AS approved_count,
    SUM(CASE WHEN "state" = 'approved' THEN amount ELSE 0 END) AS approved_amount,
    SUM((state = 'chargeback')::INT) AS chargeback_count,
    SUM(CASE WHEN "state" = 'chargeback' THEN amount ELSE 0 END) AS chargeback_amount
FROM all_transactions
GROUP BY
    "month",
    country
ORDER BY
    "month",
    country

1831. Maximum Transaction Each Day

WITH cte AS (
        SELECT
            transaction_id,
            DENSE_RANK() OVER (PARTITION BY day::DATE ORDER BY amount DESC) AS amount_desc
        FROM Transactions
    )
SELECT transaction_id
FROM cte
WHERE amount_desc = 1
ORDER BY transaction_id

Social Network Analysis

1683. Invalid Tweets

SELECT tweet_id
FROM Tweets
WHERE LENGTH(CONTENT) > 15

1148. Article Views I

SELECT DISTINCT author_id AS id
FROM VIEWS
WHERE author_id = viewer_id
ORDER BY id

1149. Article Views II

SELECT DISTINCT viewer_id AS id
FROM VIEWS
GROUP BY
    viewer_id,
    view_date
HAVING COUNT(DISTINCT article_id) >= 2

1501. Countries You Can Safely Invest In

UNION ALL

WITH cte (call_id, duration) AS (
    SELECT caller_id, duration FROM Calls
    UNION ALL
    SELECT callee_id, duration FROM Calls
)
SELECT co.name AS country
FROM
    cte c
    JOIN Person p ON c.call_id = p.id
    JOIN Country co ON SUBSTRING(p.phone_number, 1, 3) = co.country_code
GROUP BY co.name
HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls)

JOIN ON .. IN

SELECT co.name AS country
FROM
    Calls ca
    JOIN Person p ON p.id IN (ca.caller_id, ca.callee_id)
    JOIN Country co ON SUBSTRING(p.phone_number, 1, 3) = co.country_code
GROUP BY co.name
HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls)

Infra Analysis

1661. Average Time of Process per Machine

SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp)::NUMERIC, 3) AS processing_time
FROM
    Activity a1
    JOIN Activity a2 ON a1.machine_id = a2.machine_id
    AND a1.process_id = a2.process_id
    AND a1.activity_type = 'start'
    AND a2.activity_type = 'end'
GROUP BY a1.machine_id
ORDER BY machine_id

Human Resource

More Human Resource related questions see LeetCode SQL RANK

1731. The Number of Employees Which Report to Each Employee

SELECT
    m.employee_id,
    m.name,
    COUNT(e.employee_id) AS reports_count,
    ROUND(AVG(e.age), 0) AS average_age
FROM
    Employees m
    JOIN Employees e ON m.employee_id = e.reports_to
GROUP BY
    m.employee_id,
    m.name
ORDER BY employee_id

1789. Primary Department for Each Employee

WITH cte AS (
    SELECT
        employee_id,
        department_id
    FROM Employee
    WHERE primary_flag = 'Y'
)
SELECT *
FROM cte
UNION
SELECT
    employee_id,
    department_id
FROM Employee
WHERE employee_id NOT IN (SELECT employee_id FROM cte)
ORDER BY employee_id

Postgresql Exclusive Method:

DISTINCT ON is a PostgreSQL-specific extension (not standard SQL) that’s incredibly useful but often overlooked.

How It Works

DISTINCT ON keeps only the first row for each unique combination of the specified columns, based on the ORDER BY clause.

Key rule: The DISTINCT ON columns must match the leftmost columns in ORDER BY.

What happens:

  1. Groups rows by employee_id
  2. Within each group, sorts by primary_flag desc (‘Y’ comes before ‘N’)
  3. Takes the first row from each group
  4. Result: Primary department if it exists, otherwise the only department
SELECT DISTINCT ON (employee_id)
	employee_id,
    department_id
FROM Employee
ORDER BY
    employee_id,
    primary_flag DESC

1978. Employees Whose Manager Left the Company

SELECT e.employee_id
FROM
    Employees e
    LEFT JOIN Employees m ON e.manager_id = m.employee_id
WHERE
    e.salary < 30000
    AND e.manager_id IS NOT NULL
    AND m.employee_id IS NULL
ORDER BY employee_id

Bank Analysis

2066. Account Balance

SELECT
    account_id,
    day,
    SUM(
        CASE
            WHEN type = 'Deposit' THEN amount
            ELSE - amount
        END
    ) OVER ( PARTITION BY account_id ORDER BY DAY) AS balance
FROM Transactions
ORDER BY
    account_id,
    day

1555. Bank Account Summary

WITH cte AS (
    SELECT user_id, credit FROM Users
    UNION ALL
    SELECT paid_to, amount FROM Transactions
    UNION ALL
    SELECT paid_by, -amount FROM Transactions
)
SELECT
    u.user_id,
    u.user_name,
    SUM(c.credit) AS credit,
    CASE
        WHEN SUM(c.credit) < 0 THEN 'Yes'
        ELSE 'No'
    END AS credit_limit_breached
FROM
    Users u
    JOIN cte c ON u.user_id = c.user_id
GROUP BY
    u.user_id,
    u.user_name
ORDER BY u.user_id

1587. Bank Account Summary II

SELECT
    u.name,
    SUM(amount) AS balance
FROM
    Transactions t
    LEFT JOIN Users u ON t.account = u.account
GROUP BY
    t.account,
    u.name
HAVING SUM(amount) > 10000
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 - Consecutive Records
Next Post
LeetCode SQL