Question library
Questions from real interviews. Drill any of them by voice in a couple of minutes, with an interviewer, a score and feedback.
Explain the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN.JuniorImagine a query against a large table is running very slowly. Where would you start investigating?JuniorWhat are a primary key and a foreign key? And how would you model a many-to-many relationship, for example students and courses?JuniorWhat kinds of JOIN are there in SQL, and how do they differ from each other?JuniorWhat types of JOIN are there in SQL, and how do they differ? For example, how would you find users who don't have a single order?JuniorWhat's the difference between WHERE and HAVING in SQL?JuniorWhat's the difference between the window functions ROW_NUMBER, RANK, and DENSE_RANK?JuniorA query that used to run in milliseconds now takes seconds after the table grew to tens of millions of rows. How would you investigate, and what can you do about it?Mid-levelAn analytical query against Postgres that used to run in a couple of seconds now takes ten minutes. How would you investigate, and what can you do about it?Mid-levelHow do you use SQL in your work as a tester? And tell me how you'd write a query that finds the users who placed more than one order in the last month.Mid-levelIn which cases would you deliberately denormalize a database schema, and what's the price you pay for it?Mid-levelThe orders list page has started taking several seconds to load, and in the logs you see hundreds of near-identical SQL queries per HTTP request. What's going on, and how would you fix it?Mid-levelYou join the orders table with the payments table, and the total revenue in the report suddenly grows almost threefold. How would you look for the cause, and how would you fix it?Mid-levelYou need to get each user's most recent order, and some users have several orders with the same timestamp. How would you write the query, and which function would you choose?Mid-levelAfter joining orders with the promo codes table, the revenue total in the report came out fifteen percent higher than the accounting team's number. How would you look for the cause, and how would you fix the query?SeniorTell me about a time you denormalized a data model for the sake of performance. What price did you have to pay for it?SeniorYou have a query with window functions over a billion-row events table, and it takes forty minutes. How would you investigate, and what would you optimize?SeniorHow would you set up testing for a data migration from an old billing system to a new one?Senior