Quick-Fire SQL and Python Fundamentals: GROUP BY, JOINs, HAVING, Lists vs Tuples

A recruiter screen for a business intelligence role opens by asking you to rate your own SQL and Python skills on a scale of 1 to 5, then moves into a quick-fire round of concept questions on both. Answer each question in a few sentences, as you would out loud, with a short example where it helps. ### Clarifying Questions - Which SQL dialect does the team use day to day? - Should the answers stay conceptual, or would a line of code for each be welcome? - For the question about packages, is the interest in analysis and reporting work, or also in production code? ### Part 1 — SQL concepts 1. What does `GROUP BY` do? 2. How many kinds of `JOIN` are there, and what does each return? 3. What does `HAVING` do? ```hint Order of operations For `GROUP BY` and `HAVING`, think about when rows are filtered relative to when groups are formed. ``` ```hint Rows without a match The join types differ in what happens to a row that has no matching row on the other side. ``` #### What This Part Should Cover - `GROUP BY` collapsing rows into one row per group, with aggregates and the rule for non-aggregated columns - Each join type described by which unmatched rows it keeps, plus the cross join - `HAVING` filtering groups after aggregation, contrasted with `WHERE` ### Part 2 — Python concepts 1. Explain what a function is. 2. Which packages do you use most often? 3. Do you know NumPy? 4. What are a list and a tuple, and how do they differ? ```hint After creation For lists and tuples, ask what each lets you do once it exists, and where Python accepts only one of them. ``` ```hint Why a separate array type For NumPy, think about what changes when a million numbers live in one typed block of memory instead of a Python list. ``` #### What This Part Should Cover - A function's definition, parameters, return value and scope, with a tiny example - Packages named together with what each is used for in analysis work - NumPy's array type, vectorized operations and broadcasting, and how it relates to pandas - List versus tuple: mutability, hashability and typical uses ### What a Strong Answer Covers - Correct, concise definitions that fit a quick-fire format - One concrete example per concept rather than an abstract description - The common traps: `WHERE` versus `HAVING`, `NULL` keys in joins, row multiplication in one-to-many joins, and tuples that are immutable but not hashable - A self-rating on the 1-to-5 scale that is calibrated and backed by examples of real work ### Follow-up Questions - Given an `orders` table with `customer_id` and `amount`, return the customers whose total amount exceeds 1,000. Where does the filter go, and why can't it go in `WHERE`? - A `LEFT JOIN` returns fewer rows than expected after you add a `WHERE` condition on a column of the right-hand table. Why, and how do you fix it? - Why can a tuple be a dictionary key when a list cannot, and when does a tuple still fail as a key? - Why is summing a NumPy array much faster than looping over a Python list of the same numbers?