Practice SQL Interview Query | Big 4 Interview Question

Amazing thank you

with cte as(select year, brand, amount as now,
coalesce(lead(amount) over(partition by brand order by brand),0) as previous
from brands)
select year, brand,
case when previous > now then 'increased' else 'not' end as 'status'
from cte;

Excellent

Thanks a lot for sharing the solution

my solution

WITH cte1 AS
(SELECT *,
CASE
WHEN LAG(amount) OVER (PARTITION BY Brand
ORDER BY year) IS NULL THEN 0
ELSE (amount / LAG(amount) OVER (PARTITION BY brand
ORDER BY year)) - 1
END AS growth
FROM brands)
select *
from brands
where Brand in
(SELECT brand
FROM cte1
GROUP BY brand
having min(growth) = 0)

Learnt something new

Alternate solution using where not exists:
select * from dataset
with cte as (
select *, (amount-ifnull(lag(amount) over(partition by brand order by year),amount)) as yearly_growth
from dataset)
select distinct brand from cte as c1 where not exists (select 1 from cte as c2
where c1.brand=c2.brand and c2.yearly_growth<0);

Taufiq the way you explain is amazing and never seen such a teacher

so that we can try in some other way and post in comments is possible

Please paste the table created and inserted script also so that we can try individually

this is real nice video !!!! thank you for sharing this stuff

Thanks! My solution:

WITH cte AS (
SELECT *
, CASE
WHEN Sales > COALESCE(LAG(Sales) OVER(PARTITION BY Brand ORDER BY Year),0)
THEN 1 ELSE 0 END AS is_increased
FROM brands
)

SELECT Brand
FROM cte
GROUP BY 1
HAVING SUM(is_increased) = COUNT(DISTINCT YEAR)

select Brand from (
select * ,(t1.Amount-t1.s) as g from(
select * ,lead(Amount) over(partition by Brand order by Brand,Year)s from brands)t1)t2
group by Brand having min(g)>0

####### MYSQL 8.0 SOLN ########

with cte as
(
select*,
lag(amount,1,0) over(partition by brand order by year) as chng
,case when amount - lag(amount,1,0) over(partition by brand order by year) >=0 then 1 else null end as flags
from sdata
)

select year,brand,amount from cte
where brand in (
select brand from cte
group by brand
having SUM(flag)=3
)

Thanks bro this question asked in yestarday interview i am unable to write query now i learned how to write thanks

Liked your approach and way of explaining.!!!
This was my approach which was easy to understand for me -

select Brand from
(select * ,
Amount - lag(Amount) over(partition by Brand order by Year) as diff
from brands) t1
group by Brand
having min(diff) > 0;

select * from brd where brand not in (select distinct brand from (
select years,brand,amount,amount-lag(amount,1,0) over (partition by brand order by years,amount) as check_sum from brd ) a
where a.check_sum < 0)

awful slow

select *
, (case when amount < lead(amount, 1, amount+1)
over(partition by brand order by year)
then 1
else 0
end) as flag
from brands

This query does not run in mysql. Throwing syntax error. Please suggest a solution.

If we have first to record ordered properly but last record else comparitively low then what do do

Explained very well 👍

Great! You made sql fun to learn.

Thank you sir

WITH CTE AS (
SELECT Year , Brand ,Amount ,CASE
WHEN Amount < lead(Amount,1,amount+1) OVER(partition by Brand order by Year)
AND Amount < lead(Amount,2) OVER(partition by Brand order by Year)
THEN 1 ELSE 0
END AS Flag
FROM brands)

SELECT * FROM brands WHERE Brand IN (SELECT Brand FROM CTE WHERE Flag =1)

Hi TFQ, could you please create videos on Python Interview Questions for Data Analysts?

too much advertisement

You are the best!!! Thank you.

declare @brands table ([year] int, brand varchar(20), amount int)
insert into @brands ([year], brand, amount) values
(2018, 'Apple', 45000),(2019, 'Apple', 35000),(2020, 'Apple', 75000),
(2018, 'Samsung', 15000),(2019, 'Samsung', 20000),(2020, 'Samsung', 25000),
(2018, 'Nokia', 21000),(2019, 'Nokia', 17000),(2020, 'Nokia', 14000);

