Query Optimization — Series 2

Preview — 3 of 10 questions

Which query correctly copies name from customers into the customer_name column of every matching row in orders?

javascript

-- orders(id, customer_id, customer_name)
-- customers(id, name)

AUPDATE orders o SET customer_name = c.name JOIN customers c ON o.customer_id = c.id;

BUPDATE orders SET customer_name = customers.name WHERE orders.customer_id = customers.id;

CUPDATE orders o SET customer_name = c.name FROM customers c WHERE o.customer_id = c.id;

DUPDATE orders, customers SET orders.customer_name = customers.name WHERE orders.customer_id = customers.id;

Which query returns exactly one row per customer — their single most recent order, with all its columns?

javascript

-- orders(id, customer_id, order_date, total)

ASELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC;

BSELECT DISTINCT customer_id, MAX(order_date) FROM orders GROUP BY customer_id;

CSELECT * FROM orders WHERE order_date = MAX(order_date) GROUP BY customer_id;

DSELECT TOP 1 * FROM orders PARTITION BY customer_id ORDER BY order_date DESC;

What does this query compute?

javascript

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled_orders
FROM orders;

ATwo separate queries run in sequence

BAn error — FILTER cannot be combined with COUNT

CThe percentage of cancelled orders

DTotal order count, and a separate count of only cancelled orders, computed in a single pass over the table

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