Пікірлер
@sravankumar1767 6 сағат бұрын
If we have N number of rows we can't write N number of lag functions right?
@Thedata.techclub 4 сағат бұрын
Big Thanks to ask this question. yes, we can't write if we have 10000 rows for that. That was the temporary solution and demonstration of lag function to deal with nulls. If we have such situations and we do not want to write LAG so i have a solution for this. Please check the code below and ask me if you have any questions. with cte as ( select workname,person_days, expenditure, row_number() over (order by (select null)) rn1 from jobs ), Ncte as ( select workname,person_days, expenditure, row_number() over (order by (select null)) - 1 rn2 from cte ) SELECT t1.workname, (SELECT top 1 t2.workname FROM Ncte t2 WHERE t2.rn2 < t1.rn1 AND t2.workname IS NOT NULL ORDER BY t2.rn2 DESC ) New_workname,t1.person_days, t1.expenditure FROM cte t1;
@sravankumar1767 6 сағат бұрын
Nice explanation 👌 👍 👏
@Thedata.techclub 4 сағат бұрын
Thanks! Glad you found it helpful.
@beedalabharathi3120 21 сағат бұрын
Super 👍
@Savenature635 Күн бұрын
My approach : with cte as (select customer_id,order_amount, dense_rank() over(partition by customer_id order by max(order_date) desc) as rnk from az_orderss group by 1,2) select customer_id,max(case when rnk=1 then order_amount end) as latest_order_amount, min(case when rnk=2 then order_amount end) as second_latest_order_amount from cte group by customer_id;
@Savenature635 Күн бұрын
select user_id,company_id,language from capgemini_user where language in ('English','German') and user_id in ( select user_id from capgemini_user where language in ('English','German') group by company_id,user_id having count(user_id)>1);
@saktibiswal6445 3 күн бұрын
Why is the volume always less in your videos?
@Ronak-u6b 3 күн бұрын
Volume in the video is completely fine.
@sravankumar1767 3 күн бұрын
Superb explanation 👌 👏 👍
@nanisai1532 4 күн бұрын
with cte as (select user_id,count(distinct follower_id) as num from famous group by user_id), cte2 as (select *,sum(num) over() as sm from cte) select user_id,(num/sm)*100 as famous from cte2
@saijaswanth3036 6 күн бұрын
WITH cte AS ( SELECT company_id , user_id, COUNT(CASE WHEN language IN ('german' , 'english') THEN 1 ELSE NULL END) AS count_ FROM company_user GROUP BY company_id , user_id ) SELECT a.user_id , b.company_id , b.language FROM cte as a , company_user as b WHERE a.user_id = b.user_id and a.count_ = 2
@gsrsakhilakhil528 7 күн бұрын
Happy to see you back
@Iamthebestthing 7 күн бұрын
more video on joins
@ishamajumdar5580 8 күн бұрын
WITH cte AS ( SELECT u.user_id, u.user_name, f.friend_id, f1.user_name AS friend_name FROM users u LEFT JOIN friends f ON u.user_id = f.user_id LEFT JOIN users f1 ON f.friend_id = f1.user_id WHERE u.user_name IN ('Karl','Hans') ) SELECT c1.user_name AS friend_1, c2.user_name AS friend_2, c1.friend_name AS Mutual_friend FROM cte c1 JOIN cte c2 ON c1.user_id != c2.user_id AND c1.friend_id = c2.friend_id AND c1.user_name < c2.user_name;
@ishamajumdar5580 8 күн бұрын
WITH cte AS ( SELECT *, DAY(purchase_date) - DENSE_RANK() OVER(PARTITION BY empid ORDER BY purchase_date) AS diff FROM purchases ) SELECT empid FROM cte GROUP BY empid, diff HAVING COUNT(*) >= 3;
@ishamajumdar5580 10 күн бұрын
WITH cte AS ( SELECT *, MINUTE(updated_time) - ROW_NUMBER() OVER(PARTITION BY status ORDER BY MINUTE(updated_time)) AS diff FROM service_log WHERE status = 'down' ) SELECT service_name, status, MIN(updated_time) AS start_time, MAX(updated_time) AS end_time FROM cte GROUP BY service_name, status, diff HAVING COUNT(*) >= 5;
@ishamajumdar5580 10 күн бұрын
WITH cte AS ( SELECT customer_Id, order_date, order_amount, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders ) SELECT customer_ID, SUM(CASE WHEN rn = 1 THEN order_amount ELSE 0 END) AS latest_order_amount, SUM(CASE WHEN rn = 2 THEN order_amount ELSE 0 END) AS second_latest_order_amount FROM cte WHERE rn <= 2 GROUP BY customer_ID;
@ishamajumdar5580 10 күн бұрын
WITH cte AS ( SELECT e.salary, ROW_NUMBER() OVER(PARTITION BY d.department ORDER BY salary DESC) AS rn FROM employee e JOIN dept d ON e.department_id = d.id WHERE d.department IN ('marketing', 'engineering') ) SELECT ABS(c1.salary - c2.salary) AS Absolute_diff FROM cte c1 JOIN cte c2 ON c1.rn = 1 AND c2.rn = 1 AND c1.salary > c2.salary;
@ishamajumdar5580 10 күн бұрын
SELECT u.language, SUM(CASE WHEN LOWER(device) LIKE '%macbook%' OR LOWER(device) LIKE '%iphone%' OR LOWER(device) LIKE '%ipad%' THEN 1 ELSE 0 END) AS iphone_users_cnt, COUNT(*) AS total_users FROM playbook_users u JOIN playbook_events e ON u.user_id = e.user_id GROUP BY language ORDER BY total_users DESC;
@ishamajumdar5580 11 күн бұрын
My Approach : WITH cte AS ( SELECT *, id - ROW_NUMBER() OVER(ORDER BY visit_date) AS rn FROM stadium WHERE people >= 100 ) SELECT * FROM cte WHERE rn = (SELECT rn FROM cte GROUP BY rn HAVING COUNT(visit_date) >= 3 );
@ishamajumdar5580 11 күн бұрын
My Approach : WITH cte AS ( SELECT *, id - ROW_NUMBER() OVER(PARTITION BY num ORDER BY id) AS diff FROM series1 ) SELECT num FROM cte GROUP BY num, diff HAVING COUNT(*) >= 3;
@bhupeshsolanki289 11 күн бұрын
with cte as ( select Company_Id, User_Id, group_concat(Language) as grp from Company_user group by Company_Id, User_Id ) select * from cte where grp = 'German,English' or grp = 'English,German';
@sachinn5503 11 күн бұрын
WITH CTE AS( SELECT *, count(USER_ID) OVER(PARTITION BY USER_ID) ATLEAST_2 FROM COMPANY_USER WHERE LANGUAGE IN( 'ENGLISH' ,'GERMAN')) SELECT * FROM CTE WHERE G=2;
@neejudeshwal6515 12 күн бұрын
Thanks sir
@MathanRJ-p5c 13 күн бұрын
with temp as ( select user_id from Company_user where Language in ("English","German") group by user_id having count(Language) = 2 ) select company_id from Company_user where user_id in (select user_id from temp) group by company_id having count(distinct(user_id)) >= 2
@kshetrabasiMohanta-q4e 13 күн бұрын
select user_id,count(language) from company_user where language in('English','German') group by user_id having count(language)=2
@SK-kg6en Күн бұрын
This is exactly what I thought too. Since the data is less, you were able to put in as not in Spanish. But in a large table, its not easy to filter the data we dont need. The best one is to put in the filter on the data we need.
@prasaddasai 15 күн бұрын
sound quality vary poor, but vedio contion good
@Thedata.techclub 13 күн бұрын
Appreciate the kind words about the content! I'll work on the audio.
@prasaddasai 15 күн бұрын
voice not clear
@prasaddasai 15 күн бұрын
thank
@prasaddasai 16 күн бұрын
thanks
@prasaddasai 16 күн бұрын
please provide table
@AshishBusinessAnalyst-l7u Ай бұрын
please provide dataet also
@ashishparmar0914 Ай бұрын
sir increase sound quality please
@HARSHRAJ-gp6ve Ай бұрын
Plese provide the ddl and dml commands for practice for small dataset or the dataset you have used in your video
@chugalirani18 Ай бұрын
amazing series
@parthchauhan9305 Ай бұрын
Alternate approach with OVER clause: - with cte as ( SELECT *, COUNT(name) OVER (partition by salary) as cnt FROM company ) select dense_rank() OVER (order by salary) as team_id, emp_id,name,salary from cte where cnt = 2
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select * FROM new_sale ),cte1 as( select cte.*,new_sale.salary as salary1 FROM cte JOIN new_sale ON cte.Date>=new_sale.Date ORDER BY Date ) select Date,SUM(salary1) as sum2 FROM cte1 GROUP BY Date ORDER BY Date;
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select playbook_users.user_id,language,device FROM playbook_users JOIN playbook_events ON playbook_users. user_id=playbook_events.user_id ) select language,COUNT(DISTINCT user_id) as total_count, COUNT(DISTINCT CASE when device IN ('MacBook-Pro','iPhone 5s','iPad-air') THEN user_id ELSE NULL END) as apple_users FROM cte GROUP BY language;
@HARSHRAJ-gp6ve Ай бұрын
Bro if you find any kind of error in my query on any solutions I post in it, plese comment also for the same
@Thedata.techclub Ай бұрын
Sure
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select customer_id,COUNT(*) as c1, COUNT(CASE when client_id="desktop" THEN 1 ELSE NULL END) as x1 FROM fact_events GROUP BY customer_id ) select customer_id FROM (select cte.*,DENSE_RANK()OVER(ORDER BY c1 DESC) as r1 FROM cte where c1=x1) as es where r1=1;
@mattmatt245 Ай бұрын
WITH ConsecutiveDownPeriods AS ( SELECT Date, ServerStatus, ROW_NUMBER() OVER (PARTITION BY ServerStatus ORDER BY Date) - ROW_NUMBER() OVER (ORDER BY Date) AS GroupNum FROM YourTableName ) SELECT MAX(DATEDIFF(day, MIN(Date), MAX(Date)) + 1) AS MaxConsecutiveDownDays FROM ConsecutiveDownPeriods WHERE ServerStatus = 'down' GROUP BY GroupNum;
@kailashchowdhury2162 Ай бұрын
nice video
@Thedata.techclub Ай бұрын
Thank you 👍
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select salary,department FROM db_employee JOIN db_dept ON db_employee.department_id=db_dept.id ),cte1 as( select department,MAX(salary) as dept_sal FROM cte GROUP BY department HAVING department IN ('marketing','engineering') ),cte2 as( select cte1.*,LEAD(dept_sal)OVER() as second_dept_sal FROM cte1 ) select (dept_sal-second_dept_sal) as diff FROM cte2 where (dept_sal-second_dept_sal) is not null;
@aravindareddy7267 Ай бұрын
Can we use max(salary) over (partition by department_id)
@Thedata.techclub Ай бұрын
Yes, it will work.
@HARSHRAJ-gp6ve Ай бұрын
select MONTHNAME(month) as month_name, SUM(CASE when category="Electronics" THEN sales ELSE NULL END) as Electronics, SUM(CASE when category="Clothing" THEN sales ELSE NULL END) as Clothing, SUM(CASE when category="Grocery" THEN sales ELSE NULL END) as Grocery FROM sales_data GROUP BY MONTHNAME(month);
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select orderss.*,DENSE_RANK()OVER(PARTITION BY customer_id ORDER BY order_date DESC) as r1 FROM orderss ),cte1 as( select customer_id,order_amount as latest_order_amount,order_date FROM cte where r1<=2 ),cte2 as( select cte1.*,LEAD(latest_order_amount)OVER(PARTITION BY customer_id ORDER BY order_date DESC) as second_latest_order_amount FROM cte1 ) select customer_id,latest_order_amount,second_latest_order_amount FROM cte2 where second_latest_order_amount is not null; Bro keep putting sql questions daily on your channel
@Thedata.techclub Ай бұрын
Good job 👍
@TwiceMind Ай бұрын
Sir will you help how to apply or update on how to prepare for Data Analytics especially with SQL and the project behind it. as I am a career gap graduate... don't know how to comeback in life
@Thedata.techclub Ай бұрын
Re-entering the workforce after a career gap can seem challenging, but with a focused approach, especially in high-demand fields like Data Analytics, you can build a successful path forward. Learn SQL basics to advanced topics (e.g., JOIN, window functions) and explore tools like Python, Excel, and Tableau. Use platforms like Coursera, Kaggle, and LeetCode for practice and certifications. Analyze public datasets, create dashboards, and showcase work on GitHub. Connect with professionals, join webinars, and target entry-level roles (e.g., Data Analyst, SQL Developer). You’re not alone in this journey-many have successfully transitioned into Data Analytics after a gap. Stay focused, consistent, and confident. Let me know if you’d like help refining your resume, LinkedIn profile, or portfolio!
@parthchauhan9305 Ай бұрын
This is my solution: - select c.name from Orders o join customer c on o.customer_id = c.id join product p on o.product_id = p.id group by customer_id having SUM(case when p.name = 'Samsung' then 1 when p.name = 'iPhone' then 2 else 0 end) = 3 Just a little tweak in the case when statement, everything else is same.
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select service_log.*,ROW_NUMBER()OVER()as r1 FROM service_log ),cte1 as( select cte.*,ROW_NUMBER()OVER(PARTITION BY status ORDER BY updated_time) as r2 FROM cte ),cte2 as( select service_name,updated_time,status,(r1-r2) as r3 FROM cte1 ),cte3 as( select r3,MIN(updated_time) as start_time,MAX(updated_time) as end_time FROM cte2 group by r3 having count(*)>=5 ) select service_name,status,start_time,end_time FROM cte3 JOIN cte2 where cte3.r3=cte2.r3 and cte3.start_time=cte2.updated_time or cte3.end_time=cte2.updated_time LIMIT 1;
@ashusharma9439 Ай бұрын
Thanks sir select *, concat( Upper(left(content_text,1)), Lower(substring(content_text,2,length(content_text)))) from user_content; Mysql version
@HARSHRAJ-gp6ve Ай бұрын
sir provide the ddl and dml commands
@HARSHRAJ-gp6ve Ай бұрын
with cte as( select * FROM oscar_nominees where winner=1 ),cte1 as( select top_genre FROM cte JOIN nominee_information ON cte.nominee=nominee_information.name ) select top_genre FROM cte1 GROUP BY top_genre ORDER BY COUNT(*) DESC LIMIT 1;