???? SQL One-Liner: Get Latest Transaction Per User

Description:
This script solves one of the most searched SQL interview problems: finding the latest transaction per user.

Long Way (Correlated Subquery):

Uses a subquery with MAX(transaction_date) to fetch the most recent transaction.

Simple but not efficient for big tables.

Shortcut One-Liner (Window Function):

Uses ROW_NUMBER() with PARTITION BY user_id and ORDER BY transaction_date DESC.

Efficient, elegant, and widely used in analytics and reporting.

???? Real-world use cases include:

Latest order per customer.

Most recent login per user.

Most recent status update per device.

Queries:

✅ Long Way (Correlated Subquery):

SELECT t1.user_id, t1.transaction_id, t1.amount, t1.transaction_date
FROM transactions t1
WHERE t1.transaction_date = (
SELECT MAX(t2.transaction_date)
FROM transactions t2
WHERE t2.user_id = t1.user_id
);


Explanation:

For each transaction t1, we look up the maximum transaction date for the same user using a correlated subquery.

Only the latest transaction per user will match.

This works, but the correlated subquery can be slow for large datasets.

✅ Shortcut One-Liner (Window Function):

SELECT user_id, transaction_id, amount, transaction_date
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) AS rn
FROM transactions
) t
WHERE rn = 1;


Explanation:

ROW_NUMBER() assigns a unique sequential number for each transaction within a user, ordered by transaction_date DESC.

rn = 1 ensures we only take the latest record per user.

Faster, easier to read, and scalable for millions of rows.