Data Lab PreTest

You have the following dataset and need to calculate the average salary per department, but only include departments where the average salary is greater than 4000:

• df.groupBy(\Department\").avg(\"Salary\").filter(\"avg(Salary) > 4000\")"
• df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, AVG(Salary) AS AvgSalary FROM employees GROUP BY Department HAVING AVG(Salary) > 4000\")"
• from pyspark.sql.functions import col, avg\n\ndf.groupBy(\Department\").agg(avg(col(\"Salary\")).alias(\"AvgSalary\")).filter(col(\"AvgSalary\") > 4000)"
• df.filter(\Salary > 4000\").groupBy(\"Department\").avg(\"Salary\")"

You need to find all employees in departments with a budget greater than 8000. Which code snippet achieves this?

• employees_df.join(departments_df, \Department\").filter(\"Budget > 8000\").select(\"Name\", \"Department\")"
• employees_df.createOrReplaceTempView(\employees\")\ndepartments_df.createOrReplaceTempView(\"departments\")\nspark.sql(\"SELECT e.Name, e.Department FROM employees e JOIN departments d ON e.Department = d.Department WHERE d.Budget > 8000\")"
• from pyspark.sql.functions import col\n\nemployees_df.join(departments_df, \Department\").where(col(\"Budget\") > 8000).select(\"Name\", \"Department\")"
• employees_df.join(departments_df, employees_df.Department == departments_df.Department).filter(\Budget > 8000\")"

From the dataset below, find the top-earning employee in each department, including their department name and salary. Use Spark SQL:

• df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, Salary FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rank FROM employees) t WHERE rank = 1\")"
• df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, MAX(Salary) AS Salary FROM employees GROUP BY Department, Name\")"
• df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, MAX(Salary) AS MaxSalary FROM employees GROUP BY Department\")"
• from pyspark.sql.window import Window\nfrom pyspark.sql.functions import col, row_number\n\nwindow_spec = Window.partitionBy(\Department\").orderBy(col(\"Salary\").desc())\ndf.withColumn(\"rank\", row_number().over(window_spec)).filter(\"rank == 1\").select(\"Department\", \"Name\", \"Salary\")"

Which of the following codes computes the total sales per region efficiently in Spark SQL?

• df.createOrReplaceTempView(\sales\")\nspark.sql(\"SELECT Region, SUM(Sales) AS TotalSales FROM sales GROUP BY Region\")"
• df.groupBy(\Region\").sum(\"Sales\")"
• from pyspark.sql.functions import col, sum\n\ndf.groupBy(col(\Region\")).agg(sum(col(\"Sales\")).alias(\"TotalSales\"))"
• df.filter(\Sales IS NOT NULL\").groupBy(\"Region\").sum(\"Sales\")"

How can you rank products within each category by their sales in descending order?

• from pyspark.sql.window import Window\nfrom pyspark.sql.functions import col, rank\n\nwindow_spec = Window.partitionBy(\Category\").orderBy(col(\"Sales\").desc())\ndf.withColumn(\"rank\", rank().over(window_spec))"
• df.createOrReplaceTempView(\products\")\nspark.sql(\"SELECT Category, Product, RANK() OVER (PARTITION BY Category ORDER BY Sales DESC) AS Rank FROM products\")"
• df.groupBy(\Category\", \"Product\").agg(rank().alias(\"Rank\"))"
• from pyspark.sql.functions import row_number\n\nwindow_spec = Window.partitionBy(\Category\").orderBy(\"Sales DESC\")\ndf.withColumn(\"row_number\", row_number().over(window_spec))"

Which function would you use to replace all NULL values in a Spark DataFrame with default values?

• df.na.fill({\Column1\": 0, \"Column2\": \"N/A\"})"
• df.na.replace(NULL, {\Column1\": 0, \"Column2\": \"N/A\"})"
• df.fillna({\Column1\": 0, \"Column2\": \"N/A\"})"
• df.replace(NULL, {\Column1\": 0, \"Column2\": \"N/A\"})"

How do you efficiently join two DataFrames on multiple keys in PySpark?

• df1.join(df2, (df1.Key1 == df2.Key1) & (df1.Key2 == df2.Key2))
• df1.join(df2, \Key1\", \"inner\")"
• df1.createOrReplaceTempView(\df1\")\ndf2.createOrReplaceTempView(\"df2\")\nspark.sql(\"SELECT * FROM df1 JOIN df2 ON df1.Key1 = df2.Key1 AND df1.Key2 = df2.Key2\")"
• df1.merge(df2, \Key1\")"

How would you extract the year from a date column in Spark DataFrame?

• from pyspark.sql.functions import year\n\ndf.withColumn(\Year\", year(\"Date\"))"
• df.selectExpr(\YEAR(Date) AS Year\")"
• df.withColumn(\Year\", df.Date.year())"
• df.createOrReplaceTempView(\table\")\nspark.sql(\"SELECT YEAR(Date) AS Year FROM table\")"

What is the correct way to calculate the 90th percentzasdasdile of a column in PySpark?

• from pyspark.sql.functions import expr\n\ndf.selectExpr(\percentile_approx(column, 0.9)\")"
• df.selectExpr(\approx_percentile(column, 0.9)\")"
• from pyspark.sql.functions import percentile_approx\n\ndf.select(percentile_approx(\column\", 0.9))"
• df.select(\column\").approx_percentile(0.9)"

How would you add a unique identifier to each row in a Spark DataFrame?

• from pyspark.sql.functions import monotonically_increasing_id\n\ndf.withColumn(\unique_id\", monotonically_increasing_id())"
• df.withColumn(\unique_id\", row_number())"
• df.createOrReplaceTempView(\table\")\nspark.sql(\"SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS unique_id FROM table\")"
• df.withColumn(\unique_id\", dense_rank())"