SQL Skills Test for Hiring

1. You need to retrieve each customer's total order amount and their rank within their country by total spending. You must include customers with zero orders. Which query is most efficient?

• A.SELECT c.id, c.name, COALESCE(SUM(o.amount), 0) as total, RANK() OVER (PARTITION BY c.country ORDER BY SUM(o.amount) DESC) as rank FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name, c.country ORDER BY c.country, rank
• B.SELECT c.id, c.name, SUM(o.amount) as total, RANK() OVER (PARTITION BY c.country ORDER BY total DESC) as rank FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name, c.country
• C.SELECT c.id, c.name, (SELECT SUM(amount) FROM orders WHERE customer_id = c.id) as total, RANK() OVER (PARTITION BY c.country ORDER BY (SELECT SUM(amount) FROM orders WHERE customer_id = c.id) DESC) FROM customers c
• D.SELECT c.id, c.name, COALESCE(o.total, 0), RANK() OVER (ORDER BY o.total DESC) FROM customers c LEFT JOIN (SELECT customer_id, SUM(amount) as total FROM orders GROUP BY customer_id) o ON c.id = o.customer_id

2. What does this query output for the 2nd row of each product_id? SELECT product_id, price, LAG(price, 1) OVER (PARTITION BY product_id ORDER BY date) as prev_price FROM price_history

• A.NULL, because there is no previous row for the 2nd row in each partition
• B.The price from the 1st row of that product_id
• C.The difference between current and previous price
• D.An error, because LAG cannot be used with PARTITION BY

3. When should you use a subquery in the FROM clause (derived table) versus a CTE (WITH clause)? Select the best answer.

• A.Always use CTEs; they're universally more efficient
• B.Use subqueries for single-use logic; CTEs for reused logic or readability, though some databases treat them identically
• C.Subqueries are always faster because they execute inline
• D.CTEs cannot handle complex aggregations that subqueries can

4. Your query: SELECT category, COUNT(*) as count FROM products WHERE price > 100 GROUP BY category HAVING COUNT(*) > 5 returns categories with 5+ expensive products. Why is this approach better than filtering with WHERE COUNT(*) > 5?

• A.WHERE cannot reference aggregates; HAVING can. HAVING filters groups after aggregation, while WHERE filters rows before
• B.There is no difference; WHERE and HAVING are interchangeable
• C.WHERE is more efficient and should always be preferred
• D.HAVING is only for advanced SQL; beginners should use WHERE

5. You have a frequently-run query: SELECT * FROM transactions WHERE user_id = 1 AND created_date > '2025-01-01'. The table has 100M rows. Which index strategy is best?

• A.Composite index on (user_id, created_date) in that order
• B.Separate indexes on user_id and created_date; the optimizer will use both
• C.A covering index including all columns in SELECT *
• D.No index; full table scans are faster on modern hardware

6. A column contains NULL values. Your query: SELECT * FROM users WHERE status != 'active' returns 50 rows, but you expect 5000 rows (all non-active users). What's the issue?

• A.NULL values are not equal to any value, including when using !=. Use WHERE status != 'active' OR status IS NULL to include NULLs
• B.The query is correct; the data is incomplete
• C.You need to use status <> 'active' instead of !=
• D.NULL values cause the query to error, which is why fewer rows return