1) EY Data Engineer Interview Question | SQL Interview Question | Data Analyst Interview Question |
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- 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)
Please follow me on LinkedIn and reach out for any queries: / suriya-senthilkumar-3a...
Send an email to: suriya.senthil@outlook.com
#dataengineer #sql #sqlinterview #EY #datanalytics #scenario #datascience #big4 #deloite #pwc
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
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.
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 = 50 and count = 1;
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 1 then 2
when total_cnt = 1 and price_int >= 50 then 1
end )
Great
with cte as (select * from (select *,dense_rank()over(partition by category order by price) dk from category)x where dk50 then 'yes' else 'no' end as flag from b )m where flag='yes';
Great
Great effort. Keep it up
Thank you Nishant ☺️ Please share
Amazing sir
Thanks for sharing ❤
Thank you. Please do share and subscribe 🎉
Informative ❤ and subscribed
@@amarpc3532 thank you ❤️
Sir.. really useful 🎉
Thank you
WHERE (rn = 50); I prefer this
@@ogunniransiji2686 great
@@suriyas_zoneHii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
select category,subcategory from
(
select *,rank() over(partition by category order by cast(price as money) desc) as rn from category
) as a
where rn50
It will give the category which has single subcategories and also if it's price is less than 50.
Good try...keep practicing!
Hii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
@@macherlarishika8748 Hi, please send the question to suriyauxd@gmail.com
This is so easy question, don't know how it was asked for Data Engineer role
@@edumail1016 Is it ? May I know how much work experience do you have in the data engineering field ?
Data engineers with 1-3 years experience got this type of questions.
I have 1 month of experience
Hii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
Change the data type of price to numeric
Informative
Thanks ! Please do share with your friends. Videos will be uploaded weekly once !
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
Hi Piyush,
Your query won't give expected results. It will give all the subcategories based on the rank(1,2) which you've generated based on category and price. What will happen if the price is less than 50 ?
@@suriyas_zoneHii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
Sir window functions full explanation video podunga
Sure
@@suriyas_zoneHii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
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
Great
@@suriyas_zoneHii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
Nice bro
Thank you bro
@@suriyas_zoneHii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
WITH cte AS (
SELECT *,
RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rn
FROM sales)
SELECT category, subcategory FROM cte WHERE rn=1 OR rn=2;
Pleas share more with SQL scenarios pls
Sure. Weekly once video will be uploaded
Hii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
@@suriyas_zone thnks bro
Find my approach below.
select category,subcategory from (
select c.*,
case when count(*) over(partition by category)=1 and price
Shouldn't the price have >=50 in this query ?
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
@@gouravbhatt5099 Hii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
Hai can we get job in Data Engineer role as a fresher
Hi,
Yes for sure. Definitely, a fresher can get a job as data engineer. But, many companies will not offer directly. I would recommend you to learn SQL, python, story telling, basics of data modelling, basics of Excel initially to get a job as a data analyst full-time or for the intern.. Get into the field first and try for a data engineer by learning, spark/Hadoop/hive (spark will be my choice) and one cloud.
Keep an eye on my TH-cam Channel to learn more about SQL, data modelling and python.
Thank you so much @@suriyas_zone
@@SuprajaBijjigiri-o4sHii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot
Hi bro.,
Very informative and impressive content
Love to connect with you
May I know your LinkedIn Id
@@roobanj9432 Please find the link below: www.linkedin.com/in/suriya-senthilkumar-3a069118b?
Hii bro i am having online tests and in that they were asking sql codes , due to time restriction i am trying to use chatgpt , but i am not getting proper codes , errors i am getting , please do u know any other website or any suggestion to get proper code
Please try to help or give ur suggestions please to use in ot