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 |
+------------+--------------+

[Manikanta-nv]

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""")

[naveenraj]

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

[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'))
)

[srinubayyavarapu]

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

[sumitchandwani]

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

[ranjansrivastava]

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

[zubrahmed]

Exactly keep uploading more videos like this

[Munchkin_K]

You speak continuous, need time to grasp 😊

[prabhatgupta]

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.

[venkatasai]

Good explanation Raja . Keep it going .

[shakthimaan]

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

[ShubhamSharma-syce]

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;

[Jangampavan]

Thank You ..! Please attach the the notebook

[priyankatangirala]

please provide the answer for the exercise

[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

#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