1) EY Data Engineer Interview Question | SQL Interview Question | Data Analyst Interview Question |

Hi,
This question was shared to me by one of my linked in followers. Please do watch it completely and let me know your thoughts.
Please find the create table and insert statement scripts below:
create table category(
Category varchar(50),
SubCategory varchar(50),
Price varchar(50) );

insert into category values('Chips', 'Bingo', 10),
('Chips', 'Lays', 40),
('Chips', 'Kurkure', 60),
('Choclate', 'Dairy Milk', 120),
('Choclate', 'Five Star', 40),
('Choclate', 'Perk', 25),
('Choclate', 'Munch', 5),
('Biscuits', 'Oreo', 120)

#dataengineer #sql #sqlinterview #EY #datanalytics #scenario #datascience #big4 #deloite #pwc

[suriyas_zone]

Hi All,
This video is for beginner who is planning to get a job in the data field. If you're a person who is having some good experience and planning to revise the topics then please increase speed of the video. Thanks

[Kirankumar-mlro]

Please change the data type of the price column to numeric in the create table script in the description. Data was not getting as expected while (order by price desc) for category choclate in PostgreSQL.

[anujshinde]

with cte as (select *, row_number() over(partition by category order by price desc) as rank, count(subcategory) over(partition by category) as count from category)
select Category, SubCategory from cte where rank <= 2
union
select Category, SubCategory from cte where price >= 50 and count = 1;

[rawat]

with cte as(
select Category, SubCategory, cast(Price as signed) as price_int from Category), cte2 as (
select Category, SubCategory, price_int,
rank() over(partition by Category order by price_int desc) as rnk,
count(Category) over(partition by Category) as total_cnt
from cte)
select * from cte2
where rnk <= (
case
when total_cnt >1 then 2
when total_cnt = 1 and price_int >= 50 then 1
end )

[prabhatgupta]

with cte as (select * from (select *, dense_rank()over(partition by category order by price) dk from category)x where dk<=2)
, b as (select *, count(subcategory)over(partition by category) ck from cte)
select * from (select *, case when ck=2 then 'yes' when ck=1 and price>50 then 'yes' else 'no' end as flag from b )m where flag='yes';

[ogunniransiji]

WHERE (rn <= 2 AND cnt = 2) OR (cnt = 1 AND price >= 50); I prefer this

[edumail]

This is so easy question, don't know how it was asked for Data Engineer role

[maheshnagisetty]

select category, subcategory from
(
select *, rank() over(partition by category order by cast(price as money) desc) as rn from category
) as a
where rn<=2
or price>50

[shovilgupta]

Change the data type of price to numeric

[keerthianand]

Sir window functions full explanation video podunga

[piyushramkar]

correct me if i am wrong.
--query:
with cte1 as (
select category, subcategory, price,
rank() over(partition by category order by price) rnk
from category
)
select category, subcategory
from cte1
where rnk in (1, 2)

--output:
CATEGORY SUBCATEGORY
Biscuits Oreo
Chips Bingo
Chips Lays
Choclate Dairy Milk
Choclate Perk

[mohammedvahid]

Pleas share more with SQL scenarios pls

[vaibhavverma]

My Solution :)

with cte as
(select *, dense_rank() over (partition by category order by price desc)dr
, count(*) over (partition by category)cnt_category
from category)

select category, subcategory from cte
where cnt_category = 1 and price > 50
union
select category, subcategory from cte where cnt_category > 1 and dr<=2

[zaravind]

Find my approach below.
select category, subcategory from (
select c.*,
case when count(*) over(partition by category)=1 and price<=50 then 0 else
dense_rank()over(partition by category order by price desc) end rk from
category c
)
where rk<=2;

[wgbhrvi]

Hai can we get job in Data Engineer role as a fresher

[roobanj]

Hi bro.,
Very informative and impressive content
Love to connect with you


May I know your LinkedIn Id