66 Degree Interview Questions

To keep only the latest row per Name , use a Window function with row number() ranked by descending Timestamp , then filter for rank 1. Alternative usin...

• PySpark: Remove duplicate rows based on Name, keeping the latest entry by Timestamp.

  To keep only the latest row per Name , use a Window function with row number() ranked by descending Timestamp , then filter for rank 1. Alternative using dropDuplicates (only works if you can sort first — not possible directly in PySpark): Key insight : dropDuplicates() keeps an arbitrary row among duplicates unless the DataFrame is sorted first, so the row number() approach is more reliable for production pipelines. Time complexity : O(n log n) due to the sort within each partition window.

• PySpark: Calculate average salary per department.

  This is a straightforward groupBy + aggregation operation in PySpark: Output : Performance tip : If the department column has very few distinct values (low cardinality), consider broadcast joins when joining this result back to other tables. For large datasets, ensure spark.sql.shuffle.partitions is tuned (default 200 is often too high or too low). SQL equivalent : SELECT Department, AVG(Salary) FROM employees GROUP BY Department

• Python: * Input [5,6,4,8,9] → Output [125, 216, 64, 512, 729].

  The pattern maps each element to its cube (n³). The output values confirm: 5³=125, 6³=216, 4³=64, 8³=512, 9³=729. Time complexity : O(n) — single pass. Space complexity : O(n) for the output list. In PySpark , the equivalent for a DataFrame column: Interview tip : Always confirm the transformation pattern before coding — here the relationship 5→125 immediately reveals cubing. Mentioning numpy shows awareness of vectorized operations preferred in data engineering.

• Find employees who earn more than their managers. * Count occurrence of each alphabet in a string. * Find the longest substring with at most two distinct characters.

  Part 1 — Employees earning more than their managers (SQL self join): Part 2 — Count occurrences of each alphabet in a string : Part 3 — Longest substring with at most 2 distinct characters (sliding window): Time complexity for sliding window: O(n). Space : O(1) — at most 3 keys in the dict at any time.

• Spark / BigQuery Questions: * How do you optimize a PySpark job? * How do you perform a join between two DataFrames? * How to deal with OutOfMemory exception in Spark. * Difference between Repartition and Coalesce in Spark. * How can you read and write data from various sources using PySpark? * How do you debug slow-running jobs in Spark? * Explain the architecture of BigQuery and how it achieves high performance. * How do you troubleshoot slow-running queries in BigQuery? * How do you optimize query performance in BigQuery? * How do you secure access to datasets and tables in BigQuery?

  PySpark Optimization : Use broadcast() for small tables in joins to avoid shuffles Tune spark.sql.shuffle.partitions (default 200) to match data size Cache DataFrames reused multiple times: df.cache() Use predicate pushdown and column pruning (Parquet + Spark Catalyst handles this automatically) Avoid UDFs — prefer built in functions module (JVM native, much faster) Joining two DataFrames : OutOfMemory in Spark : Increase spark.executor.memory , reduce partition size with repartition() , enable