Відео

can you also share SQL from snowflake side to create those tables

For 2 question it doesn't mention to calculate sum for previous day and current day ,I beleive it should be per policy type sum not specific days ,please check. If you have avg why we need sum because sum=2*avg?

expecting many more interview questions from you till now i have enjoyed every session of yours.

great video, really helpful

thanks for the video, but where are the map_key and map_value functions?

Hi bro.. I like your videos a lot as tgey are always very informative. I have 1 dbt like can we answer this question in SQL in interviews

Could you share that ppt with us it would be great then

from pyspark.sql.functions import avg medications_data = [ (1, "Medication A", 1), (2, "Medication B", 1), (3, "Medication C", 2), (4, "Medication D", 2), (5, "Medication E", 3) ] medication_prices_data = [ (1, 10.50), (2, 20.75), (3, 15.25), (4, 25.00), (5, 12.50) ] medications_df = spark.createDataFrame(medications_data, ["medication_id", "medication_name", "category_id"]) medication_prices_df = spark.createDataFrame(medication_prices_data, ["medication_id", "price"]) # medications_df.show() # medication_prices_df.show() joined_df=medications_df.join(medication_prices_df,medications_df.medication_id==medication_prices_df.medication_id).select(medications_df.medication_name,medications_df.category_id,medication_prices_df.medication_id,medication_prices_df.price) # joined_df.show() # joined_df.printSchema() result_df=joined_df.groupBy("category_id").agg(avg("price").alias("Avg Price")) result_df.show()

# Find the medications that were prescribed by at least three different doctors. # Relevant DF: # df1 = medications (medication_id, medication_name), # df2 = prescriptions (prescription_id, doctor_id, medication_id) medications_data = [ (1, "Medication A"), (2, "Medication B"), (3, "Medication C"), (4, "Medication D"), (5, "Medication E") ] prescriptions_data = [ (1, 1, 1), (2, 2, 1), (3, 3, 1), (4, 1, 2), (5, 2, 2), (6, 3, 2), (7, 1, 3), (8, 2, 4), (9, 3, 4), (10, 4, 5), (11, 5, 5), (12, 6, 5) ] medications_df = spark.createDataFrame(medications_data, ["medication_id", "medication_name"]) prescriptions_df = spark.createDataFrame(prescriptions_data, ["prescription_id", "doctor_id", "medication_id"]) # medications_df.show() # medications_df.printSchema() # prescriptions_df.show() # prescriptions_df.printSchema() Most_pres_medication_df = prescriptions_df.groupBy('medication_id').count().filter('count>="3"') Most_pres_medication_df.cache().count() medications_df.cache().count() final_df=Most_pres_medication_df.join(medications_df,medications_df.medication_id==Most_pres_medication_df.medication_id,"inner").drop(Most_pres_medication_df.medication_id) final_df.show()

QUESTION1 ------------------- print('INPUT DATAFRAME') data=[(1,"Alice","123 Main Street,New York,USA"),(2,"Bob","456 Oak Avenue,San Francisco,USA"), (3,"Carol","789 Pine Road,Los Angeles,USA"),(4,"David","321 Elm Lane,Chicago,USA"), (5,"Emily","654 Maple Drive,Miami,USA")] schema=("emp_id","emp_name","emp_add") df=spark.createDataFrame(data,schema) df.show(truncate=False) print('OUTPUT DATAFRAME') from pyspark.sql.functions import split df1=df.withColumn("street_name",split("emp_add",",")[0])\ .withColumn("city_name",split("emp_add",",")[1])\ .withColumn("country_name",split("emp_add",",")[2]).drop("emp_add").show(truncate=False) print('O/p by using getItem() function') print('In PySpark, the getItem() function is used to retrieve an element from an array or a value from a map column within a DataFrame') df2=df.withColumn("street_name",split("emp_add",",").getItem(0))\ .withColumn("city_name",split("emp_add",",").getItem(1))\ .withColumn("country_name",split("emp_add",",").getItem(2)).drop("emp_add").show(truncate=False) THEORY -------------- In PySpark, the getItem() function is used to retrieve an element from an array or a value from a map column within a DataFrame. The split() function returns an array column in PySpark, which can be further processed using array functions or exploded into multiple rows if needed.Each element of the array corresponds to a substring resulting from the split operation.The limit argument in split() specifies the maximum number of splits to perform. This is particularly useful when you want to limit the number of resulting substrings after splitting the original string. QUESTION2 -------------------- from pyspark.sql.functions import row_number,col from pyspark.sql.window import Window print('INPUT DATASET') data = [ (1, 'Math', 90), (1, 'Science', 93), (1, 'History', 85), (2, 'Math', 85), (2, 'Science', 79), (2, 'History', 96), (3, 'Math', 95), (3, 'Science', 87), (3, 'History', 77), (4, 'Math', 78), (4, 'Science', 91), (4, 'History', 90), (5, 'Math', 92), (5, 'Science', 84), (5, 'History', 88), ] schema=("id","subject","Marks") df=spark.createDataFrame(data,schema) df.show() windowSpec = Window.partitionBy("subject").orderBy(col("Marks").desc()) df1 = df.withColumn("row_number",row_number().over(windowSpec)) print("OUTPUT AFTER APPLYING ROW_NUMBER() WINDOW FUNCTION") df1.show(truncate = False) print("OUTPUT OF STUDENT ID WHO SCORED TOP IN EACH SUBJECT") df1.filter(df1.row_number == 1).select("id","subject").show() THEORY ------------- PySpark Window Ranking functions row_number() window function gives the sequential row number starting from 1 to the result of each window partition. rank() window function provides a rank to the result within a window partition. This function leaves gaps in rank when there are ties. dense_rank() window function is used to get the result with rank of rows within a window partition without any gaps. This is similar to rank() function difference being rank function leaves gaps in rank when there are ties. This is the same as the LAG function in SQL. The lag() function allows you to access a previous row’s value within the partition based on a specified offset. It retrieves the column value from the previous row, which can be helpful for comparative analysis or calculating differences between consecutive rows. This is the same as the LEAD function in SQL. Similar to lag(), the lead() function retrieves the column value from the following row within the partition based on a specified offset. It helps in accessing subsequent row values for comparison or predictive analysis.

