Most Asked SQL Window Function Question — Second Latest Record Query Explained
Learn how to write an SQL query to get the second latest record or the only record per user using powerful window functions like ROW_NUMBER() and COUNT(). Perfect for interview prep and data analyst practice!
Medium LINK - / sql-problem-recommendation-system-based-on...
🧩 SQL Table Setup:
---------------------------------------------------------------------------------------------
create table UserActivity
(
username varchar(20) ,
activity varchar(20),
startDate Date ,
endDate Date
);
insert into UserActivity values
('Alice','Travel','2020-02-12','2020-02-20')
,('Alice','Dancing','2020-02-21','2020-02-23')
,('Alice','Travel','2020-02-24','2020-02-28')
,('Bob','Travel','2020-02-11','2020-02-18');
---------------------------------------------------------------------------------------------
📂 Additional Resources:
🔗 Dataset : https://github.com/shubhamringole/SQL...
🔗 My Repository: https://github.com/shubhamringole/SQL...
🔗 Related Blog: / singole
📺 Complete SQL Playlist:
🎥 Learn SQL Complete Guide
This playlist takes you from beginner to advanced, covering:
1. Introduction to SQL Learn to Query Databases Like a Pro
• Introduction to SQL Learn to Query Databas...
2.How to Install MySQL Workbench - A step-by-step guide on downloading, installing, and setting up MySQL Workbench.
• How to Install MYSQL Work Bench | Lesson 2...
3.SQL Fundamentals: Data Types, Keys, and Constraints
• SQL Essentials: Understanding Data Types a...
4.SQL Table Operations : Create, Delete, Drop, Truncate, and Alter | Lesson 4 | Introduction to SQL
• SQL Table Operations : Create, Delete, Dro...
5.SELECT Statement | WHERE Clause with Examples and Operators in SQL |Lesson 5 | Introduction to SQL
• SELECT Statement | WHERE Clause with Examp...
6.How to Import an Excel CSV File into MySQL A Step by Step Guide| Lesson 4 | Introduction to SQL
• How to Import an Excel CSV File into MySQL...
7.Mastering SQL GROUP BY and HAVING Clauses Explained
• Mastering GROUP BY and HAVING Clauses | Le...
8.Mastering SQL JOINs: Complete Guide with Examples | INNER & LEFT JOIN Lesson 8 | Introduction to SQL
• SQL JOIN | INNER & LEFT JOIN Lesson 8 | I...
9. Right Join, Cross Join, and Outer Join Explained | Lesson 9 | Introduction to SQL
• SQL Join Tutorial: Right, Cross, Outer | L...
10.SQL Subqueries Explained: Unlock Advanced Querying Techniques
• Master SQL with Secret Subquery Tips
11.SQL CASE Statement Explained: Boost Your Query Skills! | Lesson 11
• SQL CASE Statement Explained: Boost Your Q...
12.Master SQL Window Functions: RANK & DENSE RANK
• Master SQL Window Functions: RANK & DENSE ...
13. The Magic of Wildcards in SQL: Unlocking Pattern Matching Techniques
• Video
14.SQL SELF JOIN | UNION & UNION ALL in SQL | Lesson 14
• The Power of SQL Self-Join & Union | Lesso...
🕒 Timecodes:
00:00 Intro
00:25 Introduction to problem
02:27 Understanding the Logic
05:50 SQL Query Walkthrough
06:40 Final Thoughts
📚 More Learning:
🧠 Medium Blog: Understanding SQL CTEs
📸 Instagram: @analystmind
🌐 Medium Profile: @singole
💬 Stay Connected:
If you enjoyed this SQL problem, make sure to Like 👍, Comment 💬, and Subscribe 🔔 for more SQL practice questions and real-world use cases.
#sql #sqlproblem #mysql #dataanalysis #recommendationsystem #joins #ecommerce #sqlproject #analystmind #sqlpractice