Case-Study Questions and How to Attack Them

Start by pinning down the grain, because almost every mistake in this kind of question comes from joining at the wrong level. Ask two questions out loud: what does one row of the output represent, and what does one row of each input table represent. A product can appear on many order lines, so a join between products and order lines multiplies product rows. Revenue is a sum over the order lines, so it has to be aggregated before it can be compared or ranked.

Then state the plan out loud: build the revenue per product, rank the products inside each category, keep the top three, and join the result back to the product names for display. Ranking before joining back to the dimension table is the shape that keeps the query correct.

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  category_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_lines (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL
);

INSERT INTO categories (id, name) VALUES
  (1, 'Peripherals'),
  (2, 'Displays');

INSERT INTO products (id, category_id, name, price) VALUES
  (1, 1, 'keyboard', 80),
  (2, 1, 'mouse', 25),
  (3, 1, 'monitor stand', 40),
  (4, 1, 'webcam', 60),
  (5, 2, 'monitor', 300);

INSERT INTO order_lines (id, product_id, quantity) VALUES
  (1, 1, 2),
  (2, 1, 1),
  (3, 4, 3),
  (4, 5, 1),
  (5, 5, 2);

WITH product_revenue AS (
  SELECT p.id,
         p.category_id,
         p.name,
         SUM(p.price * ol.quantity) AS revenue
  FROM products p
  JOIN order_lines ol ON ol.product_id = p.id
  GROUP BY p.id, p.category_id, p.name
),
ranked AS (
  SELECT pr.*,
         DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rnk
  FROM product_revenue pr
)
SELECT c.name AS category, r.name AS product, r.revenue
FROM ranked r
JOIN categories c ON c.id = r.category_id
WHERE r.rnk <= 3
ORDER BY c.name, r.revenue DESC, r.name;

Peripherals returns the keyboard at 240, the webcam at 180, and the mouse at 25. The monitor stand never sold, so it is absent — an inner join on order lines drops it, which is usually what you want for a revenue ranking but worth confirming out loud. Displays returns the monitor at 900.

Two details an interviewer will look for. First, revenue is price * quantity aggregated over the lines, not a stored column, because price changes over time and a stored total silently disagrees with the current price. Second, DENSE_RANK rather than ROW_NUMBER: if two products tie on revenue, DENSE_RANK gives them the same rank, so a tie at rank 2 does not push the third product out of the top three. If the business rule says exactly three products and ties should be broken arbitrarily, ROW_NUMBER with a deterministic tiebreaker is the alternative — say which one you chose and why.

Clarity first, always. A business request is a hypothesis about the data, and the fastest way to a wrong answer is to code before you agree on what the question means.

Ask about the grain first: does one row mean one customer, one order, one order line, or one day per customer? The grain decides the join, and it is the single biggest determinant of whether the numbers are right. Then ask about the time window: is revenue booked on order date, shipment date, or payment date, and is the window inclusive of today? Then about the treatment of edges: do cancelled orders count, do returns subtract, do products with no sales appear, is a customer with two accounts one customer or two?

Then ask about the shape of the answer: one row or many, sorted or not, how many rows exactly. "Top 10" and "top 10 per category" are different questions with different queries, and the per-category version is the one that exposes the ranking mistake.

Finally, ask what the number will be used for. A metric that drives a dashboard needs to be stable and reproducible; a metric that drives a one-off decision can be rough. This tells the interviewer whether you need to model a slowly-changing dimension, whether historical recomputation matters, and whether you should build a view or a one-off query.

The reason this matters in an interview is that the interviewer is usually testing the questions you ask as much as the SQL you write. A candidate who immediately starts typing loses points; a candidate who spends two minutes agreeing on grain, window and edge cases, and then says "here is the shape I will build, and here is why", is demonstrating the skill the role actually needs.

The general answer is to stop recomputing what has not changed, and the specific tools differ by database. Start by finding out whether the query is slow because it reads too much or because it sorts and groups too much, because the two have different fixes.

In SQLite, which is what most interview exercises run on, the levers are limited but real: an index on the filtered column turns a full scan into a range scan, a covering index lets the engine satisfy the query from the index alone, and materialising the result into a summary table moves the cost from every read to a nightly job.

CREATE TABLE order_lines (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  order_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price INTEGER NOT NULL
);

CREATE INDEX idx_lines_order ON order_lines (order_id);

-- A running total, computed once per line and then only appended to.
CREATE TABLE revenue_running (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  revenue_to_date INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO revenue_running (order_id, line_no, revenue_to_date)
SELECT ol.order_id,
       ROW_NUMBER() OVER (PARTITION BY ol.order_id ORDER BY ol.id),
       SUM(ol.quantity * ol.unit_price) OVER (
         PARTITION BY ol.order_id
         ORDER BY ol.id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
FROM order_lines ol;

SELECT order_id, line_no, revenue_to_date
FROM revenue_running
WHERE order_id = 42
ORDER BY line_no;

The window function with a running frame is the important part: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW turns an aggregate into a cumulative total, so the per-order running revenue can be computed in one pass instead of a correlated subquery per row.

Beyond that, the interview answer is to identify what changes and when. A revenue-by-day report recomputes only today's partition, so a daily job can append it. A report that needs a year-to-date figure per order can be precomputed once, because an order's total does not change after it is placed. A slowly-changing dimension, such as a customer's region, does change — so it gets a versioned table and a lookup on the date, rather than a join to the current value, which would silently rewrite last year's numbers.

The detail that separates a good answer from a recited one is the trade-off. Precomputation trades freshness for speed and storage, and it needs a backfill plan and a way to rebuild when the definition changes. Cache invalidation is the real cost of moving work out of the query, and saying so out loud is what makes the answer sound like engineering rather than a list of techniques.