PWC SQL Interview Question | BIG 4 |Normal vs Mentos Life 😎

In this video we will discuss a problem asked in pWC SQL interview. we will solve this problem with 2 methods. Here is the script to create table and insert data:
create table company_revenue
(
company varchar(100),
year int,
revenue int
)
insert into company_revenue values
('ABC1',2000,100),('ABC1',2001,110),('ABC1',2002,120),('ABC2',2000,100),('ABC2',2001,90),('ABC2',2002,120)
,('ABC3',2000,500),('ABC3',2001,400),('ABC3',2002,600),('ABC3',2003,800);
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@ankitbansal6]

Hit the like button if you want more BIG 4 problems. Master the art of SQL @ Rs 1999 with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch. www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354 Some salient features of the course: 1- No prerequisite. All concepts have been covered from absolute basics. 2- Course contains 2 portfolio projects 3- 100+ interview problems to crack any SQL INTERVIEW 4- A TRUE bonus of 5000 (access to premium account to a SQL practice website). 5- You will also be part of premium data community where you can ask any doubts. 6- A bonus session on Tableau. #sql #analytics

[@shankarpesala921]

WITH CTE AS ( select *,REVENUE-LAG(revenue) OVER (PARTITION BY COMPANY ORDER BY COMPANY) AS PREV from com_r) select distinct company from com_r where company not in (SELECT company FROM CTE WHERE PREV IS NOT NULL and prev

[@akshayjain7937]

select company,count(company),sum(case when abc>revenue then 1 else 0 end) from ( select *, coalesce(lead(revenue) over ( partition by company order by year asc),100000) as abc from company_revenue) as b group by company having count(company)=sum(case when abc>revenue then 1 else 0 end)

[@sobhiksaha7140]

Thanks Ankit Sir for the problem. Here's my approach (in MySQL): select company from (select *, case when ifnull((lag(revenue) over (partition by company order by year) - revenue),0)

[@user-gj2et8dd7i]

With your CASE-WHEN favourites, now i can also think of those direct solutions : with cte as ( select * ,case when revenue> lag(revenue,1,0) over(partition by company order by (select null)) then 1 else -1 end as flag from company_revenue ) select distinct company from company_revenue where company not in (select distinct company from cte where flag =-1)

[@avi8016]

Great demonstration of logic in Mentos life method 💯 Thankyou as always to bring such good quality questions!!!

[@ankitbansal6]

Glad you liked it!

[@RightLearningPath]

Hi, I like how easily you expalin things, thanks for coming up with this problem . I have tried this query , It's similar to the second solution explained in the video, -- All Companies SELECT [company] FROM company_revenue EXCEPT -- Companies where current year revenue is less than previous year's SELECT [company] FROM ( SELECT [company], revenue AS PriorYearRevenue, ISNULL((Lead(REVENUE,1) OVER(PARTITION BY [company] ORDER BY [year])),REVENUE) AS CurrentYearRevenue FROM company_revenue ) T WHERE T.CurrentYearRevenue < PriorYearRevenue

[@ankitb5563]

Hi Ankit , Thanks for sharing the question and solution. I have created a alternate solution : with cte as ( select company,year,revenue, CASE WHEN (revenue - lag(revenue) over(partition by company order by year asc)) > 0 then 0 else 1 end as rev_flag from company_revenue ) select distinct company from cte group by company having SUM(rev_flag)

[@Advanced_Learner]

thanks Ankit, here is my soln: with cte as ( select *, lag(revenue, 1,0) over (partition by company order by year asc) as previous_revenue from company_revenue ) select company from cte group by 1 having count(*) = sum(case when previous_revenue < revenue then 1 else 0 end)

[@apna9656]

Learning so many thing from your videos... Just gave a short for this problem with cte as( select *, lag(revenue,1,0) over(partition by company order by year) as newrev from company_revenue) ,cte2 as( select *, case when revenue > newrev then 1 else 0 end as flag from cte ) select company, min(flag) from cte2 group by company having min(flag) >0

[@azad_pal]

Very nice 👍

[@medleyworld5549]

Amazing solution. I tried without lag function and it gave me same result: with cte_min_rev as ( select company , min(revenue) as min_rev from company_revenue group by company), cte as ( select a.company, a.year, a.revenue, (a.revenue-b.min_rev) as differences, rnum = row_number() over(partition by a.company order by a.company, a.year) from company_revenue a inner join cte_min_rev b on (a.company = b.company)) select company, year, revenue from cte where rnum = 1 and differences = 0

[@rk-ej9ep]

Just awesome..

[@ranjansrivastava9256]

Well Explained !!

[@Thekingslayer-ig5se]

Back after long time Good one sir

[@ArijitDatta-pt6wi]

Hi Ankit , great content you are uploading man here is my solution on this question:- with cte1 as( select *, DENSE_RANK()over(partition by company order by revenue asc) as rnk_revenue, DENSE_RANK()over(partition by company order by year asc) as rnk_year from company_revenue) ,cte2 as( select *, case when rnk_revenue=rnk_year then 'Yes' else 'No' end as flag from cte1) select distinct company from company_revenue where company not in (select distinct company from cte2 where flag='No');

[@kunalgaurav3301]

Thanks for sharing Sir

[@sahilummat8555]

Hello Sir love your content ;with cte as ( select * , lag(revenue,1,0)over(partition by company order by year) as prev_rev, revenue-lag(revenue,1,0)over(partition by company order by year)as incdsc from company_revenue) select company from cte group by company having min(incdsc)>0

[@ranadeepghosh1891]

Excellent as usual❤

[@ankitbansal6]

Thank you! Cheers!

[@rohitbobson]

Thanks for posting this question, here's my take on it. WITH Company_inc_revenue AS ( SELECT company, year, revenue ,CASE WHEN revenue > LEAD(revenue,1) over (partition by company order by year asC) THEN 1 ELSE 0 END as NON_INC_FLAG FROM company_revenue ) SELECT company FROM Company_inc_revenue Group by company HAVING SUM(NON_INC_FLAG) = 0

[@Vaibha293]

with cte as( select *, (case when revenue < lead(revenue,1,revenue+1) over(partition by company order by year asc) then 1 else 0 end)counts from company_revenue) select * from company_revenue where company not in (select company from cte where counts = 0);

[@sumitahirwar9116]

Thankyou soo much Ankit . Trying after new video : SQL Magic Show | Solving a Tricky SQL Problem with 10 Methods | SQL Tutorial 😀 ;with cte as ( select *, case when lead(revenue,1,revenue) over (partition by company order by year) >= revenue then 1 else 10 end as flag from #company_revenue ) select company from cte group by company having sum(flag) = 3

[@dibakarmandal2148]

Hi Ankit, here is my solution 🙂 with cte AS ( select *, abs(ROW_NUMBER() OVER(partition by company order by year) - DENSE_RANK() OVER(partition by company order by revenue)) as dr from pwc_company_revenue ) select company from cte GROUP BY company having sum(dr)=0;

[@lintosunny6792]

Great solution! I expanded the analysis to cover all available years, addressing the challenge of varying data lengths for different companies. Here's the refined SQL code: WITH revenue_cte AS ( SELECT *, LEAD(revenue, 1, 0) OVER (PARTITION BY company ORDER BY [year]) AS revenue_2001, LEAD(revenue, 2, 0) OVER (PARTITION BY company ORDER BY [year]) AS revenue_2002, LEAD(revenue, 3, 0) OVER (PARTITION BY company ORDER BY [year]) AS revenue_2003 FROM company_revenue ) SELECT company FROM revenue_cte WHERE [year] = 2000 AND ( (revenue_2003 = 0 AND revenue < revenue_2001 AND revenue_2001 < revenue_2002) OR (revenue_2003 0 AND revenue < revenue_2001 AND revenue_2001 < revenue_2002 AND revenue_2002 < revenue_2003) );

[@VijayKumar-ho2fj]

Hi Ankit, with cte as( select company, year, revenue, lead(revenue) over(partition by company order by year asc) as next_yr_revenue, case when revenue > lead(revenue) over(partition by company order by year asc) then 0 else 1 end as flag from company_revenue ) select distinct company from company_revenue where company not in(select company from cte where flag = 0);

[@himanshugupta239]

The way of explanation is super 😊

[@ankitbansal6]

Glad you liked it😊

[@shrishkumar9725]

Hi Ankit, with cte as ( select *, revenue - max(revenue) over(partition by company order by year asc) as rn from company_revenue ) select company from cte group by company having min(rn) = 0

[@surajn4902]

My approach: with rev as( select company, year,revenue, lag(revenue) over(partition by company order by year)as prev_revenue from company_revenue ) select company from( select company,year,revenue,prev_revenue, case when revenue

[@biswanathprasad1332]

with tab as (select *,case when revenue>lead(revenue) over(partition by company order by year) then 1 end as next_rev from company_revenue) select company,count(next_rev) from tab group by company having count(next_rev)=0

[@rohanjamea2446]

For what role was the interview for ?

[@sudhirsingh-xl5gc]

with cte as ( select company,year,revenue, case when revenue>=last then 1 else 0 end as diff from ( select *,lag(revenue,1,revenue) over(partition by company order by year) as last from company_revenue) asw) select distinct company from cte where company not in (select company from cte where diff=0)

[@hiralalpatra500]

with cte as( select *,lead(revenue)over(partition by company order by year) as after_yr_rev from company_revenue) ,cte2 as(select *,case when after_yr_rev>revenue or after_yr_rev is null then 1 else 0 end as status from cte) select company,count(company) as cnt_com,sum(status) as sum_status from cte2 group by company having count(company)=sum(status)

[@rihanalli3754]

with cte as( select company,year,revenue,case when coalesce(dd,revenue)>= revenue then 1 else -1 end as flag from( select *,lead(revenue) over (partition by company order by year asc) as dd from company_revenue) a) select company,sum(flag),count(flag) from cte group by company having sum(flag)=count(flag)

[@kailashpatro5768]

with cte as ( select *, lag(revenue,1,0) over(partition by company order by year) as prev from company_revenue ) select company from ( select *, case when revenue > prev then 1 else 0 end as diff from cte ) a group by company having count(distinct diff) = 1

[@jonedrive7268]

with cte as (select * ,(case when revenue < lead(revenue, 1, revenue + 1) over(partition by company order by year) then 1 else 0 end) as flag from company_revenue) select company from company_revenue where company not in (select company from cte where flag = 0) group by company;

[@akp7-7]

My solution: with cte as (select *, case when ((lead(revenue,1,revenue+1) over(partition by company order by year))-revenue)>0 then 1 else 0 end as new_col from company_revenue) select company from cte group by company having sum(new_col)=count(new-col)

[@arghyadhar9361]

My Approach: with cte as(select *, (case when revenue < lead(revenue, 1, revenue+1) over(partition by company order by year) then 1 else 0 end) as flag from company_revenue) select company from company_revenue where company not in (select company from cte where flag =0) group by company

[@tamojeetchatterjee9385]

I have used sign function to solve this. with cte as (select company , year , revenue , lag(revenue , 1 , revenue)over(partition by company order by year) as next_year, sign(revenue - lag(revenue , 1 , revenue)over(partition by company order by year)) as sighn from company_revenue) , gte as (select company from cte where sighn = -1) select distinct company from company_revenue where company not in (select company from gte)

[@parth_pm16]

This was my solution with cte as (select * , case when Prv_Revenue > revenue then 1 else 0 end as cnt from ( select * ,lag(revenue, 1,0) over(partition by company order by year)as Prv_Revenue from company_revenue) A) select company from cte group by company having sum(cnt)=0

[@sdef719]

with cte as ( select *, dense_rank() over(partition by company order by year) - dense_rank() over(partition by company order by revenue) as year_rev_diff from company_revenue ) select company from cte group by company having count(distinct year_rev_diff) = 1 and sum(year_rev_diff) = 0

[@dasoumya]

Hello Ankit! Here is my solution: with cte as(select *,case when revenue>lag(revenue,1,revenue-1)over(partition by company order by year) then 1 else 0 end as flag from company_revenue) select company from cte group by company having count(*)=sum(flag)

[@Alexpudow]

select company from (select *, lead(revenue,1,revenue) over(partition by company order by year)-revenue diff from company_revenue) t group by company having count(case when diff >=0 then 1 end) = count(year)

[@SonuPatel-hr2bg]

with cte as (select company, year, revenue, case when (lead(revenue, 1) over(partition by company order by year)) > revenue or (lag(revenue, 1) over(partition by company order by year)) < revenue then 1 else 0 end as revenue_flag from company_revenue) select distinct company from cte a where not exists (select 1 from cte b where a.company = b.company and b.revenue_flag = 0)

[@kundankumarroy8352]

Understood the mentos life😀😄

[@nikhilreddy4582]

After studying and applying the concepts presented in your playlists, I attempted to solve this problem independently, without referring to the video. I am immensely grateful for the valuable content you provide. Ankit, please find attached my solution for your review." Select company from (select company,year,revenue, rank() over(partition by company order by revenue)rnk, row_number() over(partition by company order by year)row, rank() over(partition by company order by revenue) - row_number() over(partition by company order by year) calc from company_revenue)A group by company having max(calc) = 0 "

[@ankitbansal6]

That's good way of solving the problem 😊

[@mithunkt1648]

Hi Ankit, Thanks for the video. I have a similiar approach while using window function twice. Kindly review my code. #calculate revenue difference wrt last year for each company and form a cte With cte as ( Select Company, Year, Revenue-Lag(revenue,1,0)over(partition by company order by year asc) as rev_diff From Company_revenue ), #Calculate minimum revenue difference for each company for all years of operation and form another cte cte2 as ( Select Company, Year, Min(rev_diff)over(partition by company) as min_diff From cte ) # Filter company with minimum revenue difference >0 using distinct Select distinct company From cte2 Where min_diff>0 I solicit feedback for self improvement.

[@ankitbansal6]

This is also good.

[@zaravind4293]

Hi Ankit sir, Please look into my approach once with cte as( select company,revenue, lag(revenue,1,0) over(partition by company order by year) prev , CASE WHEN revenue-lag(revenue,1,0) over(partition by company order by year)

[@atifsuhail7803]

Mysql- my approach With cte as(select *, lag(revenue, 1,revenue) over(partition by company order by year) as rn from company_revenue) select distinct (company) from cte where company not in (select company from cte where revenue

[@taniyasaini6830]

Simple solution using min function: with cte as ( select *,(revenue - lag(revenue,1,revenue) over (partition by company order by year asc)) as diff from company_revenue ) select company from cte group by company having min(diff)>=0

[@rajindersingh-qm5bi]

Hi Ankit this way also possible with cte as( select company, revenue,year,lag(revenue) over(partition by company) as rev from company_revenue) select company,year,revenue-rev from cte group by year;

[@sureshk12135]

Hi Ankit, I have gone through the above video to find the company whose revenue is increasing every year. It covers increasing numbers, increase and decrease numbers but not covered same numbers for different years like some x company's revenue neither increased nor decreased for the next year. I have tried that as well with the help of your previous shared knowledge. If you got some chance, could you validate it please ? with cte as ( select *, rank() over (partition by company order by revenue ) rank_, ROW_NUMBER() over (partition by company order by year) order_, case when rank() over (partition by company order by revenue ) = ROW_NUMBER() over (partition by company order by year) then 1 else 0 end check_ from revenues ) select company,count(company) compamny_count,sum(check_) total_count from cte group by company having count(company) = sum(check_)

[@JohnvictorPaul-ec1sm]

with cte as(select *,lead(revenue,1,revenue) over(partition by company) as nex from company_revenue ), cte2 as(select company from cte where revenue>nex) select company from company_revenue where company not in(select company from cte2);

[@muhammadsuhailek4677]

Lets do a rank based on the order by asc of revenue and another rank by asc of year. Then sustract these two as "sub", it should give zero, then group by company having distinct count of "sub" =1

[@user-yp5tj2ce3s]

with cte as ( select *, case when lead(revenue,1, revenue+1) over(partition by company order by year)> revenue then 1 else 0 end as l from company_revenue ) select * from company_revenue where company not in(select company from cte where l =0 )

[@karangupta_DE]

-- increasing revenue for 3 consecutive years -- with cte as ( select *, lead(revenue)over(partition by company order by year) as next_revenue from company_revenue ), cte1 as ( select company, year, revenue, next_revenue, sum(case when next_revenue - revenue > 0 then 1 when next_revenue - revenue < 0 then -1 else 0 end)over(partition by company order by year) as running_revenue_flag from cte ) select company from cte1 where running_revenue_flag = 2 group by company;

[@sujanthapa2856]

select company from (select *, (case when revenue > lag(revenue) over(partition by company order by year) then revenue when revenue < lead(revenue) over (partition by company order by year) then revenue else 0 end) as rev from revenue) group by 1 HAVING COUNT(CASE WHEN rev = 0 THEN 1 ELSE NULL END) = 0;

[@deepakbharti8381]

my solution : with cte as ( select * , lag(revenue) over(partition by company order by year)as rev from company_revenue) select * from cte where company not in ( select company from cte where rev > revenue )

[@Artouple]

with cte as ( select company, coalesce(lead(revenue, 1) over(partition by company order by year, revenue) - revenue, 0) as rev_diff from company_revenue) select company from cte group by company having min(Rev_diff) >= 0;

[@ntruesingh8517]

Tq so much

[@ankitbansal6]

Welcome 😊

[@rahulmehla2014]

my approach ultra mentos life: with cte as ( select *, lead(revenue,1) over(partition by company order by year) as nxt_yr_rev from company_revenue) select distinct company from cte where company not in (select company from cte where revenue > nxt_yr_rev)

[@abhinavsingh1173]

Hi Ankit , please find the better solution with cte AS ( Select company from (Select *,LEAD(revenue) over(partition by company order by year asc) as next_revenue from company_revenue)x where next_revenue < revenue) Select DISTINCT company from company_revenue where company NOT IN (Select * from cte);

[@reachrishav]

Hi Ankit, I believe we can approach it this way too: WITH cte AS ( SELECT *, IIF(LAG(revenue) OVER(PARTITION BY company ORDER BY year) > revenue, 1, 0) flag FROM company_revenue ) SELECT company FROM cte GROUP BY company HAVING MAX(flag) = 0

[@FrazeAhmad]

mysql?

[@reachrishav]

@@FrazeAhmad Nope. MS SQL Server

[@jacobreddydevidi1305]

🤩 optimised code

[@anandahs6078]

with year_by_revenue as ( select company, year, revenue, revenue - lag(revenue, 1, 0) over( partition by company order by year) as revenue_diff from company_revenue ) select company from year_by_revenue group by company having sum(case when revenue_diff < 0 then 1 else 0 end) = 0

[@aishwaryapattnaik3082]

Anyone can tell how to find the company name for whose the sales are decreasing every year another scenario for the same problem ?

[@vanshhans5676]

I lived mentos life before normal life 😅 with cte as( select *,LAG(revenue,1,0) over(partition by company order by year) as prev_year_rev from company_revenue) select company from cte where company not in (select company from cte where revenue

[@yashnagpal8895]

with cte as ( select *,lag(revenue) over(partition by company order by year)lag_ from company ) ,my_cte as ( select company,year,(revenue-lag_)diff,min((revenue-lag_)) over(partition by company)a,row_number() over(partition by company order by year)r_n from cte ) select distinct company from my_cte where a > 0 and r_n >2

[@shanmugapriya9349]

👌👌👌

[@pavanreddy5724]

select company,sum(check_rev) from (select *, NVL(lead(revenue,1) over (partition by company order by year asc),revenue) lead_reven ,case when lead_reven>=revenue then 1 else 0 end check_rev from company_revenue) group by 1 having sum(check_rev) = count(company);

[@Tania-fk1fx]

My approach: ith cte as ( select *, lag(revenue) over(partition by company order by year) as last_year from company_revenue ), cte2 as ( select company, case when (last_year < revenue OR last_year IS NULL) THEN 1 ELSE 0 END AS status from cte ) select company from cte2 group by company having count(status)=sum(status);

[@shushantkumar8396]

with cte as( select *,lag(revenue) over(partition by company order by year) as prev_revenue from company_revenue ) select distinct company from cte where company not in(select company from cte where revenue

[@AbhishekSharma-uj7xi]

with cte as (select *, lead(revenue,1) over(partition by company order by year) as next_rev from company) select company from( select company, case when next_rev > revenue then 1 else 0 end as increase, case when next_rev 0 and sum(dec) = 0

[@nidishmani4169]

My solution : with cte as ( select company, year, revenue, case when revenue-lag(revenue,1,0) over(partition by company order by year)>0 then 1 else 0 end as revenue_diff , count(*) as company_cnt from company_revenue group by company, year, revenue ) , cte1 as ( select company, sum(revenue_diff) as cnt, sum(company_cnt) as total_comp_cnt from cte group by company ) select company from cte1 where cnt=total_comp_cnt

[@aryakaagnihotri3210]

i have done using rownumber and min window function with cte as ( select * , row_number() over (partition by company order by year ) row_num , min(revenue) over (partition by company ) min_revenue from company_revenue ), c2 as ( select company , year , (case when revenue > min_revenue then 1 else 0 end ) inc_revenue from cte where row_num >1 ) select company from c2 group by company having count(company) = sum(inc_revenue); or we can use this select query select company from c2 where company not in (select company from c2 where inc_revenue = 0 ) group by company;

[@Hustler19]

In Mysql the simplest approach is SELECT company FROM company_revenue cr1 WHERE NOT EXISTS ( SELECT 1 FROM company_revenue cr2 WHERE cr2.company = cr1.company AND cr2.year = cr1.year + 1 AND cr2.revenue

[@reshmaammu4726]

with cte AS (select company, year, revenue, lead(revenue) over(partition by company order by year) as leads from company_revenue), cte3 as (select distinct company,case when leads>revenue then True else false end as boole from cte where leads is not null) select company from cte3 group by company having count(boole)=1

[@ajazahmed9676]

with cte1 as ( select *, row_number() over (partition by company order by year) as rw, dense_rank() over (partition by company order by revenue) as dr from company_revenue order by company), cte2 as (select company, case when rw=dr then company end as "result" from cte1 group by company) select result as company from cte2 where result is not null

[@vinodbhul8899]

with revenue as (select company,revenue,lag(revenue,1,0) over(partition by company order by year) as prev_year_rev from company_revenue), increase_trend as (select *,case when revenue>=prev_year_rev then 1 else 0 end as flag from revenue) select company from increase_trend group by company having count(*)=sum(flag);

[@dnyaneshsatpute7734]

with cte as ( select *,row_number()over(partition by company order by year) as rn_year, row_number()over(partition by company order by revenue) as rn_revenue from company_revenue ) select company from cte group by company having sum(case when rn_year=rn_revenue then 1 else 0 end)=3

[@CEMANIVANNAN]

Hi bro,thanks for good content , that why I'm follow your videos. please below solution is correct or not? with ct as(select *,lag(revenue,1,0) over(partition by company order by year) lg from company_revenue) select company from ct where revenue-lg>0 group by company having count(*)>=3; -- with cte as(select *,lag(revenue,1,0) over(partition by company order by year)lg from company_revenue) select distinct company from (select company,year,sum(case when revenue-lg>0 then 1 else 0 end) over(partition by company order by year) sump from cte) where sump>=3;

[@LakshaySharma-y8t]

this is my solution to the above problem: with cte as ( select * , COALESCE(lag(revenue,1) over (partition by company order by year),0) as prev_revenue from company_revenue order by company,year ), cte2 as ( select *, CASE when revenue > prev_revenue then 1 else 0 end as flag from cte ) select company from cte2 group by company having sum(flag) = count(year)

[@ManpreetSingh-tv3rw]

with echo as (select *,coalesce(lag(revenue,1,0) over (partition by company order by year),revenue) as nextyear_revenue from company_revenue),master1 as ( select *,revenue - nextyear_revenue as growth from echo) select distinct company from master1 where company not in (select company from master1 where growth

[@sambireddy8844]

Select distinct employee from company_revenue where employee not in (Select district employee from (Select employee, prev_revenue - revenue as rev_diff from (Select *, lag(revenue, 1,0) over ( partition by company order by year ) as prev_revenue from company_revenue as T)) where rev_diff

[@madhubanti123]

Select company from ( Select company,year,revenue, LEAD(revenue,1) over (partition by company order by year) as next, (LEAD(revenue,1) over (partition by company order by year) - revenue) as dif from company_revenue )A group by company having min(dif) >1

[@pinaakgoel2937]

select company from (select *,revenue-lag(revenue,1,0) over(partition by company order by year) as diff from company_revenue) group by company having min(diff)>0;

[@soumyananda5391]

Hi sir, My approach using join with cte as(select company_name, max(rev) as maximum, min(rev) as minimum from company group by company_name) , cte1 as(select *,row_number() over(partition by company_name order by yr) as rn from company) , cte2 as(select *,row_number() over(partition by company_name order by yr desc) as rn from company) select C.company_name from cte c join cte1 c1 on c.company_name=c1.company_name join cte2 c2 on c.company_name=c2.company_name where c1.rn=1 and c.maximum=c2.rev and c.minimum=c1.rev;

[@aaravkumarsingh4018]

#My Approach with cte as( select company,year,revenue,rank() over(partition by company order by year) as year_rn, rank() over(partition by company order by revenue) as rev_rn from company_Revenue ) select distinct company from company_revenue where company not in(select company from cte where year_rnrev_rn);

[@rahulmishra8535]

with cte as ( select *, ld - revenue as diff from ( select *, lead(revenue) over(partition by company order by year asc) as ld from company_revenue )a) select distinct company from ( select *,sum(case when diff

[@bhushanchuadhari5479]

Video quality is low..but content is best

[@AjaySingh-kj6mm]

Sir can you please make a video on how you solve tickets...with real life ticket problem example and all steps during ticket solution

[@ankitbansal6]

What kind of tickets?

[@user-xg6yi7hu1t]

Hi Ankit, here is my approach for above problem with cte as( select *,lead(revenue,1,revenue) over(partition by company order by company,year) nxt_yr_revenue from company_revenue order by company) ,cte2 as( select *,revenue-nxt_yr_revenue,sum(case when revenue-nxt_yr_revenue>0 then 0 else 1 end) over(partition by company order by company,year) flag,count(company) over(partition by company order by company,year) cnt from cte ) select company from cte2 group by company having max(flag)=max(cnt); let me know your thoughts on this?

[@Ankitatewary-q6w]

select distinct company from company_revenue where company not in ( select distinct company from( select *,(revenue-coalesce(lag(revenue) over(partition by company),0)) yearly_diff from company_revenue)temp where profit

[@ranitghatak1914]

Can we solve this question using rank.We will create two temp tables.first one we will rank on basis of year another on basis of salary.and then we will join the two temp tables on year.If the rank matches for every row then that is the answer

[@iamkiri_]

My Solution with cte as (select company, year, revenue, lead(revenue,1,revenue) over(partition by company order by year) as ld from company_revenue) select company from ( select company , case when (ld - revenue) >= 0 then 1 else 0 end as flg, count(1) over (partition by company) as cnt from cte) Q group by company having sum(flg) = count(cnt);

[@vaibhavverma1340]

Here is my SQL using lead functions :- with cte as (select company, year,(lead(revenue,1, revenue) over (partition by company order by year)- revenue)diff_revenue from company_revenue) select distinct company from cte where company not in (select company from cte where diff_revenue < 0)

[@2412_Sujoy_Das]

Ankit Sir ... used almost the same approach in the "normal life" method except that I took three CTE (total) as I didn't know about the third parameter in the lag function...... 😢

[@ankitbansal6]

Good job 👍

[@vijaygupta7059]

can anyone please explain the meaning of "count(1)" on SQL query and in 2nd solution how we group by without aggregation function, when i try in mysql but its not working

[@ankitbansal6]

kzbin.info/www/bejne/f3abf6ibntirfrs

[@prabhatgupta6415]

with cte as (select *,case when isnull(lead(revenue)over(partition by company order by year),999)>revenue then 1 else 0 end as h from company_revenue) select distinct company from company_revenue where company not in ( select company from cte where h=0) sir plz verify this one.

[@KoushikT]

################### My Approach ############################ with A as ( select *, case when year > lag(year,1,0) over (partition by company order by year) and revenue > lag(revenue,1,0) over (partition by company order by year) then 1 else 0 end as flag from company_revenue) select company from A group by company having count(company) = sum(flag)

[@praneethmuragani]

select company from ( select *, lag(revenue, 1, 0) over(partition by company order by year asc) as last_year_revenue, count(revenue) over(partition by company) as total_n_of_revenue From company_revenue ) t where revenue > last_year_revenue group by company, total_n_of_revenue having count(1) = total_n_of_revenue

[@stat_life]

MY SOLUTION with cte as( select * , lag(revenue,1,0) over(partition by company order by year) as prev_yr from company_revenue ) select company, count(case when (revenue - prev_yr) > 0 then 1 else null end) as inc_rev from cte group by company

[@palbabu33]

posting my solution before watching: select company from ( select * ,revenue - (lag(revenue) over(partition by company order by year)) as rev_diff from company_revenue ) a group by company having min(rev_diff)>0

[@radhikagupta7314]

with sample as ( select distinct c1.cid from company c1 inner join company c2 on c1.cid=c2.cid and c1.year=c2.year+1 where c1.revenue

[@atharvabhangre9044]

My Solution Before watcing your solution with cte as(select company,count(*) listings,count(case when revenue>last_rev or last_rev is null then 1 end) yoy_growth_year from (select *,lag(revenue,1) over(PARTITION by company order by year asc) last_rev from company_revenue) a group by company) select Company from cte where listings=yoy_growth_year