Just Grab the Latest Record… Said Everyone Before Debugging It

How a “simple” SQL task may have depth.

Brie Larson in Captain Marvel (2019). Photograph: Marvel Studios(left table), Pink Batman logo(Right table), filter -> visualized with GenAI (Result table)

My vision, empowered by GenAI to visualize what I mean with the help of Captain Marvel-style. Because why not? We’re talking about powerful queries here. And I always thought Batman would benefit from a feminine touch.

🎯 The Task: “Find the last (most recent) platform each user logged in with”

user_logins SQL input Table with 3 columns: user_id, date, platform

Expected Output: one row per user, showing their user_id and last_platform. Easy, right?

Turns Out MAX(date) Isn’t the End of the Story

Here’s how one might start constructing a query:

-- ❌ Incorrect Output 1
SELECT 
  user_id, 
  MAX(date) as latest_date, 
  platform
FROM user_logins
GROUP BY user_id, platform;

Query output table (Incorrect Output 1)

But this returns multiple rows per user — one for each platform they’ve used — along with the latest_date for each platform. Not quite what I wanted.

😅 HAVING Second Thoughts

Trying to be clever, I reached for HAVING

-- ❌ Still Incorrect
SELECT 
  user_id,
  platform,
  MAX(date)
FROM user_logins
GROUP BY user_id, platform
HAVING MAX(date);

Query output table (Incorrect Output 2)

Having looks fancy, but still wrong. It’s only useful if I just want the latest login date per user per platform. A single platform tied to that latest date is still elusive.

💡 TL;DR

• HAVING works only on aggregated values. I CAN’T aggregate the entire ROW even if MAX(date) was found. MAX(date) returns one date, not the row that had that date. (Think of a cell V.S. a row).
• If I want entire rows where a value like date is max, I need to pair the aggregation with a filter like JOIN or ROW_NUMBER().

✅ A classic Subquery approach inside the JOIN clause

These 2 versions do work and return exactly what I need:

-- V1. Subquery is in JOIN clause (table as A&B)
SELECT B.*
FROM user_logins B
JOIN (
    SELECT user_id, MAX(date) AS latest_date
    FROM user_logins
    GROUP BY user_id
) A
  ON A.user_id = B.user_id
 AND A.latest_date = B.date;

-- the same output
-- V2. Subquery is in FROM clause (table as L&R)
SELECT 
    L.*, R.platform
FROM
    (SELECT 
        user_id, MAX(date) AS latest_date
    FROM
        user_logins
    GROUP BY user_id) L
        JOIN
    user_logins R ON L.user_id = R.user_id
        AND L.latest_date = R.date;

Query output table (Correct)

Why it works:

• The subquery groups by user_id and finds their most recent date.
• Then join back to the full table to get the platform for now specific rows.

This “feels” solid. Clear intent, and efficient with proper indexing. But… could I get away without the subquery?

🧪 The Self-Join Trick (O(n²) pitfall)

Here’s a self-join version that uses no subqueries

SELECT L.*
FROM user_logins L
LEFT JOIN user_logins R 
  ON L.user_id = R.user_id
 AND L.date < R.date
WHERE R.user_id IS NULL;

💡 Why it works:

I compare each left (L) date row to all future logins on the right (R) from the same user. If there’s no such later login (R.date > L.date), that means the currentL date is the latest. Yes, here is an O(n²) comparisons pitfall.

It’s a smart trick that uses NULL as a “signal”: no later logins = this row is the latest.

📸 Visual Learners, Assemble! V2 of the outfit

Captain Marvel was dressed by a ‘Right Pink Batman’ stylist. (A face touch was unnecessary, but oh well, if only we’d have control over our AI)

🧠 Bonus Round: ROW_NUMBER()

Another clean and modern solution — Partition (popular in Spark)

SELECT user_id, date, platform
FROM (
    SELECT 
       *,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
    FROM user_logins
) temp_must_have
WHERE rn = 1;

temp_must_have — inner query result of a window function

5*! Why I love this one:

• Uses a window function to number rows by recency.
• rn = 1 gives me the most recent login per user — one row per user.
• Scales beautifully and avoids the n² pitfalls.

🚀 TL;DR

• MAX(date) alone doesn't give me the full row — just the date value.
• To get the full context (including platform), I need to:
• → Join back on that max date, or
• → Use a n² left self-join, or
• → Go with ROW_NUMBER().

Each option has trade-offs. But isn’t that what makes SQL so much fun?

This rabbit hole started as a “one-liner” task and ended up being a small lesson in nuance. It’s humbling — and kind of exciting — to see how deep “just get the latest login” can go.

⚙️ Performance Notes

Method — | — Pros — | — Cons

JOIN + MAX()| Fast on indexed data (e.g., user_id, date) | Requires a derived subquery

LEFT JOIN Self | No subqueries, very readable | Can be slow on large datasets, O(n²) comparison

ROW_NUMBER()| Very scalable, no duplicates | Slightly more advanced syntax

🔧 Optional: Add an index for better performance

CREATE INDEX idx_user_date ON user_logins(user_id, date);

And just as icing on a cake:

SQL Joins on Superheroes tables (credit to David Cupp)

Table1 — is your star-studded cast — actors in their natural habitat, maybe sipping lattes or doing headshots. Table2 is the superhero squad — muscled-up and masked. When you run an INNER JOIN, that’s where movie magic happens: actors fully suited up as their iconic characters —Iron Man in his armor, Batman in dramatically backlit. Add a LEFT JOIN, and you also get those behind-the-scenes shots — actors without capes, wondering where their stunt doubles went, maybe confused why everyone else is in spandex. It’s SQL meets Comic-Con, and everyone gets a cameo. Now toss in a FULL OUTER JOIN, and things get wild: not only do you get the suited-up stars and the casual candids, but you also see the lonely superheroes who haven’t been cast yet — and actors waiting on the call from Marvel. It’s like an SQL-powered red carpet meets multiverse mashup!

Update (2025–04–12): I found a similar problem on Leetcode “512. Game Play Analysis II”, have a go at it.

Update (2025–04–22): Hey, if you are like me root for the idea of complete makeover of outfits of Disney princesses to Batman-like:

[embed]Pinterest в 2025 г | Принцессы, Сказочное фэнтези, Комиксы 04.03.2025 - Автор пина:Dayana. Находите и прикалывайте свои пины в Pinterest!pin.it