← Akuna Capital Interview Insights
Thought I had this cold but the multiple choice options were sneaky.
Start by defining each join type in terms of which rows are preserved from which tables, then explain how they differ when there are unmatched rows. Use a concrete example with two small tables to illustrate when the result sets diverge, and mention practical scenarios where each join is appropriate.
Pro tip: Emphasize that LEFT JOIN and RIGHT JOIN are functionally equivalent if you swap the table order, and that the choice often comes down to readability and intent. Also note that filtering on the right table in a LEFT JOIN's WHERE clause can accidentally turn it into an INNER JOIN, a common pitfall.
Clearly state what INNER JOIN, LEFT JOIN, and RIGHT JOIN do in terms of matching rows and preserving unmatched rows from one or both tables.
Describe how the result sets differ when there are unmatched rows: INNER returns only matches, LEFT returns all left rows plus matches, RIGHT returns all right rows plus matches.
Use two small tables (e.g., Customers and Orders) with some unmatched rows to show exactly which rows appear in each join's output.
Mention practical scenarios: INNER for strict matches, LEFT for preserving all left records (e.g., all customers with their orders if any), RIGHT for the opposite, and note that RIGHT is often avoided in favor of LEFT with swapped tables.
Warn about filtering on the non-preserved table in the WHERE clause, which can negate the outer join effect, and about NULL handling in join conditions.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by defining WHERE as a row-level filter applied before grouping, and HAVING as a group-level filter applied after aggregation. Then explain that HAVING is necessary when filtering on aggregated results or when the filter condition depends on the result of an aggregate function. Use a concrete SQL example to illustrate the difference and clarify execution order.
Pro tip: Mention that in many databases, you can use HAVING without GROUP BY, but it's rarely good practice; also note that pushing conditions to WHERE when possible improves performance by reducing the number of rows before aggregation.
Explain that WHERE filters individual rows before any grouping or aggregation occurs. It cannot contain aggregate functions.
Explain that HAVING filters groups after aggregation. It is typically used with GROUP BY and can contain aggregate functions.
Describe the logical query processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This clarifies why WHERE cannot use aggregates and HAVING can.
Give examples where HAVING is required, such as filtering groups based on COUNT, SUM, AVG, etc., or when the filter condition depends on an aggregate.
Mention that filtering as early as possible with WHERE is more efficient, and only use HAVING when necessary. Also note that some databases allow HAVING without GROUP BY, but it's non-standard.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by explaining the three-valued logic (TRUE, FALSE, UNKNOWN) that SQL uses for NULLs, then contrast how comparisons and aggregates handle NULLs. Use concrete examples to illustrate common pitfalls and how to work around them.
Pro tip: Mention that NULL handling varies slightly across databases (e.g., Oracle treats empty strings as NULL, while PostgreSQL does not), and that understanding these nuances is crucial for writing portable SQL.
Explain that NULL represents an unknown value, and any comparison with NULL yields UNKNOWN, not TRUE or FALSE. This affects filtering and joins.
Describe how =, <>, <, >, etc., return UNKNOWN when either operand is NULL. To test for NULL, use IS NULL or IS NOT NULL.
Explain that most aggregate functions (SUM, AVG, COUNT, MAX, MIN) ignore NULLs, except COUNT(*). This can lead to unexpected results if not handled.
Discuss techniques like COALESCE, ISNULL, or NULLIF to replace NULLs with default values, and how to use them in queries to avoid logic errors.
Highlight that NULLs can cause issues in joins, constraints, and indexing. Consider trade-offs between using NULLs vs. default values in schema design.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
The tie-breaking behavior is where people slip up and I almost did.
Start by defining both functions and their core purpose: assigning numbers to rows within a partition. Then contrast their tie-handling behavior: ROW_NUMBER gives unique sequential numbers even for ties, while RANK assigns the same rank to tied rows and leaves gaps. Use a concrete example to illustrate, and mention when to use each.
Pro tip: Mention that ROW_NUMBER is non-deterministic for ties unless you specify a tie-breaker in the ORDER BY, which is crucial for reproducible results in production. Also, note that DENSE_RANK is often a better choice when you need consecutive ranks without gaps.
Explain that ROW_NUMBER assigns a unique sequential integer to each row within a partition, starting at 1, based on the ORDER BY clause. Even if rows have identical values, they receive different numbers.
Explain that RANK assigns the same rank to rows with identical values in the ORDER BY, but leaves gaps in the ranking sequence after ties. For example, if two rows tie for rank 1, the next row gets rank 3.
Provide a simple dataset (e.g., scores: 100, 100, 90) and show the output of both functions. ROW_NUMBER would give 1,2,3; RANK would give 1,1,3.
Highlight that ROW_NUMBER is useful for deduplication or pagination where you need exactly one row per group, while RANK is useful for ranking with ties (e.g., leaderboards).
Briefly mention DENSE_RANK (no gaps) and NTILE, and when each is appropriate. Also note performance considerations: ROW_NUMBER can be more efficient as it doesn't need to check for ties.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying that SQL is declarative, so the logical processing order differs from the written order. Then walk through the logical order step by step, explaining why each clause is processed in that sequence and how it affects query results.
Pro tip: Emphasize that understanding logical processing order helps debug issues like using SELECT aliases in WHERE (not allowed) or why HAVING is needed for aggregated filters. This shows you grasp SQL semantics beyond syntax.
Explain that SQL is declarative: you specify what you want, not how to get it. The database optimizer determines the actual execution plan, but there is a logical order for evaluating clauses.
State the logical processing order: FROM (and JOINs), WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT/OFFSET. This is the sequence in which the clauses are conceptually evaluated.
Briefly describe what each clause does: FROM assembles data, WHERE filters rows, GROUP BY aggregates, HAVING filters groups, SELECT computes expressions, ORDER BY sorts, LIMIT restricts output.
Discuss why this order matters: e.g., you can't use SELECT aliases in WHERE because SELECT is processed later; HAVING can filter on aggregates because it runs after GROUP BY; ORDER BY can use SELECT aliases because it runs after SELECT.
Give an example or two where this knowledge helps, such as debugging a query or optimizing performance by understanding which filters are applied when.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.