Видео

not sure if i understood the problem correctly by the example but i think this works perfectly fine w/o multiple ctes: "SELECT TOP 1 txnmonth FROM eshop ORDER BY clothing + electronics + sports DESC"

--Find the company who have alteast 2 users who speaks both English and German. with cte as ( select company_id,USER_ID,language, DENSE_RANK()over(partition by USER_ID order by language)as rn, count(language)over(partition by USER_ID)as cnt from Google_Intermediate_Interview where language in ('English', 'German') ) select company_id,USER_ID,language from cte where cnt >= 2

My approach : with cte as (select *,lead(flag) over(partition by empd_id order by swipe) as next_flag, lead(swipe) over(partition by empd_id order by swipe) as next_log from clocked_hours) select empd_id,cast(sum(case when flag='I' and next_flag='O' then cast(next_log-swipe as time) else 0 end) as time) as total_clocked_hrs from cte group by 1;

Nice✌️

so much background noise.🤷‍♂🤷‍♂

WITH cte AS (select student_id, school, class, subject from students where subject IN ('maths','physics')) select school, class, COUNT(DISTINCT student_id) AS student_count from cte where student_id IN (select student_id from cte group by student_id having COUNT(subject) >=2) group by school, class having student_count >=1; This will be the answer

select * from products where product_id not in ( select distinct product_id from ( select *, coalesce(total_sales_revenue-lag(total_sales_revenue) over(partition by product_id order by year asc),0) as lag_col from sales)a where a.lag_col<0 )

We can create case statement as well right? If current destination > Prev destination then 1 else 0 .. And then filter the result where we have only 1 Kindly let me know if this works or not.. Many thanks ❤

Thanks for the video. I used the lag function and used distinct instead of max in the output line. Is it valid? Please check. with cte as ( select p.product_id, p.product_name, s.year, total_sales_revenue, LAG(total_sales_revenue,1) over (partition by p.product_id order by year) as prev_year_revenue from products p join sales s on p.product_id = s.product_id --order by p.product_id, s.year ) select distinct product_id, product_name from cte where product_id not in (select product_id from cte where total_sales_revenue < prev_year_revenue)

I used self join: select p.name, max(case when pp.gender = 'F' then pp.name end) Mother, max(case when pp.gender = 'M' then pp.name end) Father from people p join relations r on p.id = r.c_id join people pp on pp.id = r.p_id group by p.name Thanks for the video.

with cte as ( select empNo,eName,sal,deptno , max(sal) over (partition by deptno) as max_sal , min(sal) over (partition by deptno) as Min_sal from emp2) select c1.empNo,c1.eName,c1.deptno,c2.max_sal,c2.min_sal from cte c1 join cte c2 on c1.empno=c2.empno where c1.sal= c2.max_sal or c1.sal=c2.min_sal order by c1.deptno

Select taxmonth, sum(cast(clothing as int)+cast(electronics as int)+cast(sports as int)) as total_sales From eshop Group by taxmonth order by total_sales desc limit 1 this als works

other method----- select st.student_id ,st.student_name ,s.subject_name ,NULLif(count(e.subject_name),0) as No_of_times_appeared from Students st cross join Subjects s left join Examinations e on e.student_id = st.student_id and e.subject_name = s.subject_name group by st.student_id,st.student_name,s.subject_name order by st.student_id

select sales_date, sum(case when fruits='apples' then sold_num end) apple_count, sum(case when fruits='oranges' then sold_num end) orange_count, sum(case when fruits='apples' then sold_num end) - sum(case when fruits='oranges' then sold_num end) diff_count from sales group by sales_date

Are the two users 1 and 3 who are working in company 1? Does this correct?

Nice explanation,and good question though ✅

select [dep_id],max([salary]) as higest_salary, min([salary]) as lowest_salary from [Sambit].[dbo].[emp] group by [dep_id]

select distinct cust_id from (select * ,min(order_date) over(partition by cust_id order by order_date) as min_date ,max(order_date) over(partition by cust_id order by order_date desc) as max_date from transactions)a where (month(max_date)-month(min_date))=1

select * from( select sid , case when sname <> tname then 'Mimatched' when tid is null then 'New in sources' end Review from sources as s left join targets t on s.sid = t.tid) t1 where Review is not null union select tid , 'New in targets' from targets where tid not in( select sid from sources) is this correct solution?

