Видео

You are doing very great job !!

Please use the Light theme

thank you habibi

Here rating is basically a category

Thanks for this 🙏🙏 please dont stop ur videos

few of my code in not working reason i don't know what is wrong even if i look at the code it as it is

Thank you for this project I'm really grateful to you... And I would like to advice you to make your personal playlist as private to make your account more professional ❤

Nice Spectacles Sir..😎

Sir, please use light theme..!

with cte as( select MONTH(submit_date) as month,product_id,stars FROM reviews ) select month,product_id,ROUND(AVG(stars),2) as avg_rating FROM cte GROUP BY month,product_id ORDER BY month,product_id;

Hats off for ur efforts and consistency bro

with cte as( select id,country,state,amount,SUBSTRING(trans_date,1,7) as half_month FROM Transactions ) select half_month,country,SUM(amount) as trans_total_amount,COUNT(*) as trans_count, SUM(CASE when state='approved' THEN amount ELSE NULL END) as approved_total_amount, COUNT(CASE when state='approved' THEN 1 ELSE NULL END) as approved_count FROM cte GROUP BY half_month,country;

with cte as( select departmentId,Salary,name,DENSE_RANK()OVER(PARTITION BY departmentId ORDER BY salary DESC) as x1 FROM employee ),cte1 as( select department.name as dept_name,cte.name as emp_name,Salary FROM cte JOIN department ON cte.departmentId= department.id where x1<=3 ) select * FROM cte1;

At the end final project is there right sir So this project is resume level project ?

Sir can you give me a brief introduction of your means What is your total year of experience In which company do you have worked so far Current tech stack and also mentioned previously used ones also

with cte as( select * FROM tweets where YEAR(tweet_date)=2022 ),cte1 as( select user_id,COUNT(*) as x1 FROM cte GROUP BY user_id ) select x1,COUNT(user_id) as x2 FROM cte1 GROUP BY x1;

nice explaining...thank you...

with cte as( select * FROM product_spend where YEAR(transaction_date)=2022 ),cte1 as( select category,product,SUM(spend) as sum1 FROM cte GROUP BY category,product ) select category,product,sum1 FROM(select cte1.*,DENSE_RANK()OVER(PARTITION BY category ORDER BY sum1 DESC) as x1 FROM cte1) as es where x1<=2;

with cte as( select 1 as value,device_type,viewership_count FROM viewership ) SELECt SUM(CASE WHEN device_type IN ('tablet', 'phone') THEN viewership_count ELSE 0 END) AS mobile_users, SUM(CASE WHEN device_type = 'laptop' THEN viewership_count ELSE 0 END) AS laptop_users FROM cte GROUP BY value;

Please dont break your consistency You are doing a great job

Amazin, wish you all the best.

select e.id,e.name as employee ,e.salary,m.id,m.name as manager,m.salary from employees e join employees m on e.manager_id=m.id and e.salary >m.salary;

My solution easy way: with cte as ( SELECT DISTINCT customer_id, strftime('%m', purchase_date) as mnth FROM amazon_sales where purchase_date like '%2024%' ) select customer_id, count(mnth) as cnt from cte group by customer_id having cnt =7

select a.* from saless a join saless b on b.saledate = date_add( a.saledate, interval '1 day') where abs(a.saledate - b.saledate) = 1 and a.productid = b.productid

select saleid,productid , saledate , quantity from( select *, count(consecutive_date) over(partition by consecutive_date) from ( select * , (saledate - interval '1 day' * rank)::date as consecutive_date from (select * , dense_rank() over(partition by productid order by saledate) as rank from Sales_3) x) y) z where count >= 2 and rank =1

select productid, saledate from ( select *, lead(saledate) over(partition by ProductID order by saleid), abs(saledate - lead(saledate) over(partition by ProductID order by saleid)) as difference from sales) where difference = 1

with cte as( select saleID,ProductID as Productid,lead(SaleDate) over(order by SaleID) as nextDay from Sales ) select s.ProductID,min(s.SaleDate) from Sales s join cte c on c.SaleID=s.SaleID where DateAdd(day,1,s.SaleDate)=c.nextDay and s.ProductID = c.Productid group by s.ProductID;

with cte as( select Sales.*,LEAD(SaleDate)OVER(PARTITION BY ProductID) as x1 FROM Sales ),cte1 as( select ProductID,DATEDIFF(x1,SaleDate) as x2 FROM cte ),cte2 as( select ProductID,COUNT(x2) FROM cte1 where x2=1 GROUP BY ProductID HAVING COUNT(x2)>=1 ),cte3 as( select Sales.*,DENSE_RANK()OVER(PARTITION BY productID ORDER BY SaleDate) as x3 FROM Sales ) select SaleID,cte3.ProductID,saleDate,quantity FROM cte2 JOIN cte3 ON cte2.ProductID=cte3. ProductID where x3=1;

