Top 50 SQL Questions

-

 

🔥 1. Basic → Intermediate (Warm-up but important)

1. Find duplicate records in a table
2. Delete duplicate records (keep latest)
3. Find second highest salary
4. Find Nth highest salary
5. Get employees earning more than their manager
6. Count employees in each department
7. Departments with more than 5 employees
8. Get employees who joined in last 30 days
9. Find records with NULL values in specific columns
10. Replace NULL values with default

⚡ 2. Joins & Relationships (Very common)

11. Find customers who never placed an order
12. Find orders without matching customers
13. Self join to find employee-manager hierarchy
14. Find mutual friends (self join problem)
15. Cross join to generate combinations
16. Find missing IDs in a sequence
17. Find unmatched records between two tables
18. Anti-join using NOT EXISTS
19. Compare two tables and find differences
20. Join 3+ tables and aggregate results

🚀 3. Window Functions (VERY IMPORTANT 🔥)

21. Assign row numbers to each partition
22. Find top 3 salaries per department
23. Find latest record per customer
24. Calculate running total
25. Moving average (last 3 days)
26. Find gaps in sequence (missing dates)
27. Rank employees by salary
28. Difference between consecutive rows
29. Find duplicate records using window functions
30. Get first and last record per group

🧠 4. Aggregation & Grouping (Scenario-heavy)

31. Total sales per region per month
32. Find highest selling product per category
33. Conditional aggregation (CASE WHEN)
34. Pivot rows into columns
35. Unpivot columns into rows
36. Count distinct values per group
37. Find percentage contribution per group
38. Find median salary
39. Find departments with highest avg salary
40. Group consecutive records

🔥 5. Subqueries & CTEs

41. Use CTE to find hierarchical data (org chart)
42. Recursive CTE for parent-child relationships
43. Find employees with salary above avg
44. Correlated subquery to find latest order
45. Replace subquery with JOIN
46. Use multiple CTEs in one query
47. Find running total using CTE
48. Compare performance: CTE vs temp table
49. Find cumulative percentage
50. Solve complex multi-step transformation using CTE

💣 MUST-PRACTICE (Most asked in real interviews)

If you’re short on time, focus on these 15:

  • #2 Delete duplicates
  • #3, #4 Nth highest salary
  • #11 Customers with no orders
  • #22 Top N per group
  • #23 Latest record per group
  • #24 Running total
  • #25 Moving average
  • #28 Difference between rows
  • #29 Dedup using window
  • #31 Sales aggregation
  • #33 Conditional aggregation
  • #34 Pivot
  • #41 Recursive CTE
  • #44 Correlated subquery

Comments

Post a Comment

Popular posts from this blog

TIME-SERIES SQL

-
  🔥 TIME-SERIES SQL (MSSQL SERVER ONLY) 📌 Sample Table (Used in All Queries) CREATE TABLE transactions ( user_id INT , txn_time DATETIME, amount INT ); 🟢 SIMPLE LEVEL 1️⃣ Running Total (Cumulative Sum) SELECT user_id, txn_time, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY txn_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM transactions; 2️⃣ Row-Based Moving Average (Last 3 Records) SELECT user_id, txn_time, amount, AVG(amount) OVER ( PARTITION BY user_id ORDER BY txn_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM transactions; 3️⃣ Time Difference Between Events SELECT user_id, txn_time, LAG(txn_time) OVER ( PARTITION BY user_id ORDER BY txn_time ) AS prev_time, DATEDIFF( MINUTE , LAG(txn_time) OVER (PARTITION BY user_id ORDER ...
Read more

SCD TYPE 2 – INTERVIEW QUESTIONS + MERGE CODE

-
 Below is a COMPLETE, interview-ready guide to SCD Type 2 using MERGE , covering ALL POSSIBLE QUESTIONS (theory + edge cases) AND production-grade SQL code . This is curated for senior data engineer / Azure / Databricks / SQL Server interviews. 🔹 SCD TYPE 2 – INTERVIEW QUESTIONS + MERGE CODE 1️⃣ What is SCD Type 2? Answer: SCD Type 2 maintains full historical changes by: Expiring old records Inserting a new row for every change Typical columns: effective_start_date effective_end_date is_current version (optional) 2️⃣ SCD Type 2 Table Design (Interview MUST) 🎯 Dimension Table CREATE TABLE dim_customer ( customer_sk INT IDENTITY ( 1 , 1 ), customer_id INT , name VARCHAR ( 100 ), city VARCHAR ( 100 ), effective_start_date DATE , effective_end_date DATE , is_current CHAR ( 1 ) ); 🎯 Staging Table CREATE TABLE stg_customer ( customer_id INT , name VARCHAR ( 100 ), city VARCHAR ( 100 ), load_date ...
Read more

PySpark Interview Questions with Detailed Answers

-
  Spark Internals & Execution 1. Explain the full lifecycle of a Spark job — from code to DAG to task execution. Definition: The Spark job lifecycle begins when the driver program initiates an action (e.g., .collect() , .count() ). Here's a step-by-step breakdown: User Code: You write transformations and actions in PySpark. Logical Plan: Spark creates a logical plan based on your transformations. Optimization: Catalyst optimizer optimizes the logical plan. Physical Plan: Spark creates a physical plan with stages. DAG (Directed Acyclic Graph): Stages are translated into a DAG of tasks. Task Scheduling: Tasks are distributed across executors. Execution: Executors run the tasks in parallel. Healthcare Example: In processing electronic health records (EHR), you may filter patients by diagnosis, join with lab results, and then aggregate. Spark translates this pipeline into optimized stages and tasks. 2. What is the difference between transformations and actions? Why are tra...
Read more