IBM Data Engineer SQL Interview Question (Hacker Rank Online Test)

Подписаться 96 тыс.

Просмотров 14 тыс.

50% 1

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.com/
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

Опубликовано:

26 апр 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 32

@ankitbansal6 15 дней назад

**correction** : For the first solution it will be

@dfkgjdflkg 13 дней назад

you never cease to impress me. Thanks for your work!

@prakritigupta3477 8 дней назад

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

@meropahad7537 13 дней назад

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.

@sravankumar1767 13 дней назад

Superb explanation Ankit 👌 👏 👍

@ankitbansal6 13 дней назад

Keep watching

@user-jl5fr5cn1n 10 дней назад

what is the equivalent function for julianday in sql server and Ms sql

@shivanandkumar6002 12 дней назад

Thank alot for this 😊

@ankitbansal6 12 дней назад

You're welcome 😊

@kumarvummadi3772 9 дней назад

Please do a video on except operator in SQL

@naveent1793 12 дней назад

Hi Ankit, i purchased your sql and python course can you provide resources to learn pyspark

@akashjha7277 11 дней назад

Great ❤

@Tollybuff 10 дней назад

What is selection process

@Viralvlogvideos 12 дней назад

I know very basic sql I want to learn joins and other import concepts with handson how to ?

@ankitbansal6 12 дней назад

www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354

@harishmahale7180 13 дней назад

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)

@rahulmehla2014 12 дней назад

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

@Apna_tahlka_123 13 дней назад

One question is plj tell me if I learn only SQL can I got job or not

@idwtv534 13 дней назад

@GowthamR-ro2pt 12 дней назад

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.....

@ankitbansal6 12 дней назад

This also works 😊

@GowthamR-ro2pt 12 дней назад

@@ankitbansal6 😁👍🏻

@ShubhamKumar-Xplorer 7 дней назад

Range join

@Mathematica1729 12 дней назад

Good and easy question.

@prakritigupta3477 13 дней назад

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

@user-dw4zx2rn9v 3 дня назад

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

@Tollybuff 10 дней назад

Anybody written exam

@kedarwalavalkar6861 13 дней назад

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;

@vikas261196 9 дней назад

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;

@Rahelgodi 13 дней назад

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);