PWC SQL Interview Question | BIG 4 |Normal vs Mentos Life 😎
HTML-код
- Опубликовано: 19 окт 2024
- 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)
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)
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;
Great demonstration of logic in Mentos life method 💯
Thankyou as always to bring such good quality questions!!!
Glad you liked it!
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 👍
Thanks, looks very simple and clean.
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
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
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)
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)
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
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
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);
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
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
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;
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);
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
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');
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
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
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
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
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)
);
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)
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
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 *,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 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)
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;
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)
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)
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 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;
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)
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)
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
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
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.
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 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
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;
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 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
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 *, 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
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
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);
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
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
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
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)
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);
-- 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;
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
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
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;
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);
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;
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 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 )
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);
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
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
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'
Back after long time
Good one sir
The way of explanation is super 😊
Glad you liked it😊
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
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 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
)
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
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 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;
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
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_)
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
Excellent as usual❤
Thank you! Cheers!
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)
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
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 *,
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;
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)
Hi @ankitbansal6, could you please explain why we used such a complex solution when only a simple comparison with lag/lead function would have been sufficient because data can be sorted in increasing order of years and we just need to check if there is any record where year is greater but revenue is lesser, it would not be considered.
Here is y solution:
with cte as (select *, lead(revenue,1,revenue) over(partition by company order by year asc)as l_revenue from company_revenue)
select distinct company from cte where company not in (select distinct company from cte where l_revenue < revenue);
Just awesome..
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
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
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
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?
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
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?
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.
Thanks for sharing Sir
#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);
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 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
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
Well Explained !!
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
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)
For what role was the interview for ?