select o.customer_id as customer_id, sum(o.total_items_ordered) as total_items_ordered, sum(r.returned_items) as total_items_returned, round((sum(r.returned_items)/sum(o.total_items_ordered) * 100), 2) as return_percent from orders as o join returns as r on o.order_id = r.order_id group by customer_id order by return_percent desc limit 5; this query gives the same output.

Instead of joins I used lag. My approach With cte as ( SELECT SALEID, PRODUCTID, QUANTITY, SALEDATE, LAG (SALEDATE) OVER(PARTITION BY PRODUCTID ORDER BY SALEDATE) AS PREV_ DATE FROM SALES) SELECT PRODUCTID, min (PREV DATE) as dates FROM CTE WHERE abs (DATEDIFF(DAY,SALEDATE, PREV_DATE)) =1 group by PRODUCTID Can you please reviews final answer and say if it's correct or wrong approach

with cte as( SELECT Employees.*,DENSE_RANK()OVER(ORDER BY salary DESC) as x1 FROM Employees ) select Employees.* FROM Employees where salary=(select DISTINCT(salary) FROM cte where x1=2); select Employees.* FROM Employees where salary=(select DISTINCT(salary) FROM cte where x1=4); select Employees.* FROM Employees where salary=(select DISTINCT(salary) FROM cte where x1=6);

with cte as( select Employees.*,ROW_NUMBER()OVER(PARTITION BY id,name,salary,department_id, manager_id,hire_date) as x1 FROM Employees ) select id,name,salary,department_id,manager_id,hire_date FROM cte where x1=1;

Easy !! but Awesome !! keep doing !!

How would it be different if I want to do it in MySQL? Meaning what changes or points I need to take under considerations if I do it parallelly while watching your video? Please let me know as it would help me with other project videos that you have uploaded as well. I have completed courses on SQL and new to making projects.

bro i think in #4 we have to "TRIM" our query so that {'United States'} and {' United States'} can be combine into single {'United States'} ~~

Thankyou fr bringing this out, i was soo scared to learn sql but your way of teaching is making it easier . Thank you for all the efforts and time you have put to bring this 💖💯

Please comment mysql approach...

Can i not use mysql for the same project?

in postgres sql you using ilike so my doubt is i am using sql workbench so what i can use i think ilike is not supported in mysql ?

hi you can solve this questions in mysql workbench thank you

Where can I find questions to practice them ??

with cte as (select *,row_number() over(partition by gender order by id) as rn from students) select id,name,gender from cte order by rn,gender desc;

😍

Empl_id, EMP_Name, salary Tax .20 = (0-10000)rs .30 = 0-30000 rs Tax after deduction Highest lowest

Plz make one real time Python(pandas) portfolio project and another Excel project in detail as you j=have made this one

with cte as( select (select COUNT(*) FROM uber_ride where ride_status='cancelled_by_driver') as x1, (select COUNT(*) FROM uber_ride where ride_status LIKE 'cancelled%') as x2 FROM uber_ride LIMIT 1 ) select ROUND(((x1/x2)*100),2) as percentage_ride_cancelled_driver FROM cte;

with cte as( SELECT * FROM user_purchases where MONTH(date) BETWEEN 0 AND 3 ),cte1 as( select user_id,WEEK(date) as week1,amount_spent FROM cte where day_name='Friday' ) select week1 as week_num,AVG(amount_spent) as avg_spend_friday FROM cte1 GROUP BY week1;

hlo sir, can you tell how you move all the table name to right indentation ? like in video you select all the things and then you move it in right. but how?

with cte as( select order_id,HOUR(order_time) as hour,customer_id,total_amount FROM order_data ) select hour,COunt(*) as cnt_orders,(COUNT(*)/(select COUNT(*) FROM cte)*100) as order_percentage FROM cte GROUP BY hour ORDER BY (COUNT(*)/(select COUNT(*) FROM cte)*100) DESC;

with cte as( select category,order_id FROM amazon_products JOIN return_records ON amazon_products.product_id=return_records. product_id ) select category,(COUNT(DISTINCT order_id)/(select COUNT(*) FROM return_records))*100 as percentage FROM cte GROUP BY category ORDER BY (COUNT(DISTINCT order_id)/(select COUNT(*) FROM return_records))*100 DESC, category DESC;