← Rbcroyalbank Interview Insights
Start by defining each structure clearly: a Series is a one-dimensional labeled array, while a DataFrame is a two-dimensional labeled data structure with columns of potentially different types. Then highlight the key differences in dimensionality, mutability, and use cases, and provide a concrete example to illustrate when you would use each.
Pro tip: Mention that a DataFrame can be thought of as a dictionary of Series objects (each column is a Series), which demonstrates a deeper understanding of pandas internals and how operations propagate.
Describe a Series as a one-dimensional labeled array capable of holding any data type, with an index for labeling.
Describe a DataFrame as a two-dimensional labeled data structure with columns of potentially different types, akin to a spreadsheet or SQL table.
Explain that Series is 1D (like a single column) while DataFrame is 2D (like a table with rows and columns).
Highlight that DataFrame supports column-wise operations and heterogeneous data, while Series is used for single-variable analysis and operations.
Give a brief example, such as a Series representing temperatures over days, and a DataFrame representing temperatures and humidity across multiple cities.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Blanked for a second on phrasing it cleanly.
Start by clearly defining WHERE as a row-level filter applied before grouping, and HAVING as a group-level filter applied after aggregation. Then explain their interaction with GROUP BY and aggregates, using a concrete SQL example to illustrate when each is valid. Finally, mention performance considerations and best practices.
Pro tip: Emphasize that filtering with WHERE before grouping reduces the data set early, which is more efficient than filtering after aggregation with HAVING. This shows you understand query optimization, not just syntax.
Explain that WHERE filters individual rows before any grouping or aggregation occurs. It cannot contain aggregate functions.
Explain that HAVING filters groups after GROUP BY and aggregation. It can use aggregate functions like COUNT, SUM, AVG.
Describe the logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This clarifies why WHERE cannot use aggregates and HAVING can.
Give a SQL query that uses both WHERE and HAVING, e.g., SELECT department, AVG(salary) FROM employees WHERE status = 'active' GROUP BY department HAVING AVG(salary) > 50000;
Mention that WHERE is more efficient for row-level filtering because it reduces data before grouping. Use HAVING only for aggregate conditions.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
GROUP BY email, COUNT(*) as dup_count, then HAVING COUNT(*) > 1.
Use a GROUP BY on the email column with a HAVING clause to filter groups having COUNT(*) > 1. Then select the email and the count to return duplicates and their frequency.
Pro tip: Mention that you would also consider case-insensitivity and trimming whitespace if the data might have inconsistencies, and discuss indexing on the email column for performance.
Confirm the table name (e.g., users) and the column containing emails (e.g., email).
Use GROUP BY email to aggregate rows with the same email address.
Apply COUNT(*) to count how many times each email appears.
Add a HAVING clause with COUNT(*) > 1 to keep only emails that appear more than once.
In the SELECT clause, include the email and the count (aliased, e.g., AS duplicate_count).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying the schema and what 'duplicated' means (e.g., exact email match, case-insensitive). Then present a SQL solution using a subquery or window function to return all columns for rows whose email appears more than once, and discuss trade-offs between approaches.
Pro tip: Mention that you'd verify the query's performance on large datasets and consider indexing the email column; also note that in production you might need to handle NULLs or case sensitivity based on business rules.
Ask about the table structure, whether duplicates are case-sensitive, and if NULL emails should be considered. Confirm the expected output format.
Decide between using a subquery with GROUP BY and HAVING COUNT(*) > 1, a self-join, or a window function like COUNT(*) OVER (PARTITION BY email).
Construct the SQL statement to select all columns from the table where the email is in the list of duplicated emails, ensuring correct filtering.
Compare performance and readability of different methods, and mention indexing or other optimizations for large datasets.
Explain how you would test the query with sample data, including edge cases like multiple duplicates and NULL values.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.