Відео

mysql with per_month as (select year(created_at) as yr,month(created_at) as mon,sum(value)total_per_mon from amazon_monthly_rev group by year(created_at),month(created_at)) ,prev_revenue as (select *,lag(total_per_mon,1,0) over()pre from per_month) select concat(yr,'-',mon)as yearMonth,round((total_per_mon-pre)/pre*100,2)as rev_diff from prev_revenue;

with percentage as (select student_id,round(sum(marks)/count(1),0) as percentage from marks group by student_id) select s.student_id,s.name,p.percentage, case when p.percentage >=70 then 'Distinction' when p.percentage between 60 and 69 then 'First Class' when p.percentage between 50 and 59 then 'Second Class' when p.percentage between 40 and 49 then 'Thid Class' when p.percentage <= 39 then 'Fail' end as Result from student s join percentage p on s.student_id = p.student_id;

with first_joindate as (select *,min(join_date) over(partition by user_id)as first from user_data) ,new_user_count as ( select join_date, sum(case when join_date = first then 1 else 0 end)new_user,count(1)total_user from first_joindate group by join_date) select join_date,new_user,case when new_user > 0 then round((new_user/total_user) * 100,0) else 0 end as percentage_new_user from new_user_count;

with cte as (select *, (total_sales_revenue-lag(total_sales_revenue,1,0) over(partition by product_id))diff from salesq) select * from products where product_id = (select product_id from cte group by product_id having min(diff) > 0)

create table pop(user1 int,user2 int); insert into pop values (1,5), (1,3), (1,6), (2,1), (2,6), (3,9), (4,1), (7,2), (8,3); with all_pairs as( select user1,user2 from pop union select user2,user1 from pop) select user1 as user,round(count(user1)/(select count(distinct user1) from all_pairs)*100,2) as per from all_pairs group by user1;

select a.source,a.destination from flight a join flight b on a.source < b.source and a.destination = b.source;

with report as( select success_date as date ,'succeeded' as status from succeeded union select fail_date as date,'fail' as status from failed) ,cte2 as( select *,row_number() over(partition by status order by date)rn,(day(date) - row_number() over(partition by status order by date))diff from report where date >= '2019-01-01') select case when status = 'succeeded' then 'succeeded' else 'failed' end as period_state, min(date)as start_date,max(date)as end_date from cte2 group by diff,status

Hey Man , Thank you for your videos , I have been following you since long , I have a question , in the Coding interview for Data Engineers do they provide Leetcode style predefined code or we have to write the entire code including the input handling ?

Thank you for sharing very useful info.

The Best Video about this topic I found on YT

a = [1,2,0,4,-1,5,6,0,0,7,0] b = [] c = [] for i in a: if i != 0: b.append(i) else: c.append(i) final = b.extend(c) print(b)

def operation(st): st_new = "" cnt = 0 dr = st[0] print(dr) for i in st: if i == dr: cnt +=1 else: st_new = st_new + dr + str(cnt) cnt = 1 dr = i st_new = st_new + dr + str(cnt) print(st_new) if __name__ == "__main__": st = "abcabbbccaabd" print(st) operation(st)

a = [2,0,2,1,1,0] for i in range(len(a)): for j in range(0,len(a)-1): if a[j] > a[j+1]: a[j],a[j+1] = a[j+1],a[j] print(a)

a = [2,3,[10,20,[100,200],[2,5]],50] b = [] def rec(n): for i in n: if isinstance(i,list): rec(i) else: b.append(i) return b print(rec(a))

Tried with different approach from pyspark.sql.functions import * frnds_like_df = friends_df.alias("fdf").join(likes_df.alias("ldf"),friends_df['friend_id'] == likes_df['user_id'],'left').select('fdf.user_id','ldf.page_id').groupBy("user_id").agg(collect_set(col('page_id')).alias('liked_array')) user_like_df = friends_df.alias("fdf").join(likes_df.alias("ldf"),friends_df['user_id'] == likes_df['user_id'],'left').select('fdf.user_id','ldf.page_id').groupBy("user_id").agg(collect_set(col('page_id')).alias('user_likes_array')) # final_df = frnds_like_df.join(user_like_df, ['user_id']) final_df = frnds_like_df.join(user_like_df, ['user_id']) \ .withColumn("uncommon", array_except(col('liked_array').cast('array<string>'), col('user_likes_array').cast('array<string>'))) \ .filter(size(col("uncommon")) > 0) \ .withColumn("values", explode(col("uncommon"))).drop(*['liked_array','user_likes_array','uncommon']) final_df.show()

I really apricate you brother . i was encountering many issues even i could not figure out from out . but this video resolves all errors .Thank you .

pls provide code repo for this

We need this piece also after the filter condition. # Finding the unique records. answer_df = friend_page_concat_df.select(col("friend_id").alias("user_id"), col("page_id")).distinct() answer_df.show()

Thanks Man

I have a simpler solution with first_value and last_value window function as below (wrote in spark sql and can be adjusted in pyspark script acordingly - ************************************************************************************ %sql with cte as ( select cust_id, first_value(origin) over( partition by cust_id order by flight_id range between unbounded preceding and unbounded following ) as origin, last_value(destination) over( partition by cust_id order by flight_id range between unbounded preceding and unbounded following ) as destination from flight ) select distinct(*) from cte ************************************************************************************

Thank you so much for making these different types of pyspark questions, This is really helpful.

could you please make alternate solution in mysql ?

This solution only works for a specific month. This will fail if the data is scaled over several months.

This worked for me : ) os.environ['PYSPARK_PYTHON'] = sys.executable os.environ['PYSPARK_DRIVER_PYTHON'] = sys.executable Thanks alottt !!!!!!!!

