SQL Self Join for Date Comparison

🧠 SQL Problem That Looks Simple… But Isn’t 👇 There’s a queue of people waiting to board a bus with a weight limit of 1000 kg. The goal? 👉 Find the last person who can board without exceeding the limit. At first glance, it feels like a simple filter problem. But the moment you think deeper, you realize: 👉 This is about running totals (cumulative sum) So instead of looping row by row, I used a window function, 💡 Key Insight: Compute cumulative weight using SUM() OVER (ORDER BY turn) Filter valid rows (<= 1000) Pick the last feasible person 🔥 What I learned: Window functions can replace complex loops Many “greedy” problems translate neatly into SQL Thinking in terms of running aggregates is powerful Curious — how would you approach this without window functions? 🤔 #SQL #DataAnalytics #LearningInPublic #DataScience #LeetCode

Day 9/50 – #SQLChallenge 🚀 Solved “Rising Temperature” problem on LeetCode. ✅ Approach: Used SELF JOIN to compare rows within the same table ✅ Key Concept: Comparing consecutive records using date difference 💡 Advanced Insight: Since SQL doesn’t have a direct way to access the previous row, a SELF JOIN helps simulate this behavior by pairing each record with its previous day. This pattern is widely used in time-series data analysis. 🔍 Takeaway: Understanding how to compare rows across time is crucial for real-world scenarios like tracking growth, trends, and performance metrics. Consistency is compounding 📈 #SQL #LeetCode #Database #Joins #CodingChallenge #ProblemSolving #LearningInPublic #DeveloperJourney

🚀 Day 40 of My SQL Learning Journey Today I worked on a SQL problem involving aggregation and string manipulation 🔥 🔹 Problem: Group sold products by date and list them in sorted order 🔗 Problem Link: https://lnkd.in/gp6qVmNt 🔹 Solution: SELECT sell_date, COUNT(DISTINCT product) AS num_sold, GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products FROM Activities GROUP BY sell_date; 🔹 Key Learning: Using COUNT(DISTINCT) for unique values Combining rows using GROUP_CONCAT() Sorting values inside aggregation 💡 SQL can generate clean reports directly from raw data! Consistency continues 🚀 #SQL #LeetCode #90DaysOfCode #DataAnalytics #CodingJourney

🚀 Day 28 of My SQL Learning Journey Today I explored Window Functions — one of the most powerful concepts in SQL! 🔹 Problem: Rank scores without gaps (same score → same rank) 🔗 Problem Link: https://lnkd.in/gsWJbr4m 🔹 Solution: SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM Scores; 🔹 Key Learning: - Introduction to Window Functions - Difference between "RANK()" and "DENSE_RANK()" - Handling duplicates in ranking problems 💡 Window functions are heavily used in real-world analytics and interviews! Slowly moving from basic queries → advanced SQL 🚀 #SQL #WindowFunctions #LeetCode #90DaysOfCode

❄️ LeetCode Daily Challenge 📅 Day 19 of 50 Days SQL Challenge Continuing my SQL journey with another Hard-level SQL problem focused on session analytics and behavioral anomaly detection. 📌 Problem: Find Zombie Sessions 🔗 Problem Link: https://lnkd.in/gDEr9VEq 💡 Problem Summary: We need to identify zombie sessions — sessions where users appear active but show abnormal behavior patterns. A session qualifies as a zombie session if it satisfies ALL conditions: ✔ Session duration > 30 minutes ✔ At least 5 scroll events ✔ Click-to-scroll ratio < 0.20 ✔ No purchases during the session Finally, return the result ordered by: scroll_count (DESC), session_id (ASC) The idea was to aggregate event-level data into session-level metrics, then filter sessions based on behavioral thresholds. Another Hard SQL challenge completed — consistency continues 💪 Let’s grow one query at a time 🚀 #LeetCode #SQL #DataEngineering #Analytics #Database #WindowFunctions #DailyPractice #LearningInPublic #50DaysChallenge

❄️ LeetCode Daily Challenge 📅 Day 34 of 50 Days SQL Challenge Today’s challenge was all about analyzing performance improvement over time — a very practical scenario in real-world analytics. 📌 Problem: Find Drivers with Improved Fuel Efficiency 🔗Problem Link: https://lnkd.in/gWqPXFQN 💡 Problem Breakdown: Identify drivers whose fuel efficiency improved: ✔ Calculate efficiency per trip (distance / fuel) ✔ Compare average efficiency between first half (Jan–Jun) and second half (Jul–Dec) ✔ Include only drivers with trips in both halves ✔ Compute improvement = second_half_avg - first_half_avg 34 days of consistent SQL practice completed ✅ Daily practice is turning concepts into intuition 💪 Let’s grow one query at a time 🚀 Drop your approach below 👇 #LeetCode #SQL #DataEngineering #Analytics #PerformanceAnalysis #SQLPractice #LearningInPublic #50DaysChallenge #DataAnalytics

❄️ LeetCode Daily Challenge 📅 Day 30 of 50 Days SQL Challenge Today’s challenge was a deep dive into subscription analytics and churn prediction — something very relevant to real-world data scenarios. 📌 Problem: Find Churn Risk Customers 🔗Problem Link: https://lnkd.in/dw95yEPj 💡 Problem Breakdown: Identify churn risk customers who: ✔ Currently have an active subscription (last event is not cancel) ✔ Have at least one downgrade in their history ✔ Current plan revenue < 50% of their historical maximum ✔ Have been subscribed for at least 60 days 30 days of consistent SQL practice completed ✅ Daily practice is turning concepts into intuition 💪 Let’s grow one query at a time 🚀 Drop your approach below 👇 #LeetCode #SQL #DataEngineering #Analytics #ChurnAnalysis #CustomerRetention #SQLPractice #WindowFunctions #LearningInPublic #50DaysChallenge

SQL Window Functions finally clicked… when I stopped thinking like a developer 👇 Think of a school assembly: 👉 Students are divided class-wise → PARTITION BY 👉 Inside each class, they stand by height → ORDER BY 👉 Then each student gets a position → ROW_NUMBER() That’s it. No rows removed. No data collapsed. Just smarter calculations on top of your existing data. If you’re struggling with window functions, stop memorizing syntax… Start visualizing real-world scenarios. #SQL #DataAnalytics #LearnSQL #DataScience #TechLearning What’s the concept in SQL that took you the longest to understand? 👇

🚀 Day 18 of the 30-Day SQL Challenge Today's Topic : Self Join A Self Join is used when a table needs to be joined with itself to find relationships within the same data. 💡 Example: Linking employees with their managers using the same table. 📌 When to use? • Find hierarchical relationships • Compare rows in the same table • Analyze structured data 🔥 Simple concept, powerful use in real-world queries. Consistency check ✅ Day 18/30 — keep going! #SQL #DataAnalytics #Learning #Coding #DataScience

❄️ LeetCode Daily Challenge 📅 Day 28 of 50 Days SQL Challenge Continuing my SQL consistency journey with a Medium-level SQL problem focused on user behavior analysis and aggregation. 📌 Problem: Find Emotionally Consistent Users 🔗 Problem Link: https://lnkd.in/gwAXyQGJ 💡 Problem Summary: Identify users who are emotionally consistent based on their reactions: ✔ At least 5 reactions ✔ At least 60% of reactions are the same type ✔ Return dominant reaction + ratio 28 days of consistent SQL practice completed ✅ Consistency is turning into clarity 💪 Let’s grow one query at a time 🚀 #LeetCode #SQL #DataEngineering #Analytics #UserBehavior #DataAnalysis #WindowFunctions #DailyPractice #LearningInPublic #50DaysChallenge