14. Pepsico azure databricks interview question and answer | azure data engineer interview Q & A

[@goluSingh-su1xs]

Superb explanation, love watching

[@ssunitech6890]

Thank you so much 🙂

[@amritasingh1769]

Very nice question, your explanation in Excel is awesome 👍

[@ssunitech6890]

Thanks a lot

[@rammohanrao1369]

sinmple solution: groupby(machineid,activity_type).agg(sum(timestamp)) and then lag and difference

[@ssunitech6890]

Thanks 👍

[@aranijayachandra0078]

Great sir❤

[@ssunitech6890]

Thanks 🙏

[@azuredb]

thank you sir

[@ssunitech6890]

Keep learning

[@prashanthkammari6402]

Can you please share code or maintain in public github

[@ssunitech6890]

I am maintaining code in description of each video, will add code here today.

[@durgad4763]

We can use pivot function to convert rows to separate columns right?

[@ssunitech6890]

Yes we can

[@tejaspise4638]

df2 = df1.withColumn('timestamp', when(df1.activityid == 'start', col('timestamp') * -1).otherwise(col('timestamp'))) df3 = df2.groupBy('Machine_id', 'processid').agg(sum('timestamp').alias('total_time')) res = df3.groupBy('Machine_id').agg(avg('total_time').alias('avg_processing_time'))

[@ssunitech6890]

Thanks 👍

[@satishgs5355]

df = df.withColumn("lag",lag(col("Timestamp")).over(Window.orderBy("Machine_id")))\ .withColumn("time_diff",round((col("Timestamp")-col("lag")),2))\ .filter(col("Activity_type") == "end").groupBy(col("Machine_id")).agg(avg("time_diff")) df.show()

[@enisertem9738]

df1 = (df1.groupBy(df1.Machine_id,df1.processid) .agg(sum(when(df1.activityid=="start",df1.timestamp * -1).otherwise(df1.timestamp)).alias("process_time"))) df2 = (df1.groupBy(df1.Machine_id) .agg(mean(df1.process_time)).alias("avg_process_time")) df2.show()

[@viduAndtwins]

from pyspark.sql.window import Window from pyspark.sql.functions import * windowSpec = Window.partitionBy("Machine_id","processid").orderBy(col("processid"),col("activityid").desc()) df.withColumn("lag",col("timestamp")-lag(col("timestamp"),1).over(windowSpec)).groupBy("Machine_id").avg("lag").alias("avg_processingtime").show()

[@neeraj.g]

better approach

[@ssunitech6890]

👍