← Rbcroyalbank Interview Insights
Start by defining both structures clearly, emphasizing that a Series is a one-dimensional labeled array while a DataFrame is a two-dimensional labeled table. Then compare their indexing capabilities and typical use cases, using concrete examples to illustrate when each is appropriate. Conclude by tying the answer back to practical data manipulation scenarios, especially in a financial context like RBC.
Pro tip: Mention that a DataFrame can be thought of as a dictionary of Series sharing the same index, which demonstrates deep understanding. Also, highlight that many pandas operations return Series from DataFrames, so knowing when to use each is crucial for efficient code.
Clearly state that a Series is a 1D labeled array capable of holding any data type, and a DataFrame is a 2D labeled data structure with columns of potentially different types.
Explain that Series has a single index, while DataFrame has both row and column indexes. Mention that DataFrame indexing allows for label-based and integer-based selection across two axes.
Describe when to use each: Series for a single variable or column, DataFrame for tabular data with multiple variables. Give examples like time series (Series) vs. financial records (DataFrame).
Note that a DataFrame column is a Series, and you can extract Series from DataFrames or combine Series to form a DataFrame. This shows how they interconnect.
Provide a concise example from a software engineering or finance context, such as using a Series for daily stock prices and a DataFrame for multiple stocks with dates.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by defining the core difference: WHERE filters rows before grouping, while HAVING filters groups after aggregation. Then explain the logical order of SQL execution to clarify when each is applied, and explicitly state that aggregate functions can only be used in HAVING (and SELECT), not in WHERE. Finally, provide a simple example to illustrate the distinction.
Pro tip: Mention that some databases allow HAVING without GROUP BY (treating the whole result as one group), but this is non-standard and best avoided. Also, note that filtering early with WHERE improves performance by reducing the data before aggregation.
State that WHERE filters individual rows before any grouping, while HAVING filters groups after aggregation. Emphasize that WHERE is applied to raw data, HAVING to summarized results.
Describe the SQL query processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This clarifies that WHERE runs before GROUP BY and HAVING runs after.
Clarify that aggregate functions (e.g., SUM, COUNT, AVG) cannot be used in WHERE because they operate on groups, which don't exist yet. They can be used in HAVING and in the SELECT list.
Give a simple SQL example, such as: SELECT department, AVG(salary) FROM employees WHERE salary > 50000 GROUP BY department HAVING AVG(salary) > 60000. Explain how WHERE filters rows and HAVING filters groups.
Note that using WHERE to filter rows before grouping is more efficient than using HAVING, because it reduces the number of rows that need to be aggregated.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This one slowed me down more than I'd like to admit.
Start by clarifying the definition of a duplicate: rows with identical account_id, transaction_id, amount, and timestamp, ignoring ingest_id. Then write a GROUP BY query on those four columns with HAVING COUNT(*) > 1 to return the duplicate groups and their counts. Optionally, show how to retrieve the full rows using a window function or self-join.
Pro tip: Mention that ingest_id is likely a surrogate key and should be excluded from the duplicate check, and discuss how to handle NULLs or timestamps with different precision to avoid false positives.
Confirm that duplicates are based on account_id, transaction_id, amount, and timestamp, and that ingest_id is not part of the business key.
Use GROUP BY on the four columns and HAVING COUNT(*) > 1 to return each duplicate group with its count.
If needed, use a window function like COUNT(*) OVER (PARTITION BY ...) or a self-join to return all columns for duplicate rows.
Address NULL handling, timestamp precision, and indexing strategies to optimize the query.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.