IBM Data Engineer SQL Interview Question (Hacker Rank Online Test)
HTML-код
- Опубликовано: 6 сен 2024
- In this video we are going to discuss a SQL interview question asked in IBM for a data engineer position. It was part of a hacker rank test. We are also going to tweak the question a bit and try to solve it.
Kick off Your Data Analytics Journey: www.namastesql...
script:
CREATE TABLE FAMILIES (
ID VARCHAR(50),
NAME VARCHAR(50),
FAMILY_SIZE INT
);
-- Insert data into FAMILIES table
INSERT INTO FAMILIES (ID, NAME, FAMILY_SIZE)
VALUES
('c00dac11bde74750b4d207b9c182a85f', 'Alex Thomas', 9),
('eb6f2d3426694667ae3e79d6274114a4', 'Chris Gray', 2),
('3f7b5b8e835d4e1c8b3e12e964a741f3', 'Emily Johnson', 4),
('9a345b079d9f4d3cafb2d4c11d20f8ce', 'Michael Brown', 6),
('e0a5f57516024de2a231d09de2cbe9d1', 'Jessica Wilson', 3);
-- Create COUNTRIES table
CREATE TABLE COUNTRIES (
ID VARCHAR(50),
NAME VARCHAR(50),
MIN_SIZE INT,
MAX_SIZE INT
);
INSERT INTO COUNTRIES (ID, NAME, MIN_SIZE,MAX_SIZE)
VALUES
('023fd23615bd4ff4b2ae0a13ed7efec9', 'Bolivia', 2 , 4),
('be247f73de0f4b2d810367cb26941fb9', 'Cook Islands', 4,8),
('3e85ab80a6f84ef3b9068b21dbcc54b3', 'Brazil', 4,7),
('e571e164152c4f7c8413e2734f67b146', 'Australia', 5,9),
('f35a7bb7d44342f7a8a42a53115294a8', 'Canada', 3,5),
('a1b5a4b5fc5f46f891d9040566a78f27', 'Japan', 10,12);
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
**correction** : For the first solution it will be
I never knew we can join based on a range condition, thank you for teachings!
You're so welcome!
I also managed to solve the question:
with cte as (select e.name as family_person, e.family_size,w.name as country_name, w.min_size,w.max_size
from families as e, countries as w
where e.family_size>=w.min_size and e.family_size
you never cease to impress me. Thanks for your work!
thanks, my sol. on similar line of code:
with cte as (
select f.family_size,count(c.min_size) as cnt
from families f join countries c on c.min_size=f.family_size
group by family_size
)
select family_size
from cte
where cnt in (select max(cnt) from cte)
order by 1 desc
Thanks Ankit😊. Can you please make a video with example to show difference between schema and database. These two are quite confusing and most of the time I see people using them interchangeably.
Actually i have completed the coding assessment with all test cases passed for data engineer role on 2nd August but still i don't get any update
Can u tell me sir when will i get the update
with cte as (SELECT f.NAME,count(1)
FROM families f
join countries c
on FAMILY_SIZE between MIN_SIZE and MAX_SIZE
group by 1
order by 2 desc
limit 1)
select name from cte
Was this IBM Data Engineer role for Google Cloud Platform tools?
Hi Ankit 😊, I have an approach for the original question (Hacker Rank):
with cte as (select F.NAME Family,C.NAME Country,F.FAMILY_SIZE,C.MIN_SIZE from FAMILIES F
INNER JOIN COUNTRIES C
ON F.FAMILY_SIZE >= C.MIN_SIZE)
select Family,count(*) Eligible
from cte
group by Family
order by Eligible desc
Correct me if I am wrong.....
This also works 😊
@@ankitbansal6 😁👍🏻
Superb explanation Ankit 👌 👏 👍
Keep watching
select max(cnt) from
(SELECT name,COUNT(TOUR) as cnt from
(select F.name,F.FAMILY_SIZE, C.MIN_SIZE,C.MAX_SIZE,
CASE WHEN F.FAMILY_SIZE BETWEEN C.MIN_SIZE AND C.MAX_SIZE THEN F.NAME END AS TOUR
from FAMILIES F,COUNTRIES_1 C )
GROUP BY name)
Did anyone appear for hackerearth test for Data Engineer position in IBM ? what kind of questions they're looking for other than SQL?
When does ibm get back after the interview??
my approach :
with cte as(
select f.name,
f.family_size,c.min_size,
case when f.family_size >= c.min_size and f.family_Size
What is selection process
Mysql solution with country name: with cte as (
select f.id as fam_id, f.name as fam_name , family_size, c.id as country_id, c.name as c_name, c.min_size, max_size from families as f
cross join countries as c
where min_size
very intersting one
what is the equivalent function for julianday in sql server and Ms sql
I think datediff?
Please do a video on except operator in SQL
Hi Ankit, i purchased your sql and python course can you provide resources to learn pyspark
I'm using this approach. please correct me if I'm wrong. I didn't look at the solution as I was trying to solve it by myself
WITH CTE AS (
SELECT family.id, family.name AS family_name, family_size, countries.country, countries.min_size
FROM family
JOIN countries ON family_size >= min_size
)
SELECT family_name, COUNT(family_name) AS total_discounted_trip
FROM CTE
GROUP BY family_name;
I know very basic sql I want to learn joins and other import concepts with handson how to ?
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
Is there is only SQL coding For Data engineer role .
Please respond .
No, also coding question is there.
my solution:
with families_qualified_for_discount as (
select f.name as person_name, c.name as country_name
from families f
join countries c
on f.family_size BETWEEN c.MIN_SIZE and c.MAX_SIZE
)
select count(country_name) as total_countries_where_qualified_for_discount
from families_qualified_for_discount
group by person_name
order by count(country_name) desc
limit 1;
Please verify my solutions as well: "with cte as (select e1.name as family_person, e1.family_size, e2.name as country_name,e2.max_size,e2.min_size
from families as e1, countries as e2 where e1.family_size>=e2.min_size or e1.family_size
Are the questions same for every test?
Thank alot for this 😊
You're welcome 😊
Great ❤
One question is plj tell me if I learn only SQL can I got job or not
No
Range join
Anybody written exam
To show country name also we can use below:
select f.NAME as custo_name, count(*) as cnt,
group_concat(c.NAME order by c.NAME) country_customer_can_go
from COUNTRIES c join FAMILIES f
on f.FAMILY_SIZE between c.MIN_SIZE and c.MAX_SIZE
group by f.NAME
order by count(*) desc limit 1;
Good and easy question.
with cte as (
SELECT
FAMILIES.name as name,
family_size,
COUNTRIES.NAME as country_name
from FAMILIES left join COUNTRIES
where
FAMILIES.FAMILY_SIZE BETWEEN COUNTRIES.MIN_SIZE and COUNTRIES.MAX_SIZE
)
SELECT
name,
count(country_name) as country_count
from cte
group by 1
IBM DATE ENGINNER SQL SOLUTION:-
with cte as(
select FAMILY_SIZE FROM Families
),
cte1 as(
select MIN_SIZE FROM COUNTRIES
),cte2 as(
select * FROM cte CROSS JOIN cte1
),cte3 as(
select FAMILY_SIZE,COUNT(*) as count1 FROM cte2 where FAMILY_SIZE>=MIN_SIZE GROUP BY FAMILY_SIZE
ORDER BY COUNT(*) DESC LIMIT 1
)
select count1 FROM cte3;
2nd queston solution:-
with cte as(
select FAMILIES.FAMILY_SIZE,MIN_SIZE,MAX_SIZE FROM FAMILIES JOIN COUNTRIES ON FAMILIES.FAMILY_SIZE
BETWEEN COUNTRIES.MIN_SIZE AND COUNTRIES.MAX_SIZE
),CTE1 AS(
select FAMILY_SIZE,COUNT(*) as x1 FROM cte where FAMILY_SIZE BETWEEN MIN_SIZE AND MAX_SIZE GROUP by
FAMILY_SIZE ORDER BY x1 DESC LIMIT 1
)
select x1 FROM CTE1;
Hii...Have you completed ur coding round recently?
If yes, How many questions are there?
output with family name too:
with cte1 as (select FAMILIES.NAME, COUNT(*) pt from FAMILIES join COUNTRIES
on FAMILIES.FAMILY_SIZE between COUNTRIES.MIN_SIZE and COUNTRIES.MAX_SIZE
group by FAMILIES.NAME)
select name,pt from cte1 where pt = (select max(pt) from cte1);