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
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
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?
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
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
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)
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.....
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
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
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
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;
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;
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;
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);
**correction** : For the first solution it will be
Is these exam proctored or not
you never cease to impress me. Thanks for your work!
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
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.
When does ibm get back after the interview??
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
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?
Did u got any update?
Superb explanation Ankit 👌 👏 👍
Keep watching
Thank you
What is selection process
very intersting one
When will be results announced
Please do a video on except operator in SQL
Are the questions same for every test?
Thank alot for this 😊
You're welcome 😊
Was this IBM Data Engineer role for Google Cloud Platform tools?
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
Hi Ankit, i purchased your sql and python course can you provide resources to learn pyspark
what is the equivalent function for julianday in sql server and Ms sql
I think datediff?
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
Did anyone appear for hackerearth test for Data Engineer position in IBM ? what kind of questions they're looking for other than SQL?
Hey do you come to the conclusion? What type of questions are there DSA or SQL or both?
Good and easy question.
Great ❤
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
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)
Is there is only SQL coding For Data engineer role .
Please respond .
No, also coding question is there.
Hey! May I know what kind of questions are there.. like any coding questions from language or DSA or SQL..
One question is plj tell me if I learn only SQL can I got job or not
No
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 😁👍🏻
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
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
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;
Anybody written exam
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
Range join
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
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;
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;
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);