IBM Data Engineer SQL Interview Question (Hacker Rank Online Test)
Вставка
- Опубліковано 26 кві 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.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
**correction** : For the first solution it will be
you never cease to impress me. Thanks for your work!
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.
I never knew we can join based on a range condition, thank you for teachings!
You're so welcome!
Great ❤
Please do a video on except operator in SQL
Superb explanation Ankit 👌 👏 👍
Keep watching
Thank alot for this 😊
You're welcome 😊
what is the equivalent function for julianday in sql server and Ms sql
Hi Ankit, i purchased your sql and python course can you provide resources to learn pyspark
Good and easy question.
What is selection process
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)
I know very basic sql I want to learn joins and other import concepts with handson how to ?
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
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
Range join
One question is plj tell me if I learn only SQL can I got job or not
No
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
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
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
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
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);