Tell me about a SQL project you worked on. What was the data model, and what was the hardest query in it?
Use the same structure that you use for any project, and put the data model and the hard query at the front of it.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE subscriptions (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
plan_code VARCHAR(40) NOT NULL,
started_on TEXT NOT NULL,
ended_on TEXT,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
So what should the answer sound like? Five parts.
First, the situation. What the system was, how much data it held, and what it had to do. The scale is the most interesting part of it, because it decides which queries were hard.
Second, the data model. The main tables, how they were linked, and the one decision that shaped everything else. Say why that decision was made and not the other one.
Third, the hardest query. This is the part that the interviewer is really asking for. Give the question, say what made it hard, and say how you solved it. A query that had to work over ten million rows without stopping the site is interesting, and a query that gave the wrong answer after a change of requirement is even more interesting.
Fourth, the trade-off you accepted. Every data model has one. You denormalised something and you now have to keep it in step, or you kept it normalised and you added a summary table, or you chose the strict rules and some writes now fail. Saying which one you chose and why is the mark of somebody who has built something.
Fifth, the result. The number that changed, and how you measured it.
The next question in this topic asks about the part that people find hardest.