Advanced SQL Interview Questions and Business Insights

SQL joins feel hard until you stop thinking in diagrams and start thinking in business questions. A join simply answers this: 𝗛𝗼𝘄 𝗱𝗼 𝗜 𝗰𝗼𝗺𝗯𝗶𝗻𝗲 𝗱𝗮𝘁𝗮 𝗳𝗿𝗼𝗺 𝘁𝘄𝗼 𝘁𝗮𝗯𝗹𝗲𝘀 𝘁𝗼 𝗴𝗲𝘁 𝗼𝗻𝗲 𝘂𝘀𝗲𝗳𝘂𝗹 𝗮𝗻𝘀𝘄𝗲𝗿? Here are the 3 joins every analyst should know: 1. 𝙄𝙉𝙉𝙀𝙍 𝙅𝙊𝙄𝙉 Use it when you only want matching records from both tables. 𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: You have a customers table and an orders table. You want to see only customers who actually placed an order. 𝘚𝘌𝘓𝘌𝘊𝘛 𝘤.𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘯𝘢𝘮𝘦, 𝘰.𝘰𝘳𝘥𝘦𝘳_𝘪𝘥 𝘍𝘙𝘖𝘔 𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳𝘴 𝘤 𝘐𝘕𝘕𝘌𝘙 𝘑𝘖𝘐𝘕 𝘰𝘳𝘥𝘦𝘳𝘴 𝘰 𝘖𝘕 𝘤.𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘪𝘥 = 𝘰.𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘪𝘥; 2. 𝙇𝙀𝙁𝙏 𝙅𝙊𝙄𝙉 Use it when you want everything from the left table, even if there is no match on the right. 𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: You want a list of all customers, including those who have never ordered. 𝘚𝘌𝘓𝘌𝘊𝘛 𝘤.𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘯𝘢𝘮𝘦, 𝘰.𝘰𝘳𝘥𝘦𝘳_𝘪𝘥 𝘍𝘙𝘖𝘔 𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳𝘴 𝘤 𝘓𝘌𝘍𝘛 𝘑𝘖𝘐𝘕 𝘰𝘳𝘥𝘦𝘳𝘴 𝘰 𝘖𝘕 𝘤.𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘪𝘥 = 𝘰.𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘪𝘥; This is great for finding gaps. For example:  • customers with no orders  • employees with no assigned projects  • products with no sales 3. 𝙍𝙄𝙂𝙃𝙏 𝙅𝙊𝙄𝙉 / 𝙁𝙐𝙇𝙇 𝙅𝙊𝙄𝙉 Less common in day-to-day analytics, but useful when you want to check what exists on one side but not the other. The real trick with joins is not memorizing syntax. It is asking:  • What is my base table?  • What am I trying to keep?  • What relationship am I matching on? Once that becomes clear, joins get much easier. CTA: Which SQL join gave you the most trouble when you were learning? #SQL #DataAnalytics #BusinessIntelligence #DataAnalyst #LearnSQL

