Source: Problem prompts adapted from Ace the Data Science Interview — Chapter 8 Easy set (Huo & Singh). Seeds, SQLite solutions, and model answers here are original for this course.
Thirteen query drills plus eight short design/systems write-ups. Each SQL exercise ships its own seed database (≥15 rows per table where the prompt allows). Open the Data tab, write only a SELECT / WITH … SELECT, then Check . Runtime is SQLite via the wrap-seed runner — use CASE instead of Postgres IF, strftime / date() instead of DATE_TRUNC / INTERVAL when you need date math, and prefer LEFT JOIN over RIGHT JOIN.
Hints: restating the metric (“CTR = clicks / impressions in 2019”) before coding beats jumping into GROUP BY. Column names match the prompt schemas below.
Query problems (8.1–8.13)
Design & concepts (8.14–8.21)
Written answers — use the keywords when shown, then compare to the model answer.
8.14 Facebook
Using “view” and “query”, explain:
What is a database view? Name advantages views can have compared with base tables.
Minimum 3 words. Your answer must include: “view”, “query”.
0 words / 3 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.15 Expedia
Using “index” and “write”, explain:
If most queries are UPDATE/INSERT/DELETE, how does that affect indexing? What if most queries are SELECT/JOIN instead?
Minimum 45 words. Your answer must include: “index”, “write”.
0 words / 45 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.16 Microsoft
Using “unique” and “null”, explain:
What is a primary key? What makes a good primary key?
Minimum 35 words. Your answer must include: “unique”, “null”.
0 words / 35 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.17 Amazon
Using “ACID” and “schema”, explain:
Compare advantages and disadvantages of relational databases versus NoSQL stores.
Minimum 50 words. Your answer must include: “ACID”, “schema”.
0 words / 50 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.18 Capital One
Using “map” and “random”, explain:
At a high level, how would a MapReduce job implement a shuffle that randomly reorders a dataset?
Minimum 3 words. Your answer must include: “map”, “random”.
0 words / 3 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.19 Amazon
Using “WHERE” and “HAVING”, explain:
Name one major similarity and one major difference between WHERE and HAVING in SQL.
Minimum 30 words. Your answer must include: “WHERE”, “HAVING”.
0 words / 30 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.20 KPMG
Using “foreign key” and “primary”, explain:
What is a foreign key, and how does it relate to a primary key?
Minimum 35 words. Your answer must include: “foreign key”, “primary”.
0 words / 35 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
8.21 Microsoft
Using “clustered” and “pointer”, explain:
Compare clustered and non-clustered indexes.
Minimum 45 words. Your answer must include: “clustered”, “pointer”.
0 words / 45 min
Check requirements Check later
Meet the writing requirements, then you can open the model answer.
Next: SQL problems — Medium for retention, rolling windows, co-purchase, and MapReduce design prompts.