SQL Aggregation and String Manipulation

🚀 Day 44 of My SQL Learning Journey Today I solved a SQL problem involving aggregation and distinct counting 🔥 🔹 Problem: Find number of unique leads and partners per day 🔗 Problem Link: https://lnkd.in/gXnubrbd 🔹 Solution: SELECT date_id, make_name, COUNT(DISTINCT lead_id) AS unique_leads, COUNT(DISTINCT partner_id) AS unique_partners FROM DailySales GROUP BY date_id, make_name; 🔹 Key Learning: Using multiple COUNT(DISTINCT) Grouping by multiple columns Real-world reporting queries 💡 SQL helps transform raw data into meaningful insights! Consistency continues 🚀 #SQL #LeetCode #90DaysOfCode #CodingJourney #DataAnalytics

🚀 Day 42/100 – LeetCode SQL Practice ✅ Problem Solved: Average Time of Process per Machine 💡 My Approach: Each process has a start and end timestamp I joined the table with itself to match: 👉 start row with corresponding end row (same machine_id & process_id) Then calculated: 👉 Time = end - start Finally: 👉 Took average time per machine using AVG() 👉 Used ROUND(..., 3) to format output to 3 decimal places 🧠 What I Learned Today: How to use SELF JOIN in SQL Understanding how to pair related rows (start & end) Using aggregate functions like AVG() Importance of data formatting using ROUND Writing clean queries with GROUP BY 📈 Key Takeaway: “Break the problem → pair related data → compute → aggregate.” #Day42 #100DaysOfCode #LeetCodeSQL #SQL #DataAnalytics #CodingJourney #ProblemSolving

SQL is the backbone of data work, but even the pros forget the specific order of a WINDOW FUNCTION or the difference between UNION and UNION ALL sometimes. I put together this comprehensive SQL Cheat Sheet to keep all the essentials in one place—from basic DML to advanced CTEs and Triggers. ✅ What’s inside: Joins & Set Operations Window Functions & Aggregations Date/Time formatting Transaction control Save this post 📌 so you can find it during your next project, and Tag a friend who’s currently learning SQL! #SQL #DataScience #DataAnalytics #Coding #Database #ProgrammingTips

🚀 Day 2 of My SQL Learning Journey Today I learned about the WHERE clause in SQL. 👉 WHERE is used to filter data based on specific conditions. Basic syntax: SELECT column_name FROM table_name WHERE condition; ✔ Helps in retrieving only the required data ✔ Can be used with operators like =, >, <, AND, OR 💡 Learning WHERE made me realize how powerful SQL is when working with large datasets. Excited to keep improving! 🔥 Next: INSERT statement 👀 #SQL #LearningJourney #Beginner #DataAnalytics #Day2 #LearnInPublic

🚀 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

🚀 Day 41 of My SQL Learning Journey Today I solved a SQL problem involving self joins and missing relationships 🔥 🔹 Problem: Find employees whose managers are no longer in the company 🔗 Problem Link: https://lnkd.in/gwXAzFJm 🔹 Solution: SELECT e1.employee_id FROM Employees e1 LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id WHERE e1.manager_id IS NOT NULL AND e2.employee_id IS NULL; 🔹 Key Learning: Using self joins to compare records Handling missing relationships using LEFT JOIN Detecting NULL values effectively 💡 SQL helps uncover hidden data relationships! Consistency continues 🚀 #SQL #LeetCode #90DaysOfCode #CodingJourney #ProblemSolving

🚀 Day 52 of My SQL Learning Journey Today I solved a SQL problem based on finding maximum frequency 🔥 🔹 Problem: Find the customer who placed the highest number of orders 🔗 Problem Link: https://lnkd.in/gMVBUtxY 🔹 Solution: SELECT customer_number FROM Orders GROUP BY customer_number ORDER BY COUNT(*) DESC LIMIT 1; 🔹 Key Learning: Using GROUP BY to group data Counting occurrences using COUNT(*) Finding maximum using ORDER BY DESC 💡 Frequency-based queries are very common in real-world analytics! Consistency continues 🚀 #SQL #LeetCode #90DaysOfCode #CodingJourney #DataAnalytics

✅ Solved a SQL problem on LeetCode — Day 49 of my SQL Journey 💪 Consistency isn’t always visible… But patterns reveal it over time 📈 Today’s problem was about identifying users with persistent behaviour — not just active users, but those who show the same action consistently across days. I worked on analysing behaviour patterns to: • Track user actions across consecutive days • Group continuous activity using ROW_NUMBER() logic • Identify streaks by adjusting date gaps • Filter users with meaningful streak length (≥ 5 days) • Select the strongest pattern per user using RANK() What I practised: • Window functions for streak detection • Handling sequential data with date logic • GROUP BY + HAVING for filtering patterns • Translating consistency into measurable conditions What stood out — Activity can be random… Consistency is never random. When actions repeat over time, They stop being noise and become behaviour. That’s where real insights start. SQL doesn’t just analyse data. It uncovers patterns hidden in time. Consistent learning, one query at a time 🚀 #SQL #LeetCode #SQLPractice #DataAnalytics #LearningInPublic

🚀 Day 19/50 – LeetCode SQL Challenge (Queries Quality & Percentage) Today’s problem focused on analyzing query performance by calculating quality and identifying poor queries using SQL. 📊 Key Concepts Used: • Aggregation (AVG, COUNT) • CASE WHEN (conditional logic) • Ratio calculation (rating / position) • ROUND() function 💡 Approach: Calculated query quality using AVG(rating / position) Used CASE WHEN to identify poor queries (rating < 3) Computed poor query percentage using average of conditional values Rounded both metrics to 2 decimal places for better readability 👉 Learned how to convert conditions into numerical values (0/1) and use AVG to directly calculate percentages. 🧠 Key Learning: AVG of 0/1 can be used to calculate percentage efficiently Combining aggregation with conditional logic simplifies complex problems Clean formatting (ROUND) makes results more professional 📈 Consistency in practice is building stronger SQL fundamentals every day #SQL #DataAnalytics #DataAnalyst #LearningInPublic #30DaysChallenge #SQLPractice #Analytics #CareerGrowth #Consistency #DataScience

🚀 Day 50 of My SQL Learning Journey Today I solved a medium-level SQL problem involving user retention analysis 🔥 🔹 Problem: Find the fraction of players who logged in again the day after their first login 🔗 Problem Link: https://lnkd.in/gsSuBeg5 🔹 Solution: SELECT ROUND( COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction FROM Activity a JOIN ( SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id ) f ON a.player_id = f.player_id AND a.event_date = DATE_ADD(f.first_login, INTERVAL 1 DAY); 🔹 Key Learning: Using subqueries to find first login Applying date functions (DATE_ADD) Calculating ratios using aggregation Real-world concept: user retention analysis 💡 SQL is powerful for analyzing user behavior and retention patterns! Consistency continues 🚀 #SQL #LeetCode #90DaysOfCode #CodingJourney #DataAnalytics #InterviewPreparation