solution in sql server with CTE2 as( select user1 as allusers from popularity union all select user2 from popularity) select distinct allusers, count(allusers) / COUNT(distinct allusers )*100 as alluserscnt from CTE2 group by allusers

I have an interview with Freshworks for position of SDET engineer..Frst round they told it will be of Hacker rank exam..Can you please tell me what type of questions that we can expect?

I used the below way before looking at your solution: with cte as (select unnest(string_to_array(content, ' ')) as word from google_files) select word,count(word) as word_count from cte where word in ('SQL', 'PySpark') group by word Thanks for the solution, I learned 2 new functions.

import pyspark from pyspark.sql.window import Window from pyspark.sql.functions import col , count, round, sum , desc, rank df=voting_results.groupBy(col("voter")).agg(count(col("candidate")).alias("count_total")) df=df.withColumn("vote_value" , round(1*1.0/col("count_total"),3)) df2=voting_results.join(df,on = (voting_results.voter==df.voter), how="inner")\ .select(col("candidate"),col("vote_value")) df2=df2.filter(col("candidate").isNotNull()) df2=df2.groupby(col("candidate")).agg(round(sum(col("vote_value")),3).alias("total_count")) #df2.show() df2=df2.withColumn("rnk",rank().over(Window.orderBy(col("total_count").desc()))) df2=df2.filter(col("rnk")==1)\ .select("candidate") df2.show()

Instead of zip, can’t we use extend or + for the 2nd problem?

Thanks. I will try to get this done in MS SQL Server.

please share the method using self join. also the union. humble request.

thanks for coming up with python questions for de role

from pyspark.sql.functions import * from pyspark.sql.window import Window schema=['firstName','lastName','videoId','flagId'] data=[('Anupam','Sharma','V1','F1'),('Anupam','Sharma','V1','F11'),('Shashank','Saxena','V2','F2'),('Karan','Rawat','V3','F3'),('Anupam','Sharma','V4','F4'),('Anupam','Sharma','V5','F5'),('Anupam','Sharma','V6','F6'),('Shashank','Saxena','V7','F7'),('Shashank','Saxena','V8','F8'),('Shashank','Saxena','V9','F9'),('Karan','Rawat','V10','F10')] df1=spark.createDataFrame(data,schema) df1.display() schema2=['flagId','reviewByYT','reviewOutcome'] data2=[('F1','TRUE','Approved'),('F2','TRUE','Approved'),('F3','TRUE','Approved'),('F4','TRUE','Removed'),('F5','TRUE','Approved'),('F6','TRUE','Approved'),('F7','TRUE','Approved'),('F8','FALSE',None),('F9','TRUE','Approved'), ('F10','TRUE','Removed'),('F11','TRUE','Approved')] df2=spark.createDataFrame(data2,schema2) df2.display() df=df1.join(df2,df1.flagId==df2.flagId,'inner').filter(df2.reviewOutcome=='Approved').select(concat(df1.firstName,lit(' '),df1.lastName).alias('fullName'),df1.videoId,df2.reviewOutcome).distinct() df.display() dfOut=df.groupBy(df.fullName).agg(count(df.videoId).alias('approvedCount')).withColumn('highestRank',dense_rank().over(Window.orderBy(col('approvedCount').desc()))).filter(col('highestRank')==1).select(df.fullName) dfOut.display()

@DEwithDhairy First when I look into two DF's I didn't understand how to define relationship but after careful observation b/w columns like attacker and defender then got it. Keep going 👍👍👍👍

In pg u can use unnest function along with json with a delimiter of a space to get the word together

understood thanks for concept

Superb explanation 👌 👏 👍

one doubt, when I'm doing partitionby on house and region instead of groupBy I'm getting 2 duplicates records, any idea why?

The voice is too low, other than that all good.

Superb explanation 👌 👏 👍

Is this question is for freshers or experienced ones??

one feedback, volume is too low for almost all videos, explanation pace / approach is all fine.

I'm little new to these type of questions, I have used another method to solve the same , can you tell me why this approach is not suited ? Select a.cust_id, a.origin, b.destination from (Select o.cust_id, o.origin from travelling_details o where origin not In ( Select t.destination from travelling_details t where t.cust_id = o.cust_id)) a join (Select o.cust_id, o.destination from travelling_details o where o.destination not In ( Select t.origin from travelling_details t where t.cust_id = o.cust_id)) b on a.cust_id = b.cust_id;

DEwithDhairy, nice content keep up the great content

Thanks for informative stuff. Instead of specifying all conditions in the join. Just we can specify only one condition ( I mean not required and Or conditions) It works and fetch expected output. Cheers!!

check my solution using dictionary r="aabcabbccd" s=r+"1"#added 1 as a dummy charecter to help in loop dic={} dic[s[0]]=1; for i in range(1,len(s)): if (s[i] in dic.keys() and (s[i-1]==s[i])): dic[s[i]]=dic[s[i]]+1 else: print(f"{s[i-1]}",end="") print(dic[s[i-1]],end="") del dic[s[i-1]] dic[s[i]]=1

thoda tej bola kro ya phir use good speaker.

Nice explanation 👌 👍 👏

Sir in Case1 why the, rank and dense rank are taking reference of salary column , and why not department column

Using two pointer approach: lst = [1,0,1,2,5] m = 0 n = len(lst)-1 while m <= n: if lst[m] == 0: lst[m], lst[n] = lst[n], lst[m] m += 1 print(lst)