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

[@ankitbansal6]

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

[@saquibshaikh7730]

Is these exam proctored or not

[@jawakar8266]

I never knew we can join based on a range condition, thank you for teachings!

[@ankitbansal6]

You're so welcome!

[@prakritigupta3477]

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

[@dfkgjdflkg]

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

[@akashgoel601]

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

[@parmoddhiman678]

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

[@sravankumar1767]

Superb explanation Ankit 👌 👏 👍

[@ankitbansal6]

Keep watching

[@meropahad7537]

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.

[@sankarsarms]

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

[@definitesquare]

I think datediff?

[@rajbalachauhan7848]

Did anyone appear for hackerearth test for Data Engineer position in IBM ? what kind of questions they're looking for other than SQL?

[@shreeya7988]

Hey do you come to the conclusion? What type of questions are there DSA or SQL or both?

[@nithyabandla9289]

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

[@aneesahmed4564]

Congratulations on completing the assessment, I hope you’ll get the offer letter soon. By the way can you tell me what are the topics given in assessment? If possible questions 😅. Also you applied as fresher / experienced professional?

[@itzkarthik8431]

Did u got any update?

[@rahulmehla2014]

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

[@o-gr8ty]

Was this IBM Data Engineer role for Google Cloud Platform tools?

[@Tollybuff]

What is selection process

[@harishmahale7180]

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)

[@vinothkumars7421]

very intersting one

[@GowthamR-ro2pt]

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]

This also works 😊

[@GowthamR-ro2pt]

@@ankitbansal6 😁👍🏻

[@Viralvlogvideos]

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

[@ankitbansal6]

www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english

[@atharvaathalye]

When does ibm get back after the interview??

[@AmanRaj-p8w]

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

[@saramostafa2175]

Thank you

[@kumarvummadi3772]

Please do a video on except operator in SQL

[@MuskanGoyal-db7cs]

with cte as( select f.id, f.name as fname ,c.name as cname, c.min_size, c.max_size, f.family_size from countries c join families f where f.family_size>=c.min_size and f.family_size

[@deaspirant]

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

[@sandeepanand3834]

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;

[@kohinoorsuthar5808]

Are the questions same for every test?

[@shiva-KA01-rider]

Thank alot for this 😊

[@ankitbansal6]

You're welcome 😊

[@shanthiadhuri]

Is there is only SQL coding For Data engineer role . Please respond .

[@sayaninandi2190]

No, also coding question is there.

[@shreeya7988]

Hey! May I know what kind of questions are there.. like any coding questions from language or DSA or SQL..

[@akashjha7277]

Great ❤

[@Apna_tahlka_123]

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

[@idwtv534]

No

[@kedarwalavalkar6861]

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;

[@Mathematica1729]

Good and easy question.

[@prakritigupta3477]

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

[@Tollybuff]

Anybody written exam

[@YatriSpecial]

Range join

[@MrGaurav331]

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

[@DataAnalyst251]

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

[@vikas261196]

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;

[@HARSHRAJ-wz2rp]

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;

[@rupalakshmiprasanna2418]

Hii...Have you completed ur coding round recently? If yes, How many questions are there?