Пікірлер

• 
  @dasubabuch1596Күн бұрын

  select Initcap(name)||'('||substr(designation,1,1)||')' as Output from labemp;

• 
  @sayedraeespeer9 күн бұрын

  For how many years of experience was this question relevant?

• 
  @gurupradeep964815 күн бұрын

  with cte as( select *,iif(lag(total_sales_revenue,1,total_sales_revenue)over(Partition by product_id order by year)<=total_sales_revenue ,1,0) As isincreasing from #sales) select Distinct Product_id from cte a where isincreasing=all(select isincreasing from cte where product_id=a.product_id)

• 
  @user-lx1ck9bn2j17 күн бұрын

  with cte as (select s.sid,'new in source' as comment from sources s left join targets t on s.sid=t.tid where t.tid is null union all select t.tid,'Mismatch' as comment from targets t join sources s on t.tid=s.sid where t.tname<>s.sname union all select t.tid,'new in Target' as comment from targets t left join sources s on t.tid=s.sid where s.sid is null ) select * from cte

• 
  @vinil921217 күн бұрын

  with cte as ( select company_id,user_id from company_users where language in ('English','German') group by user_id,company_id having count(language)=2 ) select company_id FROM cte group by company_id having count(user_id)>=2;

• 
  @Savenature63520 күн бұрын

  my approach in mysql : with fathers as (select p.id,p.name as father_name,r.c_id from people p inner join relations r on p.id=r.p_id where p.gender='M'), mothers as ( select p.id,p.name as mother_name,r.c_id from people p inner join relations r on p.id=r.p_id where p.gender='F') select mother_name,father_name,pl.name as child_name from mothers m join fathers f on m.c_id=f.c_id join people pl on f.c_id=pl.id order by pl.name;

• 
  @Savenature63520 күн бұрын

  Here is my approach : with cte as (select team_id as team_id,count(1) as team_cnt from employee_team group by 1) select employee_id,ifnull(team_cnt,0) as team_size from cte c right join employee_team e on c.team_id=e.team_id;

• 
  @radosvetjosifov184025 күн бұрын

  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"

• 
  @Abhilash-s2g25 күн бұрын

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

• 
  @Savenature63526 күн бұрын

  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;

• 
  @codehimode29 күн бұрын

  Nice✌️

• 
  @chandanpatra105329 күн бұрын

  so much background noise.🤷‍♂🤷‍♂

• 
  @Code-Con26 күн бұрын

  will try to improve

• 
  @tanmoykarmakar3917Ай бұрын

  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

• 
  @shyamshivakumar7807Ай бұрын

  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 )

• 
  @king-hc6viАй бұрын

  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 ❤

• 
  @krishna369-t4oАй бұрын

  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)

• 
  @krishna369-t4oАй бұрын

  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.

• 
  @Tech_with_SriniАй бұрын

  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

• 
  @saib7231Ай бұрын

  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

• 
  @Hope-xb5jvАй бұрын

  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

• 
  @vasanthkumar-zw3xfАй бұрын

  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

• 
  @Tech.S7Ай бұрын

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

• 
  @Code-ConАй бұрын

  yeah

• 
  @user-rh8ps7ue1xАй бұрын

  Nice explanation,and good question though ✅

• 
  @Skd833Ай бұрын

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

• 
  @Naveen-uz4hwАй бұрын

  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

• 
  @rohithr9122Ай бұрын

  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?

• 
  @code_with_logic449Ай бұрын

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

• 
  @nareshgiduthuri78472 ай бұрын

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

• 
  @nareshgiduthuri78472 ай бұрын

  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;

• 
  @maheshnagisetty44852 ай бұрын

  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

• 
  @jayavani22232 ай бұрын

  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

• 
  @gouthamstar65582 ай бұрын

  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;

• 
  @gouthamstar65582 ай бұрын

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

• 
  @gouthamstar65582 ай бұрын

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

• 
  @gouthamstar65582 ай бұрын

  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

• 
  @harshkumargupta13482 ай бұрын

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

• 
  @saib7231Ай бұрын

  no this is syntax wise all instead you can use this 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

• 
  @anime_7632 ай бұрын

  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

• 
  @MusicalShorts-hn1px2 ай бұрын

  Thanks for posting the problem along with data set

• 
  @user-ew2nw1my7r2 ай бұрын

  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

• 
  @king-hc6vi2 ай бұрын

  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.

• 
  @shryk0s9632 ай бұрын

  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

• 
  @suvadipkundu1522 ай бұрын

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

• 
  @Code-Con2 ай бұрын

  Try it out

• 
  @somanathking46942 ай бұрын

  Bro, Please post the string based scenario questions

• 
  @somanathking46942 ай бұрын

  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.

• 
  @Code-Con2 ай бұрын

  Keep it up bro!!

• 
  @snehithasingam99183 ай бұрын

  explanation 👌

• 
  @tamojeetchatterjee93853 ай бұрын

  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

• 
  @vijaygupta70593 ай бұрын

  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

• 
  @anirbanbiswas76243 ай бұрын

  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

• 
  @anirbanbiswas76243 ай бұрын

  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

• 
  @gouthamstar65582 ай бұрын

  this is a complex method, only works for this particular data, static methods are not good and useful for dynamic data updates

• 
  @vijaygupta70593 ай бұрын

  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