วีดีโอ

Thanks for sharing🎉

Can you please create video on DLT streaming tables. I'm facing issues while using SCD1. My bronze notebook is seperate and Silver notebook is seperate. I'm facing issues while calling bronze table as stream and loading into silver.

thanks for sharing. keep up the good work!!

query = spark.sql("""with cte as (select dept_id,emp_name,salary, row_number() over(partition by dept_id order by salary desc, emp_name) as rn, count(dept_id) over (partition by dept_id order by dept_id) as dept_count from emp ) select dept_id,max(case when rn = 1 then emp_name else Null end ) as max_salary, min(case when rn = dept_count then emp_name else Null end) as min_salary from cte group by dept_id""")

product_data = [(1, 'Laptop', 'Electronics'), (2, 'Jeans', 'Clothing'), (3, 'Chairs', 'Home Appliances')] product_schema = ['product_id', 'product_name', 'category'] product_df = spark.createDataFrame(product_data, product_schema) product_df.show() sales_data = [(1, 2019, 1000.00), (1, 2020, 1200.00), (1, 2021, 1100.00), (2, 2019, 500.00), (2, 2020, 600.00), (2, 2021, 900.00), (3, 2019, 300.00), (3, 2020, 450.00), (3, 2021, 400.00)] sales_schema = ['product_id', 'year', 'total_sales_revenue'] sales_df = spark.createDataFrame(sales_data, sales_schema) sales_df.withColumn("year",to_date("year",'YYYY').cast(DateType())) sales_df.show()

This question is for how many years of experience candidates ?

Can we Write In CTE2 AS ( SELECT DISTINCT company from cte1 where rnk =1) ?

Why your audio is echoing?

match_df1=match_df.withColumn('team',expr("concat((team_A),',',team_B)")) match_df1=match_df1.drop('team_A','team_B') match_df1.show() match_df1=match_df1.withColumn('team',split(col('team'),',')) match_df1=match_df1.withColumn('team',explode(col('team'))) match_df1=match_df1.select('team','win') match_df1.show() match_df2=match_df1.groupBy('team').agg(count('*').alias('played')) match_df3=match_df1.groupBy('win').agg((count('*')/2).cast('int').alias('total_win')) final_df=match_df2.join(match_df3,col('team')==col('win'),'left').orderBy(col('total_win').desc()) final_df=final_df.select('team','played','total_win',coalesce(col('total_win'),lit(0)).alias('total_wins')) final_df=final_df.drop('total_win') final_df.show() this is my alternative approach and its working well.

For duplicates, With CTE (Select *, row_number() over (partition by dept,name,salary order by salary) as rnk from emp) SELECT * from CTE where rnk = 1 Will get only the records with out duplicate

Here is my approach, this also works. from pyspark.sql.functions import * from pyspark.sql.window import Window win=Window.partitionBy('dept_id').orderBy(col('salary').desc()) df1=df.withColumn('highest_salary',dense_rank().over(win)) df1.show() df2=df1.groupBy('dept_id').agg(min(when(col('highest_salary')==2,col('emp_name'))).alias('min_salaried_emp')\ ,max(when(col('highest_salary')==1,col('emp_name'))).alias('max_salaried_emp')) df2.display()

i think we should use row_number not dense_rank, please clarify

Cant we use any other command for populating character * instead of repeat?

this was straightforward question, From the glimpse of the dataset, I came to know that we have to create an array and explode to get the room types.

Small suggestion. Your voice is echoing so little bit disturbance to hear you properly. Appreciate you for creating this kind of videos

select distinct * from employee order by salary desc offset 2 rows fetch next 1 row only;

with cte as (select * from employee e1 where not exists ( select 1 from employee e2 where e1.emp_id = e2.manager_id)), cte2 as (select department ,emp_id, dense_rank() over(partition by department order by salary desc ) as dnk from cte) select department, emp_id,dnk from cte2 where dnk= 1;

bhai be original . trying to talk in different accent

df=spark.read.csv("/content/jobs.csv",header=True) df.show() result=df.groupBy('job').agg(count('name').alias('total_count')) result.show() rows=result.rdd.collect() result_dict = dict((row['job'], row['total_count']) for row in rows) print(result_dict)

customer_df=spark.createDataFrame(customer_data,customer_schema) order_df=spark.createDataFrame(order_data,order_schema) customer_df.show() order_df.show() group_df=customer_df.join(order_df,'customer_id','left_anti') group_df.show()

Haven’t you already filtered out nulls in the last second step? Coaelesce was unnecessary. cdf there was a spelling mistake it is cDf

To solve Question 1 use the following query: select name,dept,salary from emp e where salary = (select max(salary) from emp where dept = e.dept ) order by dept

Nice session. Thanks a lot.

Can you share your resume please .... I'm applying im naukri since one month not even single call or reply received. Please gudie me

Infosys interview question: I have two sources : Col1 col2 1 2 3 4 5 6 C1 c2 c3 A b c D e f I want to show the records like this C1 c... I want to show the records like this C1 c2 c3 c4 c5 1 3 5 A D 2 4 6 b e . . . C f Will you please solve this and upload the video. OR You can give the code on comment.

SELECT employee_id, department_id FROM employees GROUP BY employee_id HAVING COUNT(employee_id) = 1 UNION SELECT employee_id, department_id FROM employees WHERE primary_flag = 'Y';

can you please suggest resource to learn Pyspark testing

At final , How come region The house has same rank? we have used dense rank right, it shouldn't give us the same rank. Please clarify.

data = [(1,'Rahul',33,'Kolkata'),(2,'Raj',12,'Delhi'), (2,'Raj',12,'Delhi'), (3,'Priya',37, 'Mumbai'),(3,'Priya',37, 'Indore')] schema = "Id int, Name string, Roll int, City string"

plz try to give dataset in description and nice explanation, also ur voice doesnt seem clear !!

Place data sets in discription

voice is echoing. pls rectify it

if i started learning for now than there are very less data engineering internships(if one has data internship it is easy to get a job ,and that person also get hand on experience ) ,,,,so should i do data analyst first only for internship and after that i will study for data engineering for job because it is also a good way , and i need to do an internship because it is in our college criteria , they do not support much if you do not have done internship??

Bro first add the question in either comments or description section.

How do we run the notebook in prod environment. I mean cicd , IAC , and anything else please help us with that as well.

Thanks for the help, this works well. 💌

Thanks, helped a lot