วีดีโอ

Here is my approach : select concat(year(month),"-0", month(month)) as `month`, max(case when category = 'Clothing' then sales end) as Clothing, min(case when category = 'Electronics' then sales end) as Electronics, max(case when category = 'Grocery' then sales end) as Grocery from paypal_sales_data group by 1 order by 1 desc;

Nice video! I am new to this field, and I learned a lot from it. A small tip-please share the data link so we can also practice. Also, consider making a video on a portfolio project. Thanks for sharing your knowledge!

Highly appreciate!

That's a great approach of problem solving Sir, Make more videos of SQL Questions , Sir . 😀

keep going sir appreciate your work

with cte as( select *, DATEADD(DAY, -1*ROW_NUMBER() over (partition by employee, status order by dates), DATES) FLAG from ATTENDANCE_TUE ), cte2 as( select *, FIRST_VALUE(DATES) over (partition by employee, FLAG order by DATES) start_date, FIRST_VALUE(DATES) over (partition by employee, FLAG order by DATES DESC) end_date from cte ) select distinct employee,start_date , end_date, status from cte2

My Approach : WITH cte AS ( SELECT *, DAY(dates) - ROW_NUMBER() OVER(PARTITION BY employee, status ORDER BY Dates) AS diff FROM attendance ORDER BY dates ) SELECT employee, MIN(dates) AS From_Date, MAX(dates) AS To_Date, status FROM cte GROUP BY employee, status, diff;

If we have N number of rows we can't write N number of lag functions right?

Nice explanation 👌 👍 👏

Super 👍

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;

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);

Why is the volume always less in your videos?

Superb explanation 👌 👏 👍

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

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

Happy to see you back

more video on joins

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;

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;

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;

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;

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;

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;

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 );

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;

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';

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;

Thanks sir

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

select user_id,count(language) from company_user where language in('English','German') group by user_id having count(language)=2

sound quality vary poor, but vedio contion good

voice not clear

thank

thanks

please provide table

please provide dataet also

sir increase sound quality please

Plese provide the ddl and dml commands for practice for small dataset or the dataset you have used in your video

amazing series

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

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;

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;

Bro if you find any kind of error in my query on any solutions I post in it, plese comment also for the same

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;

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;

nice video

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;

Can we use max(salary) over (partition by department_id)

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);