⚠️ DAY 10/15 — SQL TRAP: Subquery vs JOIN Same result. One takes 2 seconds. Other takes 120 seconds. On 1 million rows — this difference will cost you your job. 👇 🎯 The Situation: You want each employee's department name. You write a subquery inside SELECT. It works perfectly. Results look correct. But on large data — your query is running forever. 😵 Why? The results are identical! 🧠 Here's what's happening behind the scenes: Subquery inside SELECT = correlated subquery. It doesn't run once. It runs separately for EVERY SINGLE ROW. 4 employees? Runs 4 times. 1000 employees? Runs 1000 times. 1 million employees? Runs 1 MILLION times. 😬 JOIN runs once for ALL rows. That's it. ✅ The Numbers Don't Lie: 1 million rows — Subquery → ~120 seconds ❌ JOIN → ~2 seconds ✅ Same result. 60x faster. Just by changing the approach. 💡 Real Life Example: Subquery = going to the library for EACH student separately to find their class name. 1000 students = 1000 library trips. 😵 JOIN = getting the full class list ONCE and matching all students together in one go. ✅ Same information. One way is just exhausting. 📌 Save This Rule: → Subquery in SELECT runs per row → always slow on big data → JOIN runs once for all rows → always faster → Same result on small tables → huge difference on production data → See a subquery in SELECT? → replace it with JOIN → Always think about performance, not just correct results 🔑 One Line to Remember: Subquery = runs per row = slows down as data grows JOIN = runs once = stays fast no matter the size Correct code and fast code are two different things. 💬 Real Talk: In interviews and real jobs — writing a query that WORKS is the minimum. Writing a query that works FAST is what separates good from great. This is exactly the kind of optimisation interviewers love to ask about. 😎 🙋 Have you ever had a query that worked fine on test data but crashed on production? This was probably the reason! Comment below 👇 Follow for Day 11 tomorrow 🚀 #SQL #SQLForBeginners #DataAnalytics #LearnSQL #SQLTips #DataEngineering #InterviewPrep

🔤 SQL String Functions — Clean, Format & Standardize Text Data! Text fields often come messy: inconsistent casing, extra spaces, or missing formatting. SQL string functions help analysts tidy up text data so it’s consistent, searchable, and presentation‑ready. 🔹 1️⃣ CONCAT — Combine Text SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers; 👉 Merge columns into a single readable field. 🔹 2️⃣ TRIM — Remove Extra Spaces SELECT TRIM(name) AS cleaned_name FROM customers; 👉 Eliminate unwanted spaces for consistency. 🔹 3️⃣ UPPER / LOWER — Standardize Case SELECT UPPER(city) AS city_upper, LOWER(email) AS email_lower FROM customers; 👉 Normalize text for easier comparisons and reporting. 🔹 4️⃣ SUBSTRING — Extract Parts of Text SELECT SUBSTRING(phone, 1, 3) AS area_code FROM customers; 👉 Pull out specific portions of text (like area codes). 💡 Analyst Tip: String functions are essential for data cleaning, reporting, and dashboard building. They ensure text fields are consistent and business‑friendly. 📢 Stay Tuned! Next in the SQL Tips Series: SQL Date Functions — learn how to analyze time‑based trends with YEAR(), MONTH(), DATEDIFF(), and more! #SQL #DataCleaning #DataAnalytics #DataAnalyst #SQLTips #LearningSQL #BusinessIntelligence #DataScience #CareerGrowth #Codebasics #DataDriven

SQL Window Functions: ROW_NUMBER vs. RANK vs. DENSE_RANK I’ve been sharpening my SQL skills lately, specifically looking at how different ranking functions handle data. To practice, I used a World Happiness dataset to compare how ROW_NUMBER(), RANK(), and DENSE_RANK() behave. Data Maven Analytics Practice Query 1: Ranking Country Happiness Scores SELECT country, AVG(happiness_score) AS avg_hs, ROW_NUMBER() OVER (ORDER BY AVG(happiness_score) DESC) AS row_num, RANK() OVER (ORDER BY AVG(happiness_score) DESC) AS rank_val, DENSE_RANK() OVER (ORDER BY AVG(happiness_score) DESC) AS dense_rank_val FROM happiness_scores GROUP BY country; Practice Query 2: Ranking Regions by Country Count SELECT region, COUNT(country), ROW_NUMBER() OVER (ORDER BY COUNT(country) DESC) AS row_num, RANK() OVER (ORDER BY COUNT(country) DESC) AS rank_val, DENSE_RANK() OVER (ORDER BY COUNT(country) DESC) AS dense_rank_val FROM happiness_scores GROUP BY region ORDER BY region; Note: In these specific practice runs, the rank_val and dense_rank_val columns often output the same sequence because the average scores and country counts happened to be unique. However, the logic is crucial: if two countries had the exact same happiness score, RANK would skip the next number (1, 1, 3) while DENSE_RANK would keep it sequential (1, 1, 2). Always pick the function that fits the business logic! 💡 #SQL #DataAnalytics #LearningInPublic #Database #CodingPractice