copy past question and solution, why you do like this do some real good question

select txnmonth from eshop where clothing + electronics + sports = ( select max(clothing + electronics + sports) from eshop);

Thanks for your content. Can you please add below in description which would help create table merchant(merchant_id varchar(20) , amount int, payment_mode varchar(20)); insert into merchant values ('m1',200,'cash'),('m2',520,'online'),('m1',700,'online'),('m3',1400,'online'),('m2',50,'cash'),('m1',300,'cash'); select * from merchant;

select user_id,company_id from ( select count(rn) as cunt,user_id,company_id from ( select company_id,user_id,language,rank() over (partition by company_id order by user_id ) rn from company_users where language in('english','german') ) as a group by user_id,company_id ) as b where cunt >=2

SELECT D.dep_Name, MAX (salary) AS highest_salary, MIN(Salary) AS lowest_salary FROM EMployees E LEFT JOIN DEPARTMENTS D ON E.DEP_ID = D.DEPT_ID_DEP GROUP BY D.DEP_NAME Please let me know is it correct or wrong

with cte as (select *, lead(Fruits) over(partition by Sales_date order by sales_date) le_fu111, lead(sold_num) over(partition by Sales_date order by sales_date) le_fu11 from sales) select *, sold_num-le_fu11 from cte where le_fu11 is not null;

emp id, 4,7 are not 3rd highest salary

I got emp id--2, 6,9,10

with cte as ( select *, count(dep_id) over(partition by dep_id ) as dep_count, ROW_NUMBER() over(partition by dep_id order by emp_salary ) as rn from employees ) select * from cte where dep_count >=3 and rn=3 union select * from cte where dep_count <3 and rn=1 order by rn desc

Cant we do this Select taxmonth, Max(clothing+electronics+sports) From eshop Group by 1

My solution with cte as ( SELECT * ,LEAD(total_sales1_revenue,1) OVER(PARTITION BY product_id ORDER BY year) Year1 ,LEAD(total_sales1_revenue,2) OVER(PARTITION BY product_id ORDER BY year) year2 ,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY year) as RW FROM sales1 ) ,cte2 as( SELECT *, CASE WHEN (total_sales1_revenue < Year1 ) AND (Year1 < Year2) THEN 1 ELSE 0 END flag FROM cte WHERE RW = 1 ) SELECT P.* FROM products1 P JOIN cte2 C ON P.product_id=C.product_id WHERE C.flag = 1

Thanks for posting the problem along with data set

Simple Query select Dep_id,avg(salary) as avg_salary,min(salary) as Min_salary, Max(salary) as max_salary from table name group by Dep_id order by dep_id

Can we do partition on Payment mode and seperate the online and cash mode with row number as 1 for cash and row number as 2 for online.... And then case statement in which sum of R1 gives the value and puts 0 for online. Similarly another case statement in which sum of R2 gives the value and puts 0 for cash. Please let me know if this approach is correct or not.

with cte as (select *,(lead(total_sales_revenue) over (partition by product_id order by year)-total_sales_revenue)as x from sales) select distinct c.product_id,p.product_name from cte c inner join productss p on c.product_id=p.product_id where c.product_id not in (select product_id from cte where x<0) my solution

can we do it without using a CTE? i suspect we can , though unlikely to be optimal, thoughts??

Bro, Please post the string based scenario questions

with cte as (select *,count(team_id) over(partition by team_id) [count] from Employee_Team) select employee_id,count from cte order by employee_id; thanks bro! i have solved it ony own, by seeing your past practice videos. I know i have solved very easy one, but i understand and enjoyed this. it took me 4 attempts to achieve this.

explanation 👌

My solution with cte as (select empd_id , swipe_time , flag as f , lag(swipe_time) over(partition by empd_id) as new_time from clocked_hours) select empd_id , extract(hour from sum(swipe_time - new_time)) as clicked_hrs from cte where f = 'O' group by empd_id

my solution in mssql DB : with child as (select p1.*,r1.p_id from people as p1 inner join relations as r1 on p1.id=r1.c_id union select p1.*,r1.p_id from people as p1 inner join relations as r1 on p1.id=r1.c_id ), main as ( Select child.name as child_name ,people.name as perent_name , people.gender from child inner join people on child.p_id=people.id ) Select child_name ,max(case when gender ='F' then perent_name else null end) as father ,max(case when gender ='M' then perent_name else null end) as mother from main group by child_name

