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)

[@Reacher1998]

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)

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

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

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

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

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

[@avi8016]

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

[@ankitbansal6]

Glad you liked it!

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

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

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

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

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

[@reshmashaik6606]

with cte as(
select *,
lag(revenue,1,0) over(partition by company order by year) as lags
from company_revenue),
cte2 as(
select distinct company,
case when revenue>lags then 'Y' else 'N' end as flag
from cte
where lags0)
select company from cte2
group by company
HAVING COUNT(company)=1

[@arghyadhar91]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@addhyasumitra90]

select distinct company
from company_revenue
where company NOT IN (
select a.company
from company_revenue as a JOIN company_revenue as b
ON a.company= b.company and a.year=b.year-1
where a.revenue>b.revenue)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@ChandraPrakashDhami]

with cte as(
select *, ROW_NUMBER() over(PARTITION by company order by revenue ) flag_amount
, ROW_NUMBER () over(PARTITION by company order by [year] ) flag_year
from company_revenue cr
)
select DISTINCT company from company_revenue cr where company not in (
select DISTINCT company from cte
where flag_amount flag_year)

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

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

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

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

[@komalbansal4141]

WITH cte AS(
SELECT *,
revenue-LAG(revenue,1) OVER(PARTITION BY company ORDER BY year,revenue) AS var
FROM company_revenue
)
SELECT company FROM (
SELECT *,
SUM(CASE WHEN var

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

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

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

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

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

[@ReneA-nn7fh]

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

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

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

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

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

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

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

[@monasanthosh9208]

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

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

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

[@himanshugupta239]

The way of explanation is super 😊

[@ankitbansal6]

Glad you liked it😊

[@deautomate]

Alternative Approach:
Here we find the minimum of revenue_diff and show only those companies whose min(revenue_diff) > 0
with cte as (
select
company,
year,
revenue - lag(revenue) over(partition by company order by year) as revenue_diff
from company_revenue
)
select company from cte
where revenue_diff is not null
group by company
having min(revenue_diff) > 0

[@KasmitharamKr]

My Solution:
WITH TEMP1 AS (
SELECT *,
lag(revenue) over(partition By company order by year) as lag_revenue FROM company_revenue),
TEMP2 AS (SELECT *,
(revenue - lag_revenue) AS difference FROM TEMP1),
TEMP3 AS (SELECT *,
CASE WHEN difference > 0 THEN 0
WHEN difference IS NULL THEN 0
ELSE 1
END AS checking
FROM TEMP2)
SELECT company, sum(checking)
FROM TEMP3
GROUP BY 1
HAVING sum(checking) = 0;

[@nachiketpalsodkar4356]

Another Simpler Way!!!!
;;
WITH cte as (
SELECT *
, revenue - LAG(revenue)OVER(PARTITION BY company ORDER BY year) as DIFF
FROM company_revenue )
, cte1 as (
SELECT *
, COUNT(CASE WHEN DIFF < 0 THEN 1 ELSE NULL END ) OVER(PARTITION BY company ) as CNT
FROM cte )
SELECT DISTINCT company
FROM cte1
WHERE CNT = 0

[@ankitbansal6]

Looks Good 👍

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

[@ranadeepghosh1891]

Excellent as usual❤

[@ankitbansal6]

Thank you! Cheers!

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

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

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

[@nehapr2293]

My solution is here:
WITH CTE AS(
SELECT *,LAG(REVENUE,1,0) OVER(PARTITION BY COMPANY ORDER BY YEAR) AS prev_REV
FROM COMPANY_REVENUE)
SELECT
COMPANY
FROM CTE
WHERE COMPANY NOT IN (SELECT COMPANY FROM CTE WHERE REVENUE

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

[@RiyazKhan-zz8fq]

Here's is my solution
with cte as (select *, lead(revenue) over (partition by company) as nxtyr from company_revenue),
cte2 as (select * , (case when (revenue > nxtyr) then '0' else '1'end) as c from cte)
select company from cte2
group by company
having count(1)= sum(case when c=1 then 1 else 0 END)