Deutsche Bank Interview Questions

This is a classic heap / priority queue problem. We simulate the process by always picking the largest pile, replacing it with floor(sqrt(pile)) , and r...

• You are given an integer array gifts denoting the number of gifts in various piles. Every second: * Choose the pile with the maximum number of gifts. * Leave behind the floor of the square root of the number of gifts in the pile; take the rest. * Return the number of gifts remaining after k seconds.

  This is a classic heap / priority queue problem. We simulate the process by always picking the largest pile, replacing it with floor(sqrt(pile)) , and repeating for k seconds. Time complexity : O(k log n) — each iteration pops and pushes in O(log n). Space complexity : O(n) for the heap. Key insight : math.isqrt() (integer square root) is preferred over int(math.sqrt()) to avoid floating point precision issues. In a data engineering context, priority queues appear when scheduling tasks by priori

• SQL Question: Calculate how many hours each user was active during the day based on session transitions. Table: customer_state_log data = [ ('c001', 1,'07:00:00'), ('c001', 0,'09:30:00'), ('c001', 1,'12:00:00'), ('c001', 0,'14:30:00'), ('c002', 1,'08:00:00'), ('c002', 0,'09:30:00'), ('c002', 1,'11:00:00'), ('c002', 0,'12:30:00'), ('c002', 1,'15:00:00'), ('c002', 0,'16:30:00'), ('c003', 1,'09:00:00'), ('c003', 0,'10:30:00'), ('c004', 1,'10:00:00') ]

  This is a session pairing problem. State 1 = session start, state 0 = session end. We need to pair each start with its next end for the same user. Key technique : Matching start end pairs using ROW NUMBER() partitioned by (customer id, state) ensures the 1st start pairs with the 1st end, 2nd start with 2nd end, etc. Note for c004 : It has only a start event (no end), so it is excluded from the active hours — this is intentional; you could handle unclosed sessions separately by joining with CURRE

• SQL Question: Find products that have never been purchased.

  This is a classic anti join pattern. We need all products from the products table that have no matching row in the orders/purchases table. Best practice : Prefer NOT EXISTS over NOT IN when the subquery column may contain NULL values, since NULL in a NOT IN list causes the entire expression to return UNKNOWN , filtering out all rows unexpectedly. In a data engineering context, this pattern is commonly used in data quality checks — e.g., finding dimension table entries with no corresponding fact

• Spark / PySpark Questions: * How would you deploy a Spark job on GCP Dataproc? * How does changing the number of partitions affect performance? * Explain the significance of spark.executor.memory and how it impacts job execution. * Write a PySpark job to read CSV from GCS, process, and write Parquet back. * Write a Python program to find the maximum number in a list. * Given a DataFrame sales with columns region, salesperson, sales_amount, calculate cumulative sales per salesperson ordered by date using window functions. * Follow-ups: rolling average, partitioning by multiple columns. * Convert string dates in format "dd/MM/yyyy" to "yyyy-MM-dd", replacing invalid entries with current date.

  Deploying on GCP Dataproc : Submit via gcloud dataproc jobs submit pyspark cluster=<name region=<region gs://bucket/job.py . Use initialization actions for dependencies and autoscaling policies for dynamic resource allocation. Partition count impact : Too few partitions → fewer parallel tasks, underutilized cores. Too many → high task scheduling overhead, tiny shuffle blocks. Rule of thumb: 2–4 partitions per CPU core. Use repartition() to increase or coalesce() to decrease. spark.executor.memor