PWC SQL Interview Question | BIG 4 |Normal vs Mentos Life 😎
HTML-код
- Опубликовано: 12 сен 2023
- 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
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
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
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)
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)
Great demonstration of logic in Mentos life method 💯
Thankyou as always to bring such good quality questions!!!
Glad you liked it!
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
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)
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)
Just awesome..
Well Explained !!
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)
Back after long time
Good one sir
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
Very nice 👍
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);
Thanks for sharing Sir
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
Excellent as usual❤
Thank you! Cheers!
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');
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)
);
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
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;
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
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);
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
The way of explanation is super 😊
Glad you liked it😊
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);
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)
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
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)
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)
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
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;
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
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
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);
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)
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)
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
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
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;
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
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 )
-- 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;
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
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)
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)
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 )
Understood the mentos life😀😄
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
👌👌👌
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
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)
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;
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
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
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
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
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
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)
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
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);
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);
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
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)
#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);
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;
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
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
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)
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;
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)
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
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
################### 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)
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
Tq so much
Welcome 😊
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);
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
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
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 "
That's good way of solving the problem 😊
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);
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
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
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.
This is also good.
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_)
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;
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
mysql?
@@FrazeAhmad Nope. MS SQL Server
🤩 optimised code
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
Anyone can tell how to find the company name for whose the sales are decreasing every year another scenario for the same problem ?
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
Video quality is low..but content is best
MYSQL Solution
With CTE as
(Select *,Revenue-Next_Year as Increment,
Case When (Revenue-Next_Year)
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;
For what role was the interview for ?
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)
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...... 😢
Good job 👍
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
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;
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
Sir can you please make a video on how you solve tickets...with real life ticket problem example and all steps during ticket solution
What kind of tickets?
We can filter if min(diff) > 0 or not . Here is my solution
with cte as(
select
*,
lead(revenue) over(partition by company order by year) as next_year_rev,
coalesce(lead(revenue) over(partition by company order by year),100000) - revenue as diff
from
Company_revenue
)
select company
from cte
group by company
having min(diff) >= 0
You can use this condition in the second solution:
Having min(revenue_diff)>=0