SQL
85 questionsWindow functions, query optimization, complex joins, real-world scenarios. These are the questions that actually come up in DE interviews.
Second Highest Salary
Write a query to find the second highest salary from an Employee table. Handle the case where there might not be a second highest.
Running Total with Reset
You have a transactions table with (user_id, txn_date, amount). Write a query to calculate a running balance that resets to 0 whenever it goes negative.
LAG/LEAD for Session Analysis
Given a clickstream table (user_id, page, timestamp), calculate the time spent on each page. The last page per session has NULL duration.
Moving Average with ROWS vs RANGE
Calculate a 7-day moving average of daily sales. Explain the difference between ROWS BETWEEN and RANGE BETWEEN. What happens with missing dates?
Percentile Without PERCENTILE_CONT
Calculate the median salary per department. Your database doesn't support PERCENTILE_CONT. Solve with standard SQL.
NTILE for Bucketing
Divide customers into 4 equal spending quartiles based on their total purchase amount. Return customer_id, total_spend, and quartile.
FIRST_VALUE / LAST_VALUE Gotcha
For each employee, show their salary alongside the highest and lowest salary in their department. Why does LAST_VALUE often give unexpected results?
Cumulative Distribution
Given an orders table, calculate what percentage of total revenue is generated by the top N% of customers. Show the cumulative revenue distribution.
Gaps and Islands
Given a table of user logins (user_id, login_date), find consecutive login streaks for each user. Return user_id, streak_start, streak_end, streak_length.
Recursive CTE for Hierarchy
Given an employee table with (id, name, manager_id), write a query to show the full management chain for each employee, from CEO down.
Anti-Join: Find Missing Records
Find all customers who have never placed an order. Write it three different ways and explain which is fastest.
Self-Join: Consecutive Events
Given a sensor_readings table (sensor_id, reading_time, value), find cases where the same sensor had 3 consecutive readings above the threshold of 100.
CTE vs Subquery vs Temp Table
When would you use a CTE vs a subquery vs a temp table? What are the performance implications of each?
Lateral Join for Top-N per Group
For each department, get the top 3 highest-paid employees. Compare LATERAL JOIN vs ROW_NUMBER approach. When is each better?
EXPLAIN ANALYZE Deep Dive
Your query is slow. Walk me through how you'd diagnose and fix it using EXPLAIN ANALYZE. What are you looking for?
SARGable vs Non-SARGable
Rewrite these queries to be SARGable: 1. WHERE YEAR(created_at) = 2024 2. WHERE UPPER(name) = 'JOHN' 3. WHERE price * 1.18 > 1000 4. WHERE COALESCE(status, 'active') = 'active'
Index Strategy for a Query
Given: SELECT name, email FROM users WHERE status = 'active' AND country = 'IN' ORDER BY created_at DESC LIMIT 10. Design the optimal index.
Correlated Subquery to JOIN Rewrite
This query is slow on 10M rows. Rewrite it: SELECT *, (SELECT MAX(amount) FROM orders WHERE orders.customer_id = c.id) as max_order FROM customers c
Partition Pruning
Your 5TB table is partitioned by date. The query scans all partitions despite a date filter. Why? How do you fix it?
COUNT(*) vs COUNT(col) vs COUNT(DISTINCT)
Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column). Which is fastest? When does each give different results?
GROUP BY with HAVING vs WHERE
Find departments with more than 5 employees whose average salary exceeds 100K. What's the execution order? Where does HAVING fit?
ROLLUP and CUBE for Subtotals
Generate a sales report with subtotals by region, by product, and a grand total — all in one query. Explain ROLLUP vs CUBE vs GROUPING SETS.
Conditional Aggregation (Pivot)
Pivot this table: (student, subject, score) → one row per student with columns for math_score, science_score, english_score. No PIVOT keyword allowed.
Top N per Group Without Window Functions
Get the top 3 products by revenue for each category. Solve it WITHOUT window functions (some older databases don't support them).
EXISTS vs IN Performance
When is EXISTS faster than IN? When is IN faster? Give concrete examples with table sizes.
Scalar Subquery Pitfall
This query works in dev but fails in prod: SELECT *, (SELECT region FROM warehouses WHERE zip = o.zip) as region FROM orders o Why? How do you fix it?
Deduplicate 500M Rows
Your 500M row fact table has duplicates from a broken ETL pipeline. You can't DELETE (no downtime allowed). How do you deduplicate?
SCD Type 2 Implementation
Implement SCD Type 2 for a customer dimension. A customer's city changed. Show the SQL to handle this update while preserving history.
Data Skew in Distributed Query
Your distributed query takes 3 hours instead of 20 minutes. One executor does all the work. Diagnose and fix the skew.
Merge / Upsert Pattern
You receive daily customer updates. Some are new customers, some are updates to existing ones. Write a MERGE statement. What if your database doesn't support MERGE?
Find Overlapping Date Ranges
Given a bookings table (room_id, check_in, check_out), find all overlapping bookings for the same room.
Sessionize Clickstream Data
Given clickstream data (user_id, url, timestamp), create sessions with a 30-minute inactivity timeout. Assign a session_id to each click.
Funnel Analysis
Given events (user_id, event_type, timestamp), calculate conversion rates through a funnel: homepage → product_view → add_to_cart → purchase. Events must be in order.
Year-over-Year Growth
Calculate month-over-month and year-over-year revenue growth. Handle months with zero revenue.
Slowly Changing Lookup
You have a fact table with event_date and an SCD2 dimension. Write the join correctly. What happens if you get it wrong?
Star Schema vs Snowflake
Explain the difference between star schema and snowflake schema. When would you choose one over the other?
Partitioning Strategy
You're designing a 10TB events table. How do you decide the partition key? What are the tradeoffs of too many vs too few partitions?
File Formats: Parquet vs ORC vs Avro
You're storing data in a data lake. When would you choose Parquet vs ORC vs Avro vs CSV/JSON?
Detect Fraudulent Transactions
Flag transactions where a user makes more than 3 transactions within 5 minutes, or a single transaction exceeds 5x their average. Write the SQL.
Retention Cohort Analysis
Build a retention cohort table: for users who signed up in each month, what percentage are still active in month 1, 2, 3... 12?
Incremental vs Full Load
Your ETL job loads a 200M row table daily. Currently it's a full reload taking 3 hours. How do you convert to incremental?
NULL Semantics Deep Dive
What does NULL = NULL return? What about NULL != NULL? How does NULL behave in GROUP BY, DISTINCT, UNION, and ORDER BY?
Generate Date Spine
Many of your queries need a complete date range with no gaps. Write a query to generate a date series from 2024-01-01 to 2024-12-31.
Window Frame for Running Distinct Count
Calculate a running count of distinct users per day. COUNT(DISTINCT) doesn't work with window functions. How do you solve it?
Transaction Isolation Levels
Explain READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE. When would each cause problems in an ETL pipeline?
Slowly Changing Dimension Types
Explain SCD Type 0, 1, 2, 3, and 6. When would you use each?
MERGE Conflicts and Idempotency
Your incremental MERGE job runs twice due to a scheduler bug. The data is now wrong. How do you make the pipeline idempotent?
Cross-Database Query Differences
You're migrating from PostgreSQL to Redshift. What SQL features will break? What needs rewriting?
Data Quality Checks in SQL
Write SQL-based data quality checks that run after every ETL load. What checks would you include for an orders table?
Design a Slowly Changing Fact Table
Your fact table records account balances. The balance changes throughout the day. How do you model this to support point-in-time queries AND aggregate reporting?
Primary Key vs Unique Key
What's the difference between a PRIMARY KEY and a UNIQUE constraint? Can a table have multiple of each?
Types of JOINs
Explain INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN with a simple example. When would you use each?
WHERE vs HAVING
What's the difference between WHERE and HAVING? Can you use WHERE with aggregate functions?
GROUP BY Basics
Why does this query fail? SELECT department, name, COUNT(*) FROM employees GROUP BY department
UNION vs UNION ALL
What's the difference between UNION and UNION ALL? When should you use each?
DELETE vs TRUNCATE vs DROP
Explain the difference between DELETE, TRUNCATE, and DROP. Which can be rolled back?
Subquery vs JOIN
Rewrite this subquery as a JOIN: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'India')
NULL Handling Basics
What does this return and why? SELECT 1 WHERE NULL = NULL SELECT 1 WHERE NULL IS NULL
ORDER BY with NULLs and Aliases
Where do NULLs appear in ORDER BY? Can you ORDER BY a column alias? Can you ORDER BY a column not in SELECT?
Indexes: What, Why, When
What is an index? When should you create one? When should you NOT create one?
CASE Statement
Categorize employees into salary bands: < 30K = 'Junior', 30-60K = 'Mid', > 60K = 'Senior'. Count employees in each band.
String Functions
Extract the domain from an email column. Split 'John-Doe-Senior' into first name, last name, and level using SQL string functions.
Date Functions
Get all orders from the last 30 days. Extract year, month, day of week. Calculate the difference between two dates.
Views vs Materialized Views
What's the difference between a view and a materialized view? When would you use each?
Normalization: 1NF, 2NF, 3NF
Explain 1NF, 2NF, and 3NF with examples. When would you deliberately denormalize?
Recursive CTE: Org Hierarchy
Given an `employees` table with `(id, name, manager_id)`, write a recursive CTE to: 1. Show the full reporting chain from any employee to the CEO 2. Find all direct and indirect reports of a specific manager 3. Calculate the depth (level) of each employee in the hierarchy
Query Plan Reading
Read this EXPLAIN output and identify all performance issues: ``` Sort (cost=45000 rows=500000) → Hash Join (cost=40000 rows=500000) → Seq Scan on orders (cost=10000 rows=5000000) Filter: status='active' → Hash (cost=25000 rows=100000) → Seq Scan on customers (cost=20000 rows=1000000) Filter: country='IN' ```
Transaction Isolation Levels
Explain the 4 SQL isolation levels with concrete examples. What anomalies does each prevent? Which does your production DB use and why?
Deadlock Diagnosis and Prevention
Your application is throwing deadlock errors in PostgreSQL. Explain what happened, show a scenario that causes it, and how to prevent it.
SQL Anti-Patterns
Identify and fix the performance issues in each query: 1. `SELECT * FROM orders WHERE YEAR(created_at) = 2024` 2. `SELECT * FROM orders WHERE status != 'cancelled'` 3. `SELECT * FROM orders WHERE id IN (SELECT order_id FROM returns)` 4. `SELECT DISTINCT * FROM orders o JOIN items i ON o.id = i.order_id`
Gaps and Islands Problem
Given a `logins` table with `(user_id, login_date)`, find the longest consecutive login streak for each user. Dates might have gaps.
Running Total with Reset
Given a `transactions` table with `(id, date, type, amount)` where type is 'deposit' or 'withdrawal', calculate the running balance that resets to 0 whenever it goes negative.
Median and Percentiles
Calculate the median salary per department. Do it WITHOUT using PERCENTILE_CONT (i.e., manually with window functions).
JSON Handling in SQL
Your table has a JSON column `metadata`. Extract nested fields, filter on JSON values, and aggregate JSON arrays. Show PostgreSQL and MySQL syntax.
Star Schema Design
Design a star schema for an e-commerce analytics platform. Define fact and dimension tables. Write 3 analytical queries that demonstrate why this design is powerful.
Debugging Slow UPDATE
Your `UPDATE orders SET status='archived' WHERE created_at < '2023-01-01'` has been running for 6 hours on a 500M row table. It's blocking all other operations. Diagnose and fix.
Cumulative Metrics and YTD
Write SQL to calculate: (1) Month-over-month revenue growth %, (2) Year-to-date cumulative revenue, (3) 3-month rolling average revenue, all in a single query.
Correlated Subquery vs JOIN
Rewrite this correlated subquery as a JOIN. Explain when a correlated subquery is actually better. ```sql SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) ```
Debugging Duplicate Results
Your report shows $2M revenue but finance says it's $1.5M. After investigation, you find the JOIN is creating duplicates. The query joins orders with payments. An order can have multiple payments (partial payments). How do you fix it?
MERGE / UPSERT
Write a MERGE statement (or equivalent) for PostgreSQL, MySQL, and Spark SQL. Your source has new and updated records. Handle inserts, updates, and deletes.
Index Strategy for Complex Queries
Your table has 500M rows. These are your top 3 slow queries. Design an index strategy that covers all three without creating too many indexes: 1. `WHERE status='active' AND created_at > '2024-01-01' ORDER BY created_at DESC LIMIT 100` 2. `WHERE customer_id = ? AND status IN ('pending', 'shipped') ORDER BY created_at DESC` 3. `WHERE amount > 1000 AND region = 'APAC'`
Sessionization in SQL
Given a `clicks` table with `(user_id, url, timestamp)`, group clicks into sessions. A session ends after 30 minutes of inactivity. Assign a session_id to each click.
PIVOT and UNPIVOT
Transform this data from rows to columns (PIVOT) and back (UNPIVOT). Show both standard SQL and database-specific syntax. ``` product | quarter | revenue Phone | Q1 | 1000 Phone | Q2 | 1500 Laptop | Q1 | 2000 Laptop | Q2 | 2500 ```
Data Lineage Query
You have a `table_dependencies` table with `(table_name, depends_on)`. Write SQL to: (1) Find all upstream tables for a given table, (2) Find all downstream tables that would be affected if a source table changes, (3) Find the longest dependency chain.
Window Frame Deep Dive
Explain the difference between ROWS, RANGE, and GROUPS frame specifications. Show examples where each gives different results.