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""")
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')) )
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.
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;
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
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
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""")
Thanks for Sharing the real time scenario,
Waiting for more information,Keep it going..!!!!!!
Thanks Naveen, will do!
Really very informative videos, please upload more. you are explaining in very good manner. Keep it going !!!
Sir, Thanks a lot for your commitment and Please Upload more coding exercise videos , This will be the game changer , Respect to you
Thanks Srinu! Glad you liked it
Sure we will upload more videos
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'))
)
Thanks
Please upload more coding excercise videos they are amazing.
Really helpful for developing analytical thinking.
Thanks Sumit! Will upload more videos
Exactly keep uploading more videos like this
Sure, will create more videos on coding exercises after completing delta live table series soon
Hi, do you have these codes put somwhere in the github or anywhere please?
with temp as
(select count(id) as cnt
from table_a)
select
case when table_a.id%2=0 then table_a.id
when.table_a.id%20 and table_a.id
Thank you for sharing your approach
Thanks for the valuable content.
My pleasure! Glad it helps data engineers
I Required more sql and pyspark problems for practice is there any paid course you have which i can purchase for practice only
Good explanation Raja . Keep it going .
Thanks Venkat!
Keep up the good work. 🙂
Thanks, will do!
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.
Sure Prabhat, will create videos on these topics. Thanks for your comment
Thank you😮
You're welcome 😊
Thank You ..! Please attach the the notebook
superb!
Thank you! Cheers!
You speak continuous, need time to grasp 😊
Thank you for your feedback. I will try to speak slowly
please provide the answer for the exercise
Plz answer the Quiz question.
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;
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"])
)
Thanks for sharing your approach
directly u can assign david and alice ...u dont have to query student and in where condition u r giving student itself.
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
Thank you! It looks good. Hope it helps data engineers in this community
this code is for the assignment which raja sir has given in the last?