Calculating Cumulative Sums with SQL Window Functions

🚀 Day 35 – Top 50 SQL Questions Today’s problem: Last Person to Fit in the Bus 🔍 Objective: Determine the last person who can board the bus without exceeding the total weight limit. 💡 Approach: Calculated cumulative weight using a window function Maintained boarding sequence using the turn column Filtered entries within the allowed weight limit Selected the last eligible person based on the highest valid cumulative weight 📈 Key Insight: Window functions are essential for solving sequential and running total problems in SQL. #SQL #DataAnalytics #LeetCode #ProblemSolving #Day35

🧩 SQL Practice: Finding User Purchases Solved a good SQL problem from StrataScratch: 👉 Find users who made a second purchase within 1–7 days of their first purchase (excluding same-day purchases). 💡 Approach: Get each user’s first purchase date Join it back with all transactions Calculate the day difference Filter users who returned within 1–7 days 📌 Concepts used: CTE (WITH) | Aggregation (MIN) | Joins | Date arithmetic 🔗 Problem link: https://lnkd.in/g9EyimWx Simple problem, but a good way to practice real-world SQL logic. #SQL #DataAnalytics #Practice

𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐂𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞 | 𝐋𝐞𝐯𝐞𝐥: 𝐀𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐂𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞: Convert each row into multiple rows based on value Do it using pure SQL Avoid using loops (think set-based 💡) 𝐖𝐡𝐚𝐭 𝐭𝐡𝐢𝐬 𝐭𝐞𝐬𝐭𝐬: Recursive thinking Set-based SQL approach Understanding of row expansion Advanced SQL concepts (CTE / generate_series / window tricks) Drop your solution in the comments! P.S - Get create and insert statement in the first comment.

An interesting problem posted by Tyagi, which can be solved by using Recursive logic. Do check the comment to see my simple solution.

Day 26/90 — SQL Series | Week 4 "My WHERE clause is not filtering dates correctly." "My SUM is returning NULL instead of a number." 9 times out of 10 — it's a data type mismatch. CAST and CONVERT fix it. CAST('250' AS INT) → turns text into number so you can do math CAST('2024-01-15' AS DATE) → turns text into date so filters work CAST(order_id AS VARCHAR) → turns number into text for concatenation Rule: use CAST (works everywhere). Use CONVERT only when you need date formatting in SQL Server. #SQL #CastConvert #DataAnalytics #LearnSQL #DataAnalyst

🛑 Stop dealing with negative numbers in your SQL reports! Need to find the difference between two values regardless of which one is higher? The ABS() function is your best friend. Whether you're calculating: ✅ Price discrepancies ✅ Absolute variance ✅ Distance metrics ... ABS() turns those negative numbers positive in a snap! ⚡  I just dropped a new video covering how to use it with practical examples: #SQL #DataAnalytics #DataScience #SQLTips #LearningSQL

I rewrote a SQL query 3 times before I finally understood what it was actually doing. It was supposed to calculate average goals per league (Aug, 2013/14 season) but I kept getting stuck in nested subqueries and unnecessary id IN (...) filters. Some key lessons I learned: → 𝗕𝗿𝗲𝗮𝗸 𝗽𝗿𝗼𝗯𝗹𝗲𝗺𝘀 𝗶𝗻𝘁𝗼 𝗹𝗮𝘆𝗲𝗿𝘀 Filter → Transform → Join → Aggregate → 𝗖𝗧𝗘𝘀 𝗮𝗿𝗲 𝗳𝗼𝗿 𝗰𝗹𝗮𝗿𝗶𝘁𝘆, 𝗻𝗼𝘁 𝗰𝗼𝗺𝗽𝗹𝗲𝘅𝗶𝘁𝘆 Instead of messy nested queries, structure the logic so you can understand it later. → 𝗡𝗼𝘁 𝗲𝘃𝗲𝗿𝘆 𝘀𝘂𝗯𝗾𝘂𝗲𝗿𝘆 𝗶𝘀 𝗻𝗲𝗰𝗲𝘀𝘀𝗮𝗿𝘆 Why filter the same table with a subquery when a simple WHERE season = ... AND EXTRACT(MONTH...) = ... does the job? → 𝗔𝗹𝘄𝗮𝘆𝘀 𝗸𝗲𝗲𝗽 𝘁𝗵𝗲 𝗷𝗼𝗶𝗻 𝗸𝗲𝘆 If your CTE drops the linking column, the whole query breaks later. → 𝗔𝗩𝗚 𝗶𝘀 𝗷𝘂𝘀𝘁 𝗦𝗨𝗠/𝗖𝗢𝗨𝗡𝗧 Once you see that, debugging becomes much easier. Don’t just write queries. Design the data flow. #DataAnalytics #SQL 

If you're wondering how or where to start your SQL journey, these are some channels a that can take you from absolute #beginner to expert level. I added a link in the comments section that has online platforms and #books to support you. And they are all absolutely free! Happy new week!! #SQL #sqlbeginner #DataAnalyticsJourney #nma_writes

Created a BookMyShow POC, a small assignment given by Hitesh Choudhary. The assignment was to build something using SQL so that, if multiple users try to book a single seat, it would smartly handle the situation and allow only one user to book that seat. To do that, I use a SQL transaction and a special keyword 'for update'. This for update keyword is magical. What it does is that when a user starts a transaction for some value on a particular DB row, until that user commits or rolls back the transaction, no one can actually make another transaction successfully; it goes into a halt state. Docs https://lnkd.in/gbA_h2aN

No matter how long you’ve worked with SQL, you always end up revisiting the basics. Joins, CTEs, window functions, optimization — fundamentals never stop being useful. Sometimes the best learning is simply going back to the core. 🚀 What’s one SQL concept you use all the time? #SQL #DataAnalytics #ContinuousLearning #LearningJourney #TechCommunity