Good information. Constructive criticism: You were explaining too fast. Chart explained can be part of 1 video and practical can be in another video. This way 2 videos of 10 to 12 minutes could have been helpful. Best of luck 👍🏻

very well explained 👏

You're doing a great job👏

We can also order by date and then filter the latest date.

bhai data bhi description pr dal dia kro will be timesaviing while practicing

I used this for full address however the column shows up with null values. df=df.withColumn("FullAdress",concat(col("city")+col("Country")))

bro can you please also post the data required to do these questions as well in the description.

pls try to improve sound quality.. 😢

If Execution Memory and Storage Memory both supports data spilling into Disks why does the OOM issues occurs?

plz make video on debugging in pyspark

What tech r being used in your project?

So UnionBy is used when there is Interchanged or Switched/Swapped Columns in a given DataFrames isn't it Sir?. So that It will arrange Columns Linearly

keep uploading videos like this.

Very helpful lecture sir thank you so much ❤

Doing good job beta 👏🏻😊

can you do one real time project

You know any good DE projects available on UA-cam or Udemy which we can do?

you haven't faced any python coding question but still on safer side if we need to prepare for just python coding questions then what all topics of python u will suggest?

Interview Questions are good

Hey Priyam! Explain the process of thoughtwork. How u cleared? Regarding notice period,How to approach, How did you approach. Thanks and appreciated for all the contents

Explain ur interview process

How many rounds of interview will be there for Data Engineers?

can you pls share the ppt also. it will be very helpful for last time rivison.

Hi Sir, pyspark: joinDf = df.alias('a').join(df.alias('b'), col('a.ManagerId')==col('b.Id')) final_df = joinDf.groupBy('b.Id', 'b.Name').agg(count(col('a.Id')).alias('No_of_reportees')).filter(col('No_of_reportees')>=3) final_df.show() %sql with cte as( select ManagerId, count(*) as no_of_reports from office group by ManagerId having no_of_reports >=5) select o.Name as Emp_Name, c.no_of_reports from cte c join office o on c.ManagerId = o.Id Emp_Name no_of_reports Jane Smith 6 David Lee 5

%sql with result as ( select medication_id, count(distinct(doctor_id)) as count_distinct_doc from prescriptions group by medication_id having count_distinct_doc >=3) select m.medication_name from result r join medications m on r.medication_id = m.medication_id

Thank you Sir

Thankyou sir

Thank you Sir, keep doing the great work

Nice Explanation! Thank you!

Thank you Sir, very nice Qs

Sir please provide data

Thank you Sir

thank you and please make a video on how to explain project in the interview it will really help

df_bonus = df.withColumn("bonus", when((col('department') == 'Sales') & (col('salary') > 50000), 0.10 * col('salary')) .otherwise( when((col('department') == 'Marketing') & (col('salary') > 60000), 0.10 * col('salary')) .otherwise(0.5 * col('salary')) ) ) high_salary_employees = df.filter(col('salary') > 70000).orderBy(col('salary').desc()).limit(5) high_salary_employees.show()

Hi Sir, can you please add the schema and the data in future videos .. Thanks

select temp.medication_name, count(temp.doctor_id) dc from (select md.medication_id, medication_name, doctor_id from medications_data md inner join prescriptions_data pd on md.medication_id=pd.medication_id) temp group by temp.medication_name having dc=3 PostgreSQL Solution

Do they ask such easy questions? 😂

Very helpful, thanks for sharing :)

Can you share other 2 questions here, i like to solve them

Sir please provide code for initial dataframe