Top SQL Interview Questions (Basic to Advanced)
Top N / Filtering
1. 2nd highest salary
2. 2nd highest salary department-wise (without window functions)
3. Nth highest salary using CTE
4. Employees with salary > manager salary
5. Get employee(s) with max salary per department
Date & Time-Based
1. Find users who logged in on 3 consecutive days
2. Identify gaps in dates (missing dates)
3. Calculate number of active days per user
4. Users who placed 2+ orders within 10 minutes
5. Calculate week-over-week or month-over-month change
Aggregation & Window Functions
1. Cumulative sum (partitioned by user/date)
2. Rolling average over 7 days
3. Percentile salary per department
4. Rank/row_number/dense_rank by group
5. Lead/Lag to compare user actions over time
Joins and Relationships
1. Inner, Left, Right, Full Outer joins
2. Self join for manager-employee hierarchy
3. Join across 3+ tables with aliasing
4. Join customer-orders-payments tables
5. Find customers who bought product A but not B
Performance & Optimization
1. SQL Optimization Techniques (avoid subqueries, use CTEs, proper indexes)
Top SQL Interview Questions (Basic to Advanced)
2. Query to run faster on large datasets
3. Why use EXISTS over IN
4. Indexing: clustered vs non-clustered
5. Covering index and composite index
Database Design / Concepts
1. Normalization & Denormalization (1NF to 3NF)
2. ACID Properties
3. Use of surrogate vs natural keys
4. Star vs Snowflake schema
5. OLTP vs OLAP
Procedural SQL
1. Stored Procedures - syntax & use
2. Triggers - before/after insert, update
3. Transactions with COMMIT and ROLLBACK
4. Error handling in procedures
5. Dynamic SQL using EXEC or sp_executesql
Analytics-Based
1. Cohort analysis using SQL
2. Funnel conversion steps using clickstream
3. Pareto (80/20) principle using SQL
4. Churn prediction based on last activity
5. Basket analysis (products bought together)
Real-Time Complex Scenarios
1. User faced 3 errors in a row - how to identify
2. Sessionize user behavior based on time gap
3. Running totals with resets on condition
Top SQL Interview Questions (Basic to Advanced)
4. Detect changes in a user's status over time
5. Find people who repeated an action within the same hour
Miscellaneous
1. Difference between DELETE, TRUNCATE, DROP
2. Temp table vs CTE vs subquery
3. How to handle NULLs in joins and filters
4. How to pivot and unpivot data
5. How to generate calendar table using SQL
