Видео

audio not clr

with cte as ( select sales_date, sum(case when fruits = 'apples' then sold_num end) apples, sum(case when fruits = 'oranges' then sold_num end) oranges from fruits_sales group by Sales_date ) select sales_date, apples-oranges as diff from cte

here are my two diff solution to approach this problem - ** advance sol using window function ** with cte as (select *, count(purchase_date) over(partition by customer_id) as purchase_count, min(purchase_date) over(partition by customer_id) as purchase_start, max(purchase_date) over(partition by customer_id) as purchase_end, timestampdiff(day,min(purchase_date) over(partition by customer_id),max(purchase_date) over(partition by customer_id)) as diff from practice.purchases) select * from cte where purchase_count = 2 and diff = 1 ** Normal solution without window function ** select customer_id, count(1), min(purchase_date) as start_date, max(purchase_date) as end_date, timestampdiff(day,min(purchase_date),max(purchase_date)) as diff from practice.purchases group by customer_id having timestampdiff(day,min(purchase_date),max(purchase_date)) = 1 and count(1) = 2

My Approach select customer_id from purchases group by customer_id having count(purchase_Date)=2 and datediff(day,min(purchase_Date) , max(purchase_Date))=1

couldn't understand how datediff function addressed the lead null values as difference would be 1 for first row of each customer_id...what about the second row wherein we've null values

WITH CTE AS ( select *,( purchase_date - lead ( purchase_date ) over ( partition by customer_id order by purchase_date )) as difference, ( amount + lead( amount ) over ( partition by customer_id order by purchase_date )) as total_sum from purchases ) select customer_id from cte where difference = -1;

select dep_id,max(salary), min(salary) from employee group by dep_id

With cte as( select *, min(salary) over(partition by dep_id order by dep_id) as min_salary, max(salary) over(partition by dep_id order by dep_id) as max_salary from employee) select distinct dep_id, min_salary, max_salary from cte

please find the below solution : with cte as(select a.product_id as pid,a.PRODUCT_NAME as pn,a.CATEGORY as c, case when (lead(b.TOTAL_SALES_REVENUE,1,0) over (order by a.product_id) > b.TOTAL_SALES_REVENUE and lead(b.TOTAL_SALES_REVENUE,2,0) over (order by a.product_id) > lead(b.TOTAL_SALES_REVENUE,1,0) over (order by a.product_id)) then a.product_id else null end as x from products a inner join sales b on a.product_id = b.product_id) select pid as product_id,pn as product_name,c as category from cte where x is not null;

Can u suggest where to learn SQL

select top 1 txnmonth, (clothing+electronics+sports) AS amount from eshop ORDER BY amount DESC;

Nice work Buddy🎉

My solution 😢😢😢😢😢😢😢

WITH CTE AS(SELECT p.*,S.year,S.total_sales_revenue, total_sales_revenue - LAG(S.total_sales_revenue,1,S.total_sales_revenue)OVER(pARTITION BY S.product_id ORDER BY S.YEAR) AS HIKE FROM products AS p LEFT JOIN SALES AS S ON p.product_id = S.product_id),V1 AS( SELECT *, CASE WHEN HIKE < 0 THEN 'D' ELSE 'Q' END AS STAT FROM CTE) SELECT product_id,product_name,category FROM V1 WHERE product_id NOT IN (SELECT product_id FROM V1 WHERE STAT = 'D') group by 1,2,3;

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

For how many years of experience was this question relevant?

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)

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

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;

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;

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;

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