← Fannie Mae Interview Insights
Start by defining aggregate functions and their common purpose, then explain each function's specific behavior and output. Highlight key differences such as data type handling, null treatment, and use cases, and connect them to data science tasks like exploratory analysis and feature engineering.
Pro tip: Mention that COUNT(*) counts all rows including nulls, while COUNT(column) ignores nulls, and that AVG ignores nulls, which can lead to misleading results if not handled. Also note that these functions can be combined with GROUP BY and window functions for advanced analytics.
Explain that aggregate functions perform calculations on a set of rows and return a single value, often used with GROUP BY.
Briefly state what COUNT, SUM, AVG, MIN, and MAX do: COUNT counts rows or non-null values, SUM adds numeric values, AVG computes the mean, MIN and MAX find the smallest and largest values.
Discuss differences in data types (e.g., SUM/AVG require numeric, MIN/MAX work on various types), null handling (COUNT(column) ignores nulls, AVG ignores nulls), and return types.
Give examples of how these functions are used in data science, such as summarizing data, handling missing values, and creating features.
Note that these functions can be used with GROUP BY, HAVING, and window functions for more complex analysis.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
I used the Employees and Departments tables they gave me as examples, which felt natural.
Start by defining each join type in terms of which rows are retained from the left and right tables, then illustrate each with a concrete, business-relevant use case from a data science context. Emphasize how the choice of join impacts data completeness and analytical outcomes, especially in financial or risk modeling scenarios.
Pro tip: Mention that FULL OUTER JOINs are often avoided in production due to performance and data quality issues, but can be valuable for data reconciliation—a common need in financial institutions like Fannie Mae.
Explain that INNER JOIN returns only rows with matching keys in both tables. Use a use-case like joining loan applications with credit scores to analyze approved loans with known creditworthiness.
Explain that LEFT JOIN returns all rows from the left table and matching rows from the right, with NULLs for non-matches. Use a use-case like retaining all customers and their optional transaction history to analyze customer behavior.
Explain that RIGHT JOIN returns all rows from the right table and matching rows from the left, with NULLs for non-matches. Use a use-case like ensuring all products are listed even if they have no sales, to identify underperforming items.
Explain that FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match. Use a use-case like reconciling two data sources (e.g., internal loan records vs. external credit reports) to find discrepancies.
Briefly recap the differences and highlight how choosing the right join affects data integrity and insights, which is critical for data science at Fannie Mae.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Said UNION deduplicates and UNION ALL doesn't, so UNION ALL is faster when you know there are no dupes or you don't care.
Start by clearly defining UNION and UNION ALL in terms of set operations and duplicate handling. Then explain the performance implications, especially for large datasets, and give a concrete example of when to use each. Finally, tie it back to a data science context, such as combining datasets from different sources or incremental data loads.
Pro tip: Mention that UNION ALL is generally faster because it doesn't require a distinct sort or hash operation, but always verify data quality assumptions to avoid unintended duplicates. In big data environments like Spark, this difference can significantly impact job runtime and cost.
Explain that UNION combines results from two queries and removes duplicates, while UNION ALL combines results and keeps all duplicates.
Highlight that UNION requires additional processing to eliminate duplicates, which can be expensive on large datasets, whereas UNION ALL is more efficient.
Give examples: use UNION when you need a distinct set of records, such as merging customer lists from different systems; use UNION ALL when duplicates are acceptable or when you know there are no duplicates, like appending daily logs.
Connect to scenarios like combining training data from multiple sources, where duplicates might skew analysis, or concatenating incremental data where duplicates are expected and handled later.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This is where the interview got more interesting.
Start by defining a window function as a calculation across a set of rows related to the current row, without collapsing them. Then contrast with regular aggregation, which reduces rows into groups. Use a concrete example to illustrate the difference, especially in a data science context like calculating running totals or moving averages.
Pro tip: Mention that window functions are often more efficient than self-joins or subqueries for ranking and cumulative calculations, and highlight their use in time-series analysis—a common need in financial data at Fannie Mae.
Explain that a window function performs a calculation across a set of table rows that are somehow related to the current row, using an OVER clause to define the window.
Describe regular aggregation as using GROUP BY to collapse rows into summary rows, returning one row per group.
Emphasize that window functions retain all original rows, while aggregation reduces the number of rows.
Give a SQL example, such as calculating a running total or rank, to show how window functions work and how they differ from aggregation.
Connect to practical applications like calculating moving averages, cumulative sums, or rankings within groups, which are common in financial analytics.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Views don't store data so they're always fresh but can be slow if the underlying query is complex.
Start by defining what a SQL view and a physical table are, then compare them across dimensions like storage, performance, maintenance, and security. Use a structured comparison to highlight trade-offs, and conclude with guidance on when to use each in a data science context, especially for a regulated environment like Fannie Mae.
Pro tip: Emphasize that views are not just for convenience but can enforce security and simplify complex joins, while physical tables are essential for performance-critical operations; mention that in many data science workflows, a hybrid approach (e.g., materialized views) is often optimal.
Briefly explain that a view is a virtual table based on a SQL query, storing no data, while a physical table stores data on disk.
Discuss that views save storage but may have slower query performance due to underlying query execution, whereas tables consume storage but offer faster read access, especially with indexes.
Highlight that views automatically reflect changes in base tables and are easy to modify, while tables require ETL processes to update and schema changes can be more complex.
Explain that views can restrict access to specific rows/columns, providing a security layer, while tables require direct permissions and may expose sensitive data.
Summarize when to use each: views for abstraction, security, and simplifying complex queries; tables for performance, large datasets, and when data needs to be persisted.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Explain that you can use SQL window functions like ROW_NUMBER() to assign a unique rank to each row within a partition of duplicate values, then filter the result set to only include rows where the rank equals 1. Emphasize that this approach does not modify the underlying table, preserving data integrity while presenting a deduplicated view.
Pro tip: Mention that the choice of which duplicate to keep (e.g., the most recent based on a timestamp) can be controlled by the ORDER BY clause inside the window function, demonstrating awareness of business rules and data quality.
Confirm that the requirement is to hide duplicates in the query output, not to delete them from the table, and discuss whether any specific duplicate should be retained (e.g., latest record).
Select an appropriate SQL method such as ROW_NUMBER(), RANK(), or DISTINCT, considering performance and the need to keep a specific row.
Use ROW_NUMBER() OVER (PARTITION BY columns ORDER BY criteria) to assign a row number, then wrap it in a subquery or CTE and filter for row_number = 1.
Test the query to ensure duplicates are hidden as expected, and explain how the approach preserves the original table and can be adapted for different deduplication rules.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying the database system (e.g., SQL Server, PostgreSQL) and table structure, then discuss methods like using ROW_NUMBER() with a CTE to identify and delete duplicates, or creating a new table with DISTINCT and swapping. Emphasize the importance of backing up data and testing on a copy before executing deletions.
Pro tip: Always consider the impact on indexes, constraints, and foreign keys; mention that you would verify the row count before and after deletion to ensure correctness. Also, highlight that in production, you might use a transaction to allow rollback if something goes wrong.
Ask about the database system, table size, presence of primary keys, and whether duplicates are exact matches or based on specific columns. This determines the best approach.
Select an appropriate technique: using ROW_NUMBER() with a CTE to delete rows where row number > 1, or creating a new table with DISTINCT and renaming. Consider performance and locking implications.
Back up the table or work within a transaction. Test the query on a subset or a copy to ensure it removes only duplicates and preserves the correct rows.
Run the deletion, then verify the row count and check that no duplicates remain. Commit the transaction if all is well.
Drop any temporary tables, rebuild indexes if necessary, and document the process for future reference.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying the problem: define the employee table schema, handle ties, and specify the SQL dialect. Then present a robust solution using either a window function (DENSE_RANK) or a correlated subquery, and discuss trade-offs.
Pro tip: Always mention how you would handle ties and NULLs, and note that window functions are generally more efficient and readable than subqueries for this task.
Ask about the table schema, whether ties should be considered, and which SQL dialect is expected. Confirm if the Nth highest should be distinct or not.
Decide between using a window function (e.g., DENSE_RANK) or a correlated subquery. Consider performance and readability.
Construct the SQL query, ensuring it handles edge cases like N greater than the number of distinct salaries, and NULL values.
Walk through the query step by step, explaining how it identifies the Nth highest salary and why it works.
Mention other possible solutions (e.g., using LIMIT/OFFSET) and their limitations, and discuss performance considerations.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Talked about indexing, avoiding SELECT *, filtering early, not doing functions on indexed columns in WHERE clauses, and using EXISTS instead of IN for subqueries in some cases.
Start by clarifying the context: the type of database, data volume, and query patterns. Then structure your answer around three layers: query design, indexing, and database engine features, emphasizing trade-offs and measurement.
Pro tip: Always mention that you measure first using EXPLAIN plans and profiling, because premature optimization can hurt. Also, highlight that in data science, reducing data scanned is often more impactful than micro-optimizing SQL syntax.
Ask about the database system, data size, and whether the query is ad-hoc or part of a pipeline. This shows you tailor solutions to context.
Discuss selecting only needed columns, avoiding SELECT *, filtering early, and using set-based operations instead of cursors or loops.
Explain how proper indexes (e.g., composite, covering) and partitioning can drastically reduce data scanned. Mention trade-offs like write overhead.
Bring up query hints, materialized views, temporary tables, and parallel execution where supported. Also mention avoiding functions on indexed columns in WHERE clauses.
Emphasize using EXPLAIN plans, profiling tools, and benchmarking to validate improvements and avoid regressions.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
merge is the most flexible, works like a SQL join on specific columns.
Start by defining each operation in terms of its primary purpose: merge for flexible column-based joins, join for index-based joins, and concat for stacking along an axis. Then explain when to use each based on the structure of your data and the desired output, emphasizing that merge is the most versatile for SQL-like operations.
Pro tip: Mention that join is essentially a convenience wrapper around merge that defaults to index-based joining, and that concat can handle both row and column binding but does not align on keys—this shows deep understanding and helps avoid common pitfalls.
Explain that merge combines DataFrames based on common columns or indices, similar to SQL joins, and supports various join types (inner, outer, left, right).
Describe join as a method that combines DataFrames based on their indices by default, with an optional 'on' parameter for column-based joins, and is a convenient shortcut for index-based merging.
Explain that concat stacks DataFrames along a particular axis (rows or columns) without aligning on keys, useful for appending or adding columns when indices are already aligned.
Highlight when to use each: merge for complex column-based joins, join for quick index-based joins, and concat for simple stacking or appending operations.
Provide a brief example scenario (e.g., combining customer and transaction data) to illustrate the choice between these functions.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.