🚀 Day 34/100 — CASE Statements: Adding Logic to SQL ⚡ Today I learned how to use CASE statements in SQL, which help apply conditional logic directly inside queries. 📊 What is a CASE statement? 👉 Similar to IF-ELSE logic in programming 👉 Used to create new columns or categorize data 📌 What I explored today: 🔹 Conditional logic in SQL 🔹 Creating new categories 🔹 Using CASE with SELECT 🔹 Combining with GROUP BY 💻 Example Scenario: 👉 Categorize customers based on spending 📌 Example Query: SELECT customer_id, order_amount, CASE WHEN order_amount > 1000 THEN 'High Value' WHEN order_amount BETWEEN 500 AND 1000 THEN 'Medium Value' ELSE 'Low Value' END AS customer_category FROM orders; 🔥 Key Learnings: 💡 CASE adds decision-making power to SQL 💡 Helps in data categorization & reporting 💡 Very useful for dashboards & business insights 🚀 Real-world use cases: ✔ Customer segmentation ✔ Sales performance classification ✔ Risk analysis 🔥 Pro Tip: 👉 Use CASE with GROUP BY to create powerful summaries 📊 Tools Used: SQL | MySQL ✅ Day 34 complete. 👉 Quick question: Where would you use CASE — data cleaning or reporting? 🤔 #Day34 #100DaysOfData #SQL #CaseStatement #DataAnalytics #LearningInPublic #CareerGrowth #JobReady #InterviewPrep

🚀 Day 31/100 — HAVING vs WHERE in SQL ⚡ Today I learned a very important SQL concept that often confuses beginners — HAVING vs WHERE. 📊 What I learned: 👉 Both are used for filtering data, but at different stages 🔹 WHERE → Filters rows before grouping 🔹 HAVING → Filters groups after aggregation 💻 Example Scenario: 👉 Find products with total sales greater than 1000 📌 Example Query: SELECT product_name, SUM(sales) AS total_sales FROM orders WHERE sales > 100 GROUP BY product_name HAVING SUM(sales) > 1000; 🔥 Key Difference: WHEREHAVINGFilters rowsFilters grouped dataUsed before GROUP BYUsed after GROUP BYCannot use aggregatesCan use aggregates 💡 Simple Explanation: 👉 WHERE = filter raw data 👉 HAVING = filter summarized data 🚀 Why this matters: Used in: ✔ Data Analysis ✔ SQL Interviews (very common!) ✔ Reporting & Dashboards 🔥 Pro Tip: 👉 Use WHERE for performance (filters early) 👉 Use HAVING only when needed with aggregates 📊 Tools Used: SQL | MySQL ✅ Day 31 complete. 👉 Quick question: Which one confused you more — WHERE or HAVING? 😄 #Day31 #100DaysOfData #SQL #DataAnalytics #SQLTips #LearningInPublic #CareerGrowth #JobReady #InterviewPrep

🚀 Day 10 – SQL Single Row Functions Today I learned how to clean and transform data using functions --- 🔹 1. UPPER() 👉 Converts text to uppercase SELECT UPPER('sql'); ➡ Output: SQL --- 🔹 2. LOWER() 👉 Converts text to lowercase SELECT LOWER('SQL'); ➡ Output: sql --- 🔹 3. SUBSTR() 👉 Extracts part of a string SELECT SUBSTR('AHMEDABAD',3,4); ➡ Output: MEDA --- 🔹 4. REPLACE() 👉 Replaces characters/text SELECT REPLACE('MALAYALAM','A','B'); ➡ Output: MBLBYBLBM --- 🔹 5. MOD() 👉 Gives remainder SELECT MOD(10,3); ➡ Output: 1 --- 🔹 6. ROUND() 👉 Rounds value SELECT ROUND(5.5); ➡ Output: 6 --- 🔹 7. TRUNC() 👉 Removes decimal part SELECT TRUNC(5.5); ➡ Output: 5 --- 🔹 8. TO_CHAR() 👉 Converts date to readable format SELECT TO_CHAR(SYSDATE,'DAY'); ➡ Output: (Current Day Name) --- 🔹 9. NVL() 👉 Replaces NULL with value SELECT SALARY + NVL(COMMISSION,0) FROM EMP; ➡ Output: Adds salary even if commission is NULL --- 💡 Simple Understanding 👉 These functions help to clean and format raw data 👉 Very useful in real-world projects --- #SQL #DataAnalytics #LearningJourney #DataCleaning

