← Back to Categories
SQL Interview Questions
Comprehensive list of 32 questions to test your knowledge.
Theory & Concepts
1
What is the difference between TRUNCATE, DELETE, and DROP?
2
Explain the different types of joins.
3
Primary vs Unique Key?
4
WHERE vs HAVING?
5
Normalization 1NF-3NF?
6
Denormalization?
7
CTE vs Temp Table?
8
UNION vs UNION ALL?
9
Window Functions?
10
RANK vs DENSE_RANK?
11
Index performance?
12
ACID properties?
13
Materialized view?
14
Handling NULLs?
15
Execution order?
16
Cross join?
17
Self join?
18
Implicit vs explicit casting?
19
IN vs EXISTS?
20
Stored procedure vs function?
21
OLTP vs OLAP?
22
Indexing pros/cons?
23
Clustered vs non-clustered?
24
Find duplicates?
25
SCD Type 2 SQL?
Hands-on & Coding Scenarios
1
Employee Salary Analytics
Context / Sample Data
Table: employees
| emp_id | name | dept_id | salary | manager_id |
|--------|-------|---------|--------|------------|
| 1 | Alice | 10 | 90000 | NULL |
| 2 | Bob | 10 | 80000 | 1 |
| 3 | Carol | 20 | 75000 | 1 |
| 4 | Dave | 20 | 95000 | 3 |Tasks:
- Find the second highest salary in the company.
- Find the highest salary in each department.
- Find all employees who earn more than their direct manager.
Follow-up / Optimization:
- How would you optimize for 10M rows?
- How to handle ties using DENSE_RANK?
2
User Login Streaks
Context / Sample Data
Table: user_logins
| user_id | login_date |
|---------|------------|
| 101 | 2024-01-01 |
| 101 | 2024-01-02 |
| 101 | 2024-01-03 |
| 102 | 2024-01-01 |
| 102 | 2024-01-05 |Tasks:
- Total number of logins per user.
- Identify users who logged in for at least 3 consecutive days.
- Latest login date for each user.
Follow-up / Optimization:
- Explain the streak detection logic.
- Handle multiple logins per day?
3
E-commerce Order Revenue
Context / Sample Data
Table: orders
| order_id | customer_id | order_date | status |
|----------|-------------|------------|------------|
| 1 | 99 | 2023-11-01 | Delivered |
| 2 | 99 | 2023-11-05 | Cancelled |
Table: order_items
| order_id | product_id | quantity | price |
|----------|------------|----------|-------|
| 1 | P1 | 2 | 50.0 |
| 1 | P2 | 1 | 100.0 |Tasks:
- Total revenue per customer for 'Delivered' orders.
- Top 3 most popular products by quantity sold.
- Customers who never had a 'Delivered' order.
Follow-up / Optimization:
- Calculate a running total of revenue per day.
- What indexes to add?
4
Financial Transactions
Context / Sample Data
Table: transactions
| tx_id | account_id | tx_type | amount | tx_date |
|-------|------------|------------|--------|------------|
| 1 | A100 | DEPOSIT | 500.0 | 2024-02-01 |
| 2 | A100 | WITHDRAWAL | 200.0 | 2024-02-02 |
| 3 | A200 | DEPOSIT | 1000.0 | 2024-02-01 |
| 4 | A200 | WITHDRAWAL | 1500.0 | 2024-02-03 |Tasks:
- Calculate the final balance for each account.
- Find accounts that went into negative balance.
- Find the top 5 largest single deposits.
Follow-up / Optimization:
- Calculate a running daily balance per account.
- Handle out-of-order transactions?
5
Movie Ratings Analysis
Context / Sample Data
Table: ratings
| user_id | movie_id | rating | rating_date |
|---------|----------|--------|-------------|
| U1 | M1 | 4.5 | 2024-01-01 |
| U2 | M1 | 5.0 | 2024-01-02 |
| U3 | M1 | 2.0 | 2024-01-03 |
| U1 | M2 | 3.0 | 2024-01-04 |Tasks:
- Find the highest-rated movies with at least 100 reviews.
- Find users who rate movies lower than the overall average.
- Calculate the weekly trend of ratings for a specific movie.
Follow-up / Optimization:
- Handle review bombing?
- Find similar users based on shared movie ratings.
6
Window Functions — Running Totals & Cumulative Stats
Context / Sample Data
Table: daily_sales
| sale_date | store_id | product | amount |
|------------|----------|---------|--------|
| 2024-01-01 | S1 | Laptop | 1200 |
| 2024-01-01 | S1 | Phone | 800 |
| 2024-01-02 | S1 | Laptop | 1500 |
| 2024-01-02 | S2 | Laptop | 1100 |
| 2024-01-03 | S1 | Tablet | 600 |
| 2024-01-03 | S2 | Phone | 900 |Tasks:
- Calculate a running total of amount per store, ordered by sale_date.
- Calculate the 2-day moving average of sales per store.
- For each row, show the previous day's sale amount for the same store using LAG().
- Calculate each sale's percentage contribution to its store's total revenue.
Follow-up / Optimization:
- What is the difference between ROWS and RANGE in the frame clause?
- Can you use LEAD() to calculate the day-over-day growth rate?
7
Window Functions — Ranking, Top-N & Gaps
Context / Sample Data
Table: employee_salaries
| emp_id | name | department | salary | hire_date |
|--------|---------|------------|--------|------------|
| E1 | Alice | Eng | 95000 | 2020-01-15 |
| E2 | Bob | Eng | 95000 | 2021-03-10 |
| E3 | Carol | Eng | 80000 | 2022-06-01 |
| E4 | Dave | Sales | 70000 | 2019-09-20 |
| E5 | Eve | Sales | 85000 | 2020-11-05 |
| E6 | Frank | Sales | 85000 | 2023-01-12 |
| E7 | Grace | HR | 72000 | 2021-07-01 |Tasks:
- Rank employees within each department by salary (highest first). Show RANK, DENSE_RANK, and ROW_NUMBER side by side.
- Find the top 2 highest-paid employees per department (handle ties).
- Calculate the salary gap between each employee and the highest earner in their department.
- Calculate the Nth highest salary across the entire company using NTILE.
Follow-up / Optimization:
- When should you use ROW_NUMBER vs DENSE_RANK for top-N queries?
- How do you use PARTITION BY with multiple columns?