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:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[ankitbansal]

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.


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

[sobhiksaha]

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) <= 0 then 1 else 0 end
as flag from company_revenue) a
group by 1
having count(distinct year) = sum(flag);

[avi]

Great demonstration of logic in Mentos life method 💯

Thankyou as always to bring such good quality questions!!!

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

[webdeveloper-qi]

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)

[apna]

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

[medleyworld]

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

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

[ankitb]

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) <=1

[ArijitDatta-ptwi]

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

[Vaibha]

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

[dibakarmandal]

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;

[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

[abc_]

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

[shwetasaini]

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

[sahilummat]

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

[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

[surajn]

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<prev_revenue then 1
else 0
end as flag
from rev)aa group by company having sum(flag)=0

[Damon-]

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<rn);