with cteBrands
as (
select *
, case when (amount - lag(amount,1,0) over (partition by brand order by [year]) > 0) then 1 else 0 end as [valueIncreased]
, count(*) over (partition by brand) as record
from @brands
)

select brand
from cteBrands
group by brand
having sum(valueIncreased) = min(record);

You approach is great. But this answerhas one flaw. That is if one company is in the list and for only one year then the flag will return 1. As far as i can think it can be resolved by one more cte and counting the number of flags when flag=1. And with where clause when the count is 3.

Q1. --write a query to fetch the record of brand whose amount is increasing every year

with cte as
(
select *,lag(amount) over(partition by brand order by year) as prev from Brands
)
select Brand from cte
GROUP BY Brand
having SUM(case when (Amount-prev)<0 then 1 else 0 end) = 0

SELECT brand
FROM sales_data
GROUP BY brand
HAVING COUNT(*) > 1 AND MIN(amount) < MAX(amount) AND MAX(year) = 2023;

Here is my approach

;with cte as
(select Years,Brand,amount,
ROW_NUMBER() over(partition by brand order by years) as rnk,
DENSE_RANK() over(partition by brand order by amount) as drnk
from Brands),
cte2 As
(select brand from cte where rnk<=drnk
group by brand
having count(rnk)=max(drnk))
select * from brands where brand in(select brand from cte2)

Where can we find a job and how can we get it

Thank you very much Sir, for this question and great explanation.

Thanks for this very helpful video. I want to ask something about the last records of each brand. Let's take the last value of amount for Samsung is 19000. In this case the assumption of 1 for the last record for flag will not be correct. Is it true or is there anything that I misunderstood?

Great

My solution
DECLARE @sample TABLE (year int, brand varchar(255), amount int);
INSERT INTO @sample
VALUES
(2018, 'Apple', 45000),
(2019, 'Apple', 35000),
(2020, 'Apple', 75000),
(2018, 'Samsung', 15000),
(2019, 'Samsung', 20000),
(2020, 'Samsung', 25000),
(2018, 'Nokia', 21000),
(2019, 'Nokia', 17000),
(2020, 'Nokia', 14000)

select brand from
(select *,
LEAD(amount ) over(partition by brand order by year) as next_year_sales_Amount
from @sample)x
where next_year_sales_Amount>amount
group by brand
having count(*)= (select count(distinct year)-1 from @sample)

Such a helpful video

Hi TFQ
Can you help me with below sql question
1 .difference b/w count(*) and count (1) and count(column)?

with cte as(
SELECT *,lead(amount) over(partition by brand) as new_amount FROM practice.phones),
-cte1 as(
select *, case when amount < new_amount or new_amount is null then 1 else 0 end as flag from cte),
max_brand as
(select brand,sum(flag) as flag1 from cte1
group by 1),
main_brand as(
select brand from max_brand
where flag1 = (select max(flag1) from max_brand))

select cte1.year,cte1.brand,cte1.amount from cte1 left join main_brand mb on cte1.brand=mb.brand
where mb.brand is not null

Hey techtfq, Can I just use row number function like partition by brand order by year ascending and in where clause I put rn3>rn2 and rn2>rn1.

Sir why can't we just add 1 instead of "amount+1" in the last argument in lead function?

Thanks TF for the generous sharing! Love how you explain it explicitly. Will continue to follow for more

Hi @techTFQ , Is this correct , I have not used the amount + 1 condition

;with cte as (
select
case when Amount - lag([Amount],1,Amount) over (partition by Brand order by Year) >= 0 then 'Increased' else 'Decreased'
end as Sales
, * from brands
)
select * from brands where brand not in ( select brand from cte where Sales = 'Decreased' )

with base as (select
case
when nth_value(Amount, 2) over(partition by Brand rows between current row and 1 following)>Amount then 1
when nth_value(Amount, 2) over(partition by Brand rows between current row and 1 following) is null then 1
else 0
end as _lead,
Brand,
Amount from brands)

select Brand from base group by Brand having sum(_lead) = count(*);

It seems so easy when you explain the solution to the given problem.

U not only solve problems u always give us idea about how to approach particular questions

cool buddy

Thank you for Lead function information