Great , keep the good work.lots of love to you and your family.♥️♥️♥️🥰🥰🥰

Sir, it was difficult for me 😰. Formatting date and then actually I was trying to do it with a case statement but it was giving errors for comarison.

Great explanation sir

Sir, I have a different solution. I solved it using their formula. I used the CASE statement instead of reassigning the values. My solution took 'Runtime: 76 ms'. ~ select query_name, round(sum(rating / position) / count(query_name), 2) as quality, round(count(case when rating < 3 then query_name end) / count(query_name) * 100 , 2) as poor_query_percentage from Queries where query_name is not null group by query_name; ~ Thanks for making this series sir. Its improving SQL knowledge. Initially, I was unable to solve the questions but noe I try it myself and then go through your solutions as well.

why join, when Select student_id, min(course_id), max(grade) as grad from Enrollments group by student_id order by student_id works fine

amazing

Why do a left join and not an inner join?

My approach: import pandas as pd def find_managers(employee: pd.DataFrame) -> pd.DataFrame: return employee.groupby( 'managerId', as_index=False ).agg( count = ('id', 'count') ).merge( employee, left_on = 'managerId', right_on = 'id', how = 'right' ).query( 'count >= 5' )[['name']]

That is such a nice explanation. Thanks!

bro your solution is 2 complicated, you yourself says not to use sub-queries but in this video, you crossed all the limit. So many sub-queries haha my solution, although its not perfect. but not complicated and accepted with all the test cases. With cte as ( Select *, min(event_date) over(partition by player_id order by event_date) as Ist_Date, LAG(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS lag_date, LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS lead_date From Activity) Select ROUND(Count(DISTINCT player_id)/ (select count(distinct player_id) from cte),2) AS fraction FROM cte WHERE DATEDIFF(lead_date, Ist_Date) < 2;

How I solved this: import pandas as pd def gameplay_analysis(activity: pd.DataFrame) -> pd.DataFrame: activity.sort_values(by = 'event_date', inplace = True) grouped = activity.groupby('player_id') cnt = 0 for name, entries in grouped: if len(entries) >= 2: first_two_dates = entries['event_date'].iloc[:2].tolist() cnt += pd.Timedelta(days=1) == (first_two_dates[1] - first_two_dates[0]) return pd.DataFrame( [round(cnt / len(grouped), 2)], columns = ['fraction'] )

Sir create your SQL set questions from leetcode ??

in first view seems very easy but learn new thing here

Amazing approach

Welcome back sir

select name as Customers from Customers left join Orders on Customers.id=Orders.customerId where Orders.customerid is Null; with 496ms

Hello sir, I have a different solution. Please let me know what do you think of it. Query is as follows, ~ select s.user_id, round(ifnull(sum(c.action = "confirmed"), 0) / count(*), 2) as confirmation_rate from Signups as s left join Confirmations as c on s.user_id = c.user_id group by s.user_id; ~ "c.action = "confirmed" this returns the '1' for true and '0' for false and 'null' for 'null' thats why tohandle 'null' I added ifnull() function and adding up the respective 1s and 0s.

Your contents are helping me a lot. Thank You :)

Try to prepare your own sheet sql from leetcode sir for us will very thankful

how come pid no 2 during joins only one row is there? why isn't it joined with row no 1,3,4?

Is this correct? WITH CTE AS (SELECT *, DATE_SUB(event_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY player_id ORDER BY event_date) DAY) AS diff FROM Activity) SELECT COUNT(T.player_id)/ (SELECT COUNT(DISTINCT player_id) FROM Activity) AS tt FROM ( SELECT player_id, diff, COUNT(*) AS cnt FROM CTE GROUP BY player_id, diff HAVING COUNT(*)>1) T

Sir Please help in completing SQL 50 questions using Pandas as the very proportion of people and Leetcode Premium (for these Premium questions) and their is no playlist or videos for SQL-50 in Python

Thank you for this, please post more of such videos..

with cte as ( select customer_id,product_id , rank()over (partition by customer_id order by count(*) desc) rnk from Orders group by 1,2 ) select c.customer_id,c.product_id , p.product_name from cte c join products p on c.product_id =p.product_id where rnk =1

with cte as ( select employee_id from employees where manager_id=1 and employee_id!=1 union all select a.employee_id from employees a join cte b on (b.employee_id=a.manager_id) ) select employee_id from cte

select p.product_name, sum(unit) as unit from Products p left join Orders o on p.product_id=o.product_id where month(order_date) = 2 and year(order_date) = 2020 group by product_name having unit>=100

where conditions REGEXP '(^| )DIAB1'

Hello @EDS, can you please make videos on theoretical questions like Decision Tree, Logistic Regression, Linear Regression, Supervise vs unsupervised learning, classification as well. Also, can you create videos on LLMs as they are trending and would love to learn more about them. Thank you!

You are doing an amazing job , I hav'nt seen such a good playlist. But a kind request please refine the title of each video to include the LeetCode or Stratascratch. The search result is not pulling your videos. Exceptional content, if you do that the the subscribers count will also increase.

why did we group by player _id?????

A solution with using subquery! WITH CTE AS( Select *, COUNT(emp_id) OVER(PARTITION BY dep_id) AS cnt FROM Employees) SELECT emp_name,dep_id FROM CTE WHERE position='Manager' and cnt = (SELECT MAX(cnt) FROM CTE)

God level explanation

Thanks for solving this, with this logic being used previously, I was able to solve it before left join...super helpful.

Nice explanation for percentile.

My approach: return person[person.duplicated(subset='email', keep='first')].iloc[:,[1]].drop_duplicates()

Nice solution, tricky part was Date_FORMAT(SEC_TO_TIME(duration), "%H%,"%i","%s")

bdiya bhai

FINALLY I GOT THE MATERPEICE ❤❤

appreciate your work bro please continue this awesome work

Very Tricky Question -- your explanation makes it easier to understand.

Can you help me understand why we took max here? we're only comparing the store1 which has only 1 value with the appropriate product_id. I didn't understood why we'll get the scenario of 95, null, null?

Today I have learned the difference between range vs row between. Thanks EDS

can we first lower the name column and then upper the first letter? upper(substring(lower(name),1,1))

Where is the timestamps guy, where are you !

I have tried to solve this problem. Here is my approach : with all_matches as (select first_player as 'All_player_id',first_score as 'Scores' from matches union select second_player,second_score from matches), all_scores as ( select All_player_id,group_id,sum(scores) as total_scores, row_number() over(partition by group_id order by sum(scores) desc) as rn from all_matches a join players p on a.all_player_id=p.player_id group by 1,2 order by group_id, all_player_id asc,3 desc) select group_id,all_player_id as player_id from all_scores where rn=1; Please share if there is issue in the code : )

Absolutely ! the best playlist i haver ever came across for SQL! No doubt how sir explains the topics in so luicd manner.

Did using self join select w1.id from weather w1 left join weather w2 on w1.recordDate= w2.recordDate+1 where (w1.temperature-w2.temperature) > 0 ;

with cte as ( select player_id ,event_date, min(event_date) over(partition by player_id) as first_login from activity ) select round(sum(decode(event_date-first_login,1,1,0)) /count(distinct player_id),2) fraction from cte

Instead of Leetcode numbers in timeline, it should have been better, if you've mentioned topic names in advanced SQL

worker.tail(1), we can use this as well ryt?