Great , keep the good work.lots of love to you and your family.♥️♥️♥️🥰🥰🥰
@nikhilkoshti5390Күн бұрын
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.
@_hulk7483 күн бұрын
Great explanation sir
@nikhilkoshti53903 күн бұрын
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.
@Simran_0483 күн бұрын
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
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;
@sauravchandra105 күн бұрын
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'] )
@anupam50286 күн бұрын
Sir create your SQL set questions from leetcode ??
@Raj101856 күн бұрын
in first view seems very easy but learn new thing here
@suryanshkaushik92727 күн бұрын
Amazing approach
@gajendragada_dynamites7 күн бұрын
Welcome back sir
@Amrit341168 күн бұрын
select name as Customers from Customers left join Orders on Customers.id=Orders.customerId where Orders.customerid is Null; with 496ms
@nikhilkoshti53909 күн бұрын
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.
@user-vr5dv3sr5r10 күн бұрын
Your contents are helping me a lot. Thank You :)
@anupam502810 күн бұрын
Try to prepare your own sheet sql from leetcode sir for us will very thankful
@AJ-sw5hx10 күн бұрын
how come pid no 2 during joins only one row is there? why isn't it joined with row no 1,3,4?
@williamlu64711 күн бұрын
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
@yashwardhan323811 күн бұрын
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
@cheerfulchai13 күн бұрын
Thank you for this, please post more of such videos..
@swapnilgosawi13 күн бұрын
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
@swapnilgosawi13 күн бұрын
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
@anuragshrivastava785514 күн бұрын
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
@larissayu885314 күн бұрын
where conditions REGEXP '(^| )DIAB1'
@cheerfulchai14 күн бұрын
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!
@nag110214 күн бұрын
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.
@mbm.editzz15 күн бұрын
why did we group by player _id?????
@williamlu64715 күн бұрын
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)
@ladigoutam874415 күн бұрын
God level explanation
@cheerfulchai16 күн бұрын
Thanks for solving this, with this logic being used previously, I was able to solve it before left join...super helpful.
@cheerfulchai16 күн бұрын
Nice explanation for percentile.
@sauravchandra1016 күн бұрын
My approach: return person[person.duplicated(subset='email', keep='first')].iloc[:,[1]].drop_duplicates()
@cheerfulchai16 күн бұрын
Nice solution, tricky part was Date_FORMAT(SEC_TO_TIME(duration), "%H%,"%i","%s")
@anuragshrivastava785516 күн бұрын
bdiya bhai
@omasati170916 күн бұрын
FINALLY I GOT THE MATERPEICE ❤❤
@justcodeitbro131216 күн бұрын
appreciate your work bro please continue this awesome work
@cheerfulchai17 күн бұрын
Very Tricky Question -- your explanation makes it easier to understand.
@raghavarahulkalavala154217 күн бұрын
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?
@user-pg8tk9ze9g18 күн бұрын
Today I have learned the difference between range vs row between. Thanks EDS
@EverydayDataScience17 күн бұрын
Glad that you found the video helpful 😊
@larissayu885318 күн бұрын
can we first lower the name column and then upper the first letter? upper(substring(lower(name),1,1))
@subhrajitpradhan218618 күн бұрын
Where is the timestamps guy, where are you !
@Savenature63518 күн бұрын
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 : )
@in_ashish18 күн бұрын
Absolutely ! the best playlist i haver ever came across for SQL! No doubt how sir explains the topics in so luicd manner.
@avadhootmuli184219 күн бұрын
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 ;
@prachikatayade67619 күн бұрын
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
@kanikasuneja777719 күн бұрын
Instead of Leetcode numbers in timeline, it should have been better, if you've mentioned topic names in advanced SQL