← Rbcroyalbank Interview Insights
Pretty basic but I fumbled the 'when would you use each' part a bit.
Start by defining both data structures clearly, emphasizing that 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 explain when to use each: Series for single-variable operations or when you need a simple indexed array, and DataFrame for tabular data with multiple variables. Finally, relate it to practical scenarios in software engineering, such as data preprocessing or feature engineering.
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. Also, highlight that many operations on DataFrames return Series, so knowing when to use each is crucial for efficient data manipulation.
Explain that a Series is 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.
Contrast their dimensionality: Series for 1D data, DataFrame for 2D. Give examples: Series for a single column of data, DataFrame for multiple columns.
Explain that Series is ideal for operations on a single variable, while DataFrame is used for tabular data analysis, filtering, grouping, and joining.
Connect to real-world scenarios like data preprocessing, feature engineering, or handling CSV files, emphasizing trade-offs in memory and performance.
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 filter applied to individual rows before any grouping, and HAVING as a filter applied to groups after aggregation. Then explain that WHERE is used with non-aggregated columns, while HAVING is used with aggregated results (e.g., SUM, COUNT). Finally, mention that both can be used together in a single query, with WHERE filtering rows first and HAVING filtering the resulting groups.
Pro tip: Mention that in most databases, you can't use aggregate functions in the WHERE clause, and that HAVING without GROUP BY is valid but rare—this shows depth. Also, note that for performance, filtering as early as possible with WHERE reduces the data that needs to be grouped, which is a common optimization.
Explain that WHERE filters rows before grouping and cannot contain aggregate functions. It is used to restrict the data set based on individual row conditions.
Explain that HAVING filters groups after grouping and is typically used with aggregate functions. It is applied to the results of GROUP BY.
State that WHERE is used for conditions on columns, while HAVING is used for conditions on aggregated values. Give a simple example: WHERE salary > 50000 vs HAVING AVG(salary) > 50000.
Describe how both can appear in the same query: WHERE filters rows first, then GROUP BY aggregates, then HAVING filters groups. This order is important for correctness and performance.
Conclude that you use WHERE when you need to filter individual rows, and HAVING when you need to filter based on aggregate results. Mention that HAVING can be used without GROUP BY but is uncommon.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
GROUP BY customer_email, then COUNT(*) aliased as duplicate_count, then HAVING COUNT(*) > 1.
Start by clarifying the table schema and any assumptions about data quality, then write a straightforward SQL query using GROUP BY and HAVING to count occurrences per email. After presenting the basic solution, discuss potential performance considerations and edge cases such as case sensitivity and null values.
Pro tip: Mention that you would first check if the email column is indexed and consider using a covering index to speed up the GROUP BY operation, especially if the table is large. Also, proactively ask about data cleanliness (e.g., trailing spaces, case variations) to show attention to detail.
Confirm the table schema, whether duplicates should be counted across all source systems or per system, and how to handle NULL or empty emails. Also ask about expected data volume and performance requirements.
Use SELECT customer_email, COUNT(*) AS duplicate_count FROM customer_events GROUP BY customer_email HAVING COUNT(*) > 1; to find emails appearing more than once.
Discuss handling case-insensitive emails (e.g., using LOWER(customer_email)), trimming whitespace, and excluding NULLs. Mention that if emails are stored with different cases, you may need to normalize before grouping.
Suggest indexing the customer_email column and consider if a subquery or window function might be more efficient for large datasets. Mention that COUNT(*) is generally faster than COUNT(column) if no NULLs are present.
Explain how you would test the query with sample data, including cases with no duplicates, multiple duplicates, and NULL emails. Also consider if the result should be ordered by count descending.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.