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

[@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)

[@Arjunvikramcorner]

Great Solution!
Here's is my solution
Select company from
(select *, case when revenue > lag(revenue,1,0) over(partition by company order by year) then 1 else 0 end as flag
from company_revenue) A
group by company
having min(flag)=1;

[@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)

[@webdeveloper-q1i]

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!

[@abc_987]

with cte as (Select *,
lag(revenue,1,0) over(partition by company order by year) as prev_salary
from company_revenue),
cte2 as(select *, case when revenue> prev_salary then 'true' else 'false' end as status
from cte )
select company
from cte2
group by company
having count(distinct(status))=1
this also helps

[@shwetasaini6892]

Took a very complicated approach. Your solution looks better
-- select * from company_revenue
with cte as(
select
*,
LAG(revenue) over(partition by company order by year) as previous_year,
revenue-LAG(revenue) over(partition by company order by year) as change_in_revenue
from company_revenue),
cte2 as(
select
*,
case when change_in_revenue > 0 then 1 else 0 end as flag
from cte
where previous_year is not null)
select
distinct company
from cte2
group by company
having min(flag) > 0

[@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 👍

[@shakthimaan007]

Thanks, looks very simple and clean.

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

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

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

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

[@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);

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

[@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)

[@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)

[@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)

[@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)

[@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)

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

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

[@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)

[@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;

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

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

[@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;

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

[@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)

[@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);

[@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)

[@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)

[@parmoddhiman678]

mentos
with cte as (SELECT * ,
revenue - lag(revenue,1,0) over(partition by company order by year) as rev_diff
FROM company_revenue)
select *
from cte
where company not in (select company from cte where rev_diff

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

[@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');

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

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

[@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)
);

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

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

[@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;

[@Damon-007]

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

[@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);

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

[@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;

[@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);

[@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);

[@mathavansg9227]

with cte as (
SELECT *,lead(revenue,1,revenue) over(partition by company order by year asc) as next_year
from company_revenue)
select * from cte group by company
having (case when next_year>=revenue then 'yes' else 'no' end)='yes'

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

[@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;

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

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

[@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 )

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

[@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);

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

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

[@mirzataimoor9632]

WITH prev_year_revenue as (
select *,
LAG(revenue) OVER (PARTITION BY company order by year) as prev_year_rev
from company_revenue
)
select distinct(company) from company_revenue where company not in (
select distinct(company) from prev_year_revenue
where prev_year_rev > 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

[@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;

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

[@parmoddhiman678]

aam zindgi
with cte as (SELECT * ,
lag(revenue,1,0) over(partition by company order by year) as pre_re,
revenue - lag(revenue,1,0) over(partition by company order by year) as rev_diff,
count(*) over(partition by company) as cnt
FROM company_revenue)
select company,cnt,count(1) as sales_in_yr from cte
where rev_diff>0
group by 1,cnt
having cnt=count(1)

[@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)

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

[@mridulmaurya8383]

using joins
with abc as (select c1.company as comp1,c1.year as year1,c1.revenue as rev1,
c2.company as comp2, c2.year as year2, c2.revenue as rev2
from company_revenue c1 inner join company_revenue c2
on c1.company=c2.company and c1.year+1=c2.year
)
select distinct(comp1) as progressive_company
from abc where comp1 not in (select comp1 from abc where rev2

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

[@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)

[@sudarshankumar3851]

with cte1 as (select *,
lag(revenue,1,revenue) over (partition by company order by year asc) as new_revenue,
revenue-lag(revenue,1,revenue) over (partition by company order by year asc) as diff
from company_revenue)
select company from cte1
group by company
having min(diff)!< 0

[@anamikajaiswal9423]

with cte as (
select *,
case when (revenue - lag(revenue) over w) > 0 or (lag(revenue) over w IS NULL) then 'Y'
else 'N' end check_com
from company_revenue
window w as (partition by company order by year))
select company from cte
group by company
having count(case when check_com != 'Y' then 1 end) = 0;

[@sahilmahale7657]

with cte as(select *
,lag(revenue,1,0) over(partition by company order by yearss) as previous_yearRevenue
,count(1) over(partition by company ) as company_total
from company_revenue
)
select company
from cte
group by company,company_total
having company_total=sum(case when (revenue>previous_yearRevenue) Then 1 else 0 end)

[@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.

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

[@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;

[@ShivamGupta-wn9mo]

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

[@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;

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

[@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);

[@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;

[@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.

[@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)

[@rahulvaidya5262]

with cte as
(
SELECT
*,
lead(revenue) OVER(PARTITION BY YEAR ORDER BY revenue) as inc,
lag(revenue) OVER(PARTITION BY YEAR ORDER BY revenue) as dec
from company_revenue
)
select company from cte
where inc > dec
GROUP BY company

[@rk-ej9ep]

Just awesome..

[@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);

[@Demomail-m6w]

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?

[@narendersingh2851]

my solution :
select company from (
select *, row_number() over(partition by company order by year asc) as yr_rnk,row_number() over(partition by company order by revenue asc) as rvn_rnk
from company_revenue
) as tb
group by company having sum(case when yr_rnk=rvn_rnk then 0 else 1 end)=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

[@ranadeepghosh1891]

Excellent as usual❤

[@ankitbansal6]

Thank you! Cheers!

[@tahakhalid324]

Hi Ankit, here is my solution: with revenue_per_year as (
SELECT
company,
year,
revenue,
lead(revenue,1,revenue+1)over(partition by company order by year) as revenue_nxt_yr
from
company_revenue
) , grwoth_per_year as (
SELECT
company,
year,
revenue,
revenue_nxt_yr,
(revenue_nxt_yr - revenue) growth,
case
when (revenue_nxt_yr - revenue) >=1 then 1 else 0 end as flag
from revenue_per_year
)
SELECT
DISTINCT(company)
from
grwoth_per_year
WHERE
company not in (SELECT company from grwoth_per_year where flag

[@himanshugupta239]

The way of explanation is super 😊

[@ankitbansal6]

Glad you liked it😊

[@kunalgaurav3301]

Thanks for sharing Sir

[@rohanjamea2446]

For what role was the interview for ?

[@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_)

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

[@guptaaniket4155]

with cte as (
SELECT *,LAG(revenue) OVER (PARTITION BY company ORDER BY year) as previous_value FROM Company_revenue
),
cte2 as (
SELECT *,
CASE WHEN previous_value IS NULL THEN 1
ELSE revenue > previous_value
END as diff
from cte
)
SELECT company from cte2 group by company having min(diff) = 1;

[@monasanthosh9208]

MYSQL Solution
With CTE as
(Select *,Revenue-Next_Year as Increment,
Case When (Revenue-Next_Year)

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