with cte as(select *,lag(sold_num) over(partition by sales_date order by sales_date) as no_of_fruits, row_number() over(partition by sales_date order by sales_date desc) as rn from sales), cte2 as (select *,case when rn=2 then abs(sold_num-no_of_fruits) else False end as otpt from cte) select * from cte2 where otpt != 0

with cte as (select emp_name,emp_salary,dep_id,count(*) over(partition by dep_id) as cnt, rank() over(partition by dep_id order by emp_salary desc) as rnk from employees), output_case as(select emp_name,emp_salary,dep_id,cnt,rnk, case when cnt=4 then rnk=3 else null end as final, case when cnt=3 then rnk=3 else null end as final2, case when cnt=2 then rnk=2 else null end as final3, case when cnt=1 then rnk=1 else null end as final4 from cte) select emp_name,emp_salary,dep_id from output_case where final=1 or final2=1 or final3=1 or final4=1 I KNOW this code is bit lengthy but still solves the purpose easily

same solution as you in MSSQL DB Select merchant_id ,sum(case when payment_mode = 'cash' then amount else 0 end ) as Cash ,sum(case when payment_mode = 'online' then amount else 0 end ) as Online from payments group by merchant_id order by sum(case when payment_mode = 'cash' then amount else 0 end) desc

my solution on MSSQL DB: with cte as ( Select * ,case when total_sales_revenue< lead(total_sales_revenue,1, total_sales_revenue+1)over(partition by product_id order by year) then 1 else null end as new from sales ), sales_cte as( Select * from sales where product_id not in (select product_id from cte where new is null) ) select products.* from sales_cte inner join products on sales_cte.product_id = products.product_id group by products.product_id, products.product_name, products.category

with cte as( select candidate_id,datepart(year,vote_date)*100+''+datepart(month,vote_date) as weeks ,count(voter_id) counts from elections group by datepart(year,vote_date)*100+''+datepart(month,vote_date),candidate_id ),cte2 as( select * ,RANK() OVER(PARTITION BY weeks order by counts desc)rn from cte) select * from cte2 where rn=1;

select p1.name as name ,max(case when p2.gender='F' then p2.name end) Mother ,max(case when p2.gender='M' then p2.name end) Father from relations r inner join people p1 on p1.id=r.c_id inner join people p2 on p2.id=r.p_id group by p1.name;

Great content 👏 I have approached this in 2 ways, please share your comments 1. Using 2 CTEs, one having Child name, id & gender of child and second CTE is for holding Parent-Child info WITH CHILD AS ( SELECT c_id, p_id, p.name as Child_Name, gender as Child_Gender from people p inner join relations r on id = c_id ), Relation AS ( SELECT Child_Name, MAX(CASE WHEN p.gender = 'F' Then p.name END) Mother, MAX(CASE WHEN p.gender = 'M' Then p.name END) Father FROM people p inner join CHILD c on p.id = c.p_id GROUP BY Child_Name ) select * from Relation; 2. Using 3 CTEs, one additional CTE with both child & parent info WITH CHILD AS ( SELECT c_id, p_id, p.name as Child_Name, gender as Child_Gender from people p inner join relations r on id = c_id), PARENT_CHILD AS ( SELECT C.c_id AS CHILD_ID, Child_Name, id AS PARENT_ID, p.name as Parent_Name, p.gender as Parent_Gender FROM people p inner join CHILD c on p.id = c.p_id), FINAL AS ( SELECT Child_Name, MAX(CASE WHEN Parent_Gender = 'F' Then Parent_Name END) Mother, MAX(CASE WHEN Parent_Gender = 'M' Then Parent_Name END) Father FROM PARENT_CHILD GROUP BY Child_Name) select * from FINAL;

Hindi bolo sir.. smjh nhi aata

Thank you. Do you think this will work for not consecutive month order date and different year order date. Suppose cust_id 1 has ordered in Jan, Feb and May. So ideally this id should come for Feb churn analysis but this will not come with this solution approach. Please correct me if I am wrong.

select * from ( select *,ROW_NUMBER() over(partition by flagtab.concat order by n) as flag from ( select *, case when n>m then CONCAT(n,m) else CONCAT(m,n) end as concat from tb1) as flagtab)as new where new.flag=1