SQL Interview Questions – Maximum Transaction Each Day

Problem Description –

Write an SQL query to report the IDs of the transactions with the maximum amount on their respective day. If in one day there are multiple such transactions, return all of them.

Return the result table ordered by transaction_id in ascending order.

The query result format is in the following example.

Problem Link – Maximum transaction

Difficulty Level – Medium

Solution –

SELECT
    transaction_id
FROM (
SELECT
    transaction_id,
    day,
    amount,
    DENSE_RANK() OVER(PARTITION BY date(day) ORDER BY amount DESC) as rnk
FROM Transactions
    ) x
WHERE rnk = 1
ORDER BY 1