111. Databricks | Pyspark| SQL Coding Interview: Exchange Seats of Students

Azure Databricks Learning: Coding Interview Exercise: Pyspark and Spark SQL
=================================================================================
Coding exercises are very common in most of the Bigdata interviews. It is important to develop coding skills before appearing for Spark/Databricks interviews.
In this video, I have explained a coding scenario to exchange the seats of students in a class. This is Leet Code SQL Exercise number 626. This is also one of the FAANG company question Google, Microsoft, Amazon, Apple, Meta etc.,
Also have customised the scenario a bit and solved the problem. To get more understanding, watch this video
#LeetCodeSQL, #HackerRankSQL,#FAANGCodingQuestion, #GoogleCodingSQL, #AmazonCodingSQL, #MicrosoftCodingSQL,#SQLSeatsExchange,#PysparkSeatsExchange, #CodingInterviewQuestion, #ApacheSparkInterview, #SparkCodingExercise, #DatabricksCodingInterview,#SparkWindowFunctions,#SparkDevelopment,#DatabricksDevelopment, #DatabricksPyspark,#PysparkTips, #DatabricksTutorial, #AzureDatabricks, #Databricks, #Databricksforbeginners,#datascientists, #datasciencecommunity,#bigdataengineers,#machinelearningengineers
Input table:
+------------+--------------+
| id |Student |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 5 | Eve |
+------------+--------------+

[@GANGAEDIGA]

For given exercise: exchange_df1 = df.withColumn("ExchageStudent", when (col('id')%2==1 , coalesce(lead('name',3).over(Window.orderBy('id')),col('name'))). when (col('id')%2==0 , coalesce(lag('name',3).over(Window.orderBy('id')),col('name'))). otherwise(col('name')) )

[@rajasdataengineering7585]

Thanks

[@user-du5bs8fk6l]

Assignment Qn: if below approach is incorrect, pls let me know spark.sql("""select id, name, case when id=1 then lead(name,3) over(order by id) when id=4 then lag(name,3) over(order by id) else name end as fname from users""")

[@naveenraj9977]

Thanks for Sharing the real time scenario, Waiting for more information,Keep it going..!!!!!!

[@rajasdataengineering7585]

Thanks Naveen, will do!

[@sumitchandwani9970]

Please upload more coding excercise videos they are amazing. Really helpful for developing analytical thinking.

[@rajasdataengineering7585]

Thanks Sumit! Will upload more videos

[@srinubayyavarapu2588]

Sir, Thanks a lot for your commitment and Please Upload more coding exercise videos , This will be the game changer , Respect to you

[@rajasdataengineering7585]

Thanks Srinu! Glad you liked it Sure we will upload more videos

[@ranjansrivastava9256]

Really very informative videos, please upload more. you are explaining in very good manner. Keep it going !!!

[@zub3rahmed76]

Exactly keep uploading more videos like this

[@rajasdataengineering7585]

Sure, will create more videos on coding exercises after completing delta live table series soon

[@Munchkin_K]

You speak continuous, need time to grasp 😊

[@rajasdataengineering7585]

Thank you for your feedback. I will try to speak slowly

[@DineshTelegenic]

Thanks for the valuable content.

[@rajasdataengineering7585]

My pleasure! Glad it helps data engineers

[@venkatasai4293]

Good explanation Raja . Keep it going .

[@rajasdataengineering7585]

Thanks Venkat!

[@prabhatgupta6415]

Thanks sir u gave the data. please keep going sir. Please include how to present project infront of interview,manerial round question etc. How to handle big data managerial question. how to dominate interview in our ways.

[@rajasdataengineering7585]

Sure Prabhat, will create videos on these topics. Thanks for your comment

[@nagarajanmurugesan6932]

Keep up the good work. 🙂

[@rajasdataengineering7585]

Thanks, will do!

[@shakthimaan007]

Hi, do you have these codes put somwhere in the github or anywhere please?

[@ShubhamSharma-sy2ce]

SELECT id, CASE WHEN student = 'Alice' THEN (SELECT student FROM students_view WHERE student = 'David') WHEN student = 'David' THEN (SELECT student FROM students_view WHERE student = 'Alice') ELSE student END as updated_student, student FROM students_view;

[@ShubhamSharma-sy2ce]

in pyspark: updated_students_view = students_view.withColumn("updated_student", when(students_view["student"] == 'Alice', students_view.filter(students_view["student"] == 'David')["student"] ) .when(students_view["student"] == 'David', students_view.filter(students_view["student"] == 'Alice')["student"] ) .otherwise(students_view["student"]) )

[@rajasdataengineering7585]

Thanks for sharing your approach

[@bhanukiran7916]

Thank you😮

[@rajasdataengineering7585]

You're welcome 😊

[@Jangampavan2021]

Thank You ..! Please attach the the notebook

[@ATHARVA89]

superb!

[@rajasdataengineering7585]

Thank you! Cheers!

[@ranjansrivastava9256]

Plz answer the Quiz question.

[@TejaswiniNandam]

Great scenario to practice Thanks for providing. I have been following your channel for Spark, and your explanation for every topic is clean and easy to understand for everyone. Here is the code that I practiced, it may help anyone to practice. please let me know of any corrections or suggestions in terms of performance. #creating Dataframe data=[(1,'Alice'),(2,'Bob'),(3,'Charlie'),(4,'David'),(5,'Eve')] df=spark.createDataFrame(data,['id','student']) df.show() #Pyspark solution from pyspark.sql.functions import col,lead,lag,when from pyspark.sql.window import * df.withColumn('new',when (col('id')%2==0,lag('student').over(Window.orderBy('id'))).\ when (col('id')%2== 1,coalesce(lead('student').over(Window.orderBy('id')),'student'))).drop('student').show() #creating tempview df.createOrReplaceTempView('sampl') #sql solution %sql select id,case when id%2=0 then lag(student) over(order by id) when id%2=1 then lead(student,1,student) over(order by id) end new from sampl

[@rajasdataengineering7585]

Thank you! It looks good. Hope it helps data engineers in this community

[@mohammediqbal2406]

this code is for the assignment which raja sir has given in the last?