Query Optimization

Preview — 3 of 10 questions

What does this query return?

javascript

SELECT
  name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

AEvery employee with their salary rank within their department — ties get the same rank, next rank is skipped

BThe top-ranked employee per department only

CEvery employee ranked across the entire company, ignoring department

DAn error — RANK() requires a GROUP BY clause

What is the advantage of using a CTE over a subquery?

javascript

-- With CTE
WITH high_value_orders AS (
  SELECT user_id, SUM(total) AS revenue
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, h.revenue
FROM users u
JOIN high_value_orders h ON u.id = h.user_id;

ACTEs improve readability and can be referenced multiple times in the same query; subqueries cannot be reused

BCTEs are always faster than subqueries because they are cached

CCTEs run in parallel, subqueries run sequentially

DCTEs bypass index lookups, making them faster for large tables

Which approach avoids the N+1 query problem when fetching users and their orders?

javascript

// N+1 example (bad):
const users = await db.query('SELECT * FROM users');        // 1 query
for (const user of users) {
  user.orders = await db.query(                              // N queries
    'SELECT * FROM orders WHERE user_id = $1', [user.id]
  );
}

AUse async/await to run the N queries in parallel

BAdd an index on orders.user_id

CUse a single JOIN query to fetch users and orders together, then group in application code

DUse a stored procedure

Answer all 10 questions (7 more), see explanations for every answer, and track your score.