American Express Interview Questions

This is a behavioral/context setting question asked to understand the candidate's domain relevance and communication skills. How to structure your answe...

• Can you tell me more about the business problem you are solving in current project.

  This is a behavioral/context setting question asked to understand the candidate's domain relevance and communication skills. How to structure your answer: Problem statement : Describe the business challenge in plain terms (e.g., reducing customer churn, improving fraud detection, accelerating reporting) Data involved : Mention data sources, volume, and frequency (e.g., 50M daily transactions from 3 upstream systems) Your role : Clarify whether you were the lead engineer, contributor, or architec

• Let's say we have a table with 5 columns, and it's a huge table. I need to check from this table. There is a record which is a duplicate, and when I say duplicate it means all the five columns are identical.

  Finding fully duplicate rows (where all columns match) in a large table requires comparing every column. Approach 1: GROUP BY all columns This returns all groups of rows where every column value is identical and the group has more than one row. Approach 2: Using ROW NUMBER() window function This returns the actual duplicate rows (second and subsequent occurrences). Performance considerations for large tables: Ensure statistics are up to date so the query planner can choose an efficient plan Cons

• Firstly write a SQL query for it (finding duplicates where all five columns are identical).

  Building on the duplicate detection problem — here is a production ready SQL query: Query to identify duplicate records: Query to retrieve all duplicate rows with a row number: To delete duplicates keeping one copy: Note: Use ctid in PostgreSQL instead of rowid . The MIN(rowid) approach keeps one original and removes all exact duplicates, making it safe for deduplication workflows.

• Please write same logic in PySpark.

  Here is the PySpark equivalent for finding duplicate rows across all five columns: Method 1: Using groupBy + filter Method 2: Using Window + ROW NUMBER (returns actual duplicate rows) To deduplicate (keep one copy): dropDuplicates() is the most efficient built in method for this use case in PySpark — it leverages Spark's shuffle based deduplication under the hood and is equivalent to DISTINCT in SQL.

• What actually PARTITION BY does in window function.

  PARTITION BY in a window function divides the result set into logical groups (partitions) so that the window function is applied independently within each partition — similar to how GROUP BY creates groups, but without collapsing rows. Key distinction from GROUP BY: GROUP BY reduces rows — one row per group PARTITION BY preserves all rows — function result is added as a new column per row Example: Here PARTITION BY department means: RANK() restarts from 1 for each department AVG(salary) is compu

• Please tell me is there a direct API or function present in PySpark to find the duplicates.

  Yes, PySpark provides built in functions to detect and remove duplicates efficiently. 1. dropDuplicates() — remove duplicates (keep first occurrence) 2. distinct() — equivalent to SQL DISTINCT Difference between dropDuplicates() and distinct() : distinct() considers all columns and returns unique rows dropDuplicates(subset) lets you specify a subset of columns to define uniqueness while retaining other columns from the first occurrence 3. To COUNT duplicates (not just remove them): Performance t

• Write one new transformation like you have a dataframe with two columns now you need to create the new column which will be calculated based on col1 and col2 if both are same it should be TRUE else FALSE in PySpark.

  This uses withColumn with a conditional expression to derive a boolean comparison column. Output: Method 2: Direct boolean comparison (more concise) This directly returns a BooleanType column. Both methods produce the same result. Method 1 using when/otherwise is preferred when you want explicit True / False string literals or need to handle nulls explicitly with .isNull() checks.

• Write a Python code to check if there are consecutive odd numbers present or not in an array — if present return TRUE if not return FALSE, and it should check in circular order.

  This problem requires checking if any two adjacent elements (including the wrap around from last to first element) are both odd. Key logic: (i + 1) % n handles the circular wrap around (last element's neighbor is index 0) Check % 2 != 0 to detect odd numbers Return True immediately on first found pair (short circuit evaluation) Time complexity: O(n), Space complexity: O(1)