LC 1532 & LC 1831 & LC 1949

The following advanced-level SQL problems are about ‘Function’.

LeetCode 1532

The Most Recent Three Orders (Medium) [link]

SELECT c.name AS customer_name, t.customer_id, t.order_id, t.order_date
FROM(
    SELECT *,
        dense_rank() OVER(PARTITION BY customer_id ORDER BY order_date DESC) as rnk
    FROM Orders
) t
LEFT JOIN Customers c
ON t.customer_id = c.customer_id
WHERE t.rnk <= 3
ORDER BY c.name, t.customer_id, t.order_date DESC;

LeetCode 1831

Maximum Transaction Each Day (Medium) [link]

Using WHERE IN

SELECT transaction_id
FROM Transactions
WHERE (DATE(day), amount) IN(
    SELECT DATE(day), MAX(amount) AS amount
    FROM Transactions
    GROUP BY DATE(day)
) 
ORDER BY transaction_id;

Using window function

SELECT transaction_id
FROM(
    SELECT *,
        dense_rank() OVER(PARTITION BY DATE(day) ORDER BY amount DESC) as rk
    FROM Transactions
) t
WHERE rk = 1
ORDER BY transaction_id;

LeetCode 1949

Strong Friendship (Medium) [link]

User1 and user2 have to be friends of one another. The number of their common friends has to be no less than 3.

Using LEFT JOIN.

# first, build a friendship list
with a as (
    SELECT user1_id AS user_id,
        user2_id AS friend_id     
    FROM friendship 
    UNION 
    SELECT user2_id, user1_id 
    FROM friendship
)

SELECT f.user1_id, f.user2_id,
    COUNT(*) AS common_friend
FROM friendship f 
JOIN a a1 
ON f.user1_id = a1.user_id # find friends of user1
JOIN a a2 
ON f.user2_id = a2.user_id # find friends of user2
    AND a2.friend_id = a1.friend_id # friends have to be common
GROUP BY f.user1_id, f.user2_id
HAVING COUNT(*) >= 3;

Using WHERE IN

with a as (
    SELECT user1_id AS user_id,
        user2_id AS friend_id     
    FROM friendship 
    UNION 
    SELECT user2_id, user1_id 
    FROM friendship
)

SELECT f.user1_id, f.user2_id,
    COUNT(*) AS common_friend
FROM friendship f, a a1, a a2
WHERE f.user1_id = a1.user_id AND
    f.user2_id = a2.user_id AND
    a2.friend_id = a1.friend_id 
GROUP BY f.user1_id, f.user2_id
HAVING COUNT(*) >= 3;