Knowing SQL is one thing. Initially, I was focused only on learning SQL itself. But applying it to real business problems is what truly defines a data analyst. Over the past few weeks, I’ve been working on moving beyond syntax and understanding how SQL can be used to solve practical, business-driven questions. Some of the problems I’ve been exploring include: - Analyzing which customers contribute the most to overall revenue 📊 - Identifying high-performing products or services based on sales data 📦 - Tracking shipment or order delays across different regions 🚚 - Understanding customer purchase patterns to support better decision-making 📈 This shift in approach is helping me see how data is actually used within organizations to drive insights—not just queries. Continuing to build this practice of combining SQL with business understanding. #SQL #DataAnalytics #BusinessIntelligence #LearningJourney

Day 09 of SQL — JOINS (Where real analysis begins) 🔥 You don’t become a Data Analyst by querying one table… You become one when you connect multiple tables. That’s exactly what JOINS do. ⸻ 🔹 What is JOIN? It combines data from multiple tables based on a common column. 👉 Basically: Connecting the dots in your data 👉 Now instead of raw data… You get meaningful insights ⸻ 🧠 Simple way to understand: Table 1 = Students Table 2 = Courses JOIN = relationship Result = complete picture ⸻ ⚡ Types of JOINS you must know: • INNER JOIN → only matching data • LEFT JOIN → all from left + matched from right • RIGHT JOIN → all from right + matched from left ⸻ 📌 Why this matters: Real-world data is NEVER in one table • Customers + Orders • Products + Sales • Employees + Departments Everything is connected. And JOINS help you unlock that connection. ⸻ ⚡ Pro Tip: If your analysis feels incomplete… You probably need a JOIN. ⸻ If you’re serious about Data Analytics, this is where things get real 👇 👉 SQL is not about queries 👉 It’s about understanding relationships in data ⸻ Follow for daily SQL learning (basic → advanced) 🚀 #SQL #DataAnalytics #LearnSQL #DataAnalyst #TechSkills #CareerGrowth

🧠 SQL Cheat Sheet Every Data Analyst Should Know If you're preparing for analytics roles, these are the SQL concepts I use almost daily 👇 1. SELECT & Filtering 👉 Fetch only what you need 👉 Use WHERE to narrow down data 2. Aggregations 👉 COUNT, SUM, AVG 👉 Helps answer: “How much / how many?” 3. GROUP BY 👉 Break data into segments 👉 Example: orders by category, users by city 4. JOINs (Very Important) 👉 Combine multiple tables INNER JOIN → matching data LEFT JOIN → keep all from left table 5. CASE WHEN 👉 Add business logic inside SQL 👉 Create categories, flags, segments 6. Window Functions 👉 ROW_NUMBER, RANK, LAG 👉 Useful for ranking, cohorts, trends 7. CTEs (WITH clause) 👉 Make complex queries readable 👉 Break logic into steps 8. Subqueries 👉 Query inside a query 👉 Useful for filtering & comparisons 💡 Biggest learning: SQL is not about remembering syntax— it’s about thinking in terms of data and logic. If you're learning SQL, focus on this flow: 👉 Filter → Join → Aggregate → Analyze #SQL #DataAnalytics #LearnSQL #AnalyticsTips #InterviewPrep