Fractal Analytics SQL Interview Question (Game of Thrones Database)

Fractal Analytics SQL Interview Question (Game of Thrones Database) | SQL for Data Engineer

Рет қаралды 17,173

Ай бұрын

In this video we will discuss an advanced interview question on SQL asked in a Data Engineer interview based on Game of Thrones Database.
Kickoff your data analytics journey : www.namastesql.com/
Script:
-- Create the 'king' table
CREATE TABLE king (
k_no INT PRIMARY KEY,
king VARCHAR(50),
house VARCHAR(50)
);
-- Create the 'battle' table
CREATE TABLE battle (
battle_number INT PRIMARY KEY,
name VARCHAR(100),
attacker_king INT,
defender_king INT,
attacker_outcome INT,
region VARCHAR(50),
FOREIGN KEY (attacker_king) REFERENCES king(k_no),
FOREIGN KEY (defender_king) REFERENCES king(k_no)
);
delete from king;
INSERT INTO king (k_no, king, house) VALUES
(1, 'Robb Stark', 'House Stark'),
(2, 'Joffrey Baratheon', 'House Lannister'),
(3, 'Stannis Baratheon', 'House Baratheon'),
(4, 'Balon Greyjoy', 'House Greyjoy'),
(5, 'Mace Tyrell', 'House Tyrell'),
(6, 'Doran Martell', 'House Martell');
delete from battle;
-- Insert data into the 'battle' table
INSERT INTO battle (battle_number, name, attacker_king, defender_king, attacker_outcome, region) VALUES
(1, 'Battle of Oxcross', 1, 2, 1, 'The North'),
(2, 'Battle of Blackwater', 3, 4, 0, 'The North'),
(3, 'Battle of the Fords', 1, 5, 1, 'The Reach'),
(4, 'Battle of the Green Fork', 2, 6, 0, 'The Reach'),
(5, 'Battle of the Ruby Ford', 1, 3, 1, 'The Riverlands'),
(6, 'Battle of the Golden Tooth', 2, 1, 0, 'The North'),
(7, 'Battle of Riverrun', 3, 4, 1, 'The Riverlands'),
(8, 'Battle of Riverrun', 1, 3, 0, 'The Riverlands');
--for each region find house which has won maximum no of battles. display region, house and no of wins
select * from battle;
select * from king;
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 #got

Пікірлер: 36

@ankitbansal6 Ай бұрын

Don't forget to like the video please 🙏

@MuskanGoyal-db7cs Ай бұрын

with cte as (select * , case when attacker_outcome =1 then attacker_king else defender_king end as win_id from battle order by region), cte2 as(select battle_number, name,region,king,house, count(*) as temp from cte c join king k on win_id=k_no group by region ,house) select region,house,temp from cte2

@saralavasudevan5167 Ай бұрын

Hi Ankit! Thank you for such an interesting question and solving it with an awesome approach! Please make more complex interview SQL videos :). This was my solve: with mycte as ( select b.*, a.king as attacker_king_name, d.king as defender_king_name, case when attacker_outcome = 1 then a.house else d.house end as winner from battle as b join king as a on b.attacker_king = a.k_no join king as d on b.defender_king = d.k_no ), cte2 as ( select region, winner, count(winner) as totalwins, rank() over(partition by region order by count(winner) desc) as rn from mycte group by region, winner ) select region, winner as house, totalwins as no_of_wins from cte2 where rn = 1

@ramakumarguntamadugu1299 28 күн бұрын

Great take away from this solution is using case statement in join👍 mentos zindagi😊😊 Please do post python interview questions as well👍

@rohitsharma-mg7hd Ай бұрын

with win_k as (select region, case when attacker_outcome=1 then attacker_king else defender_king end as winner from battle) select w.region,k.house,count(k.king) as win from win_k w inner join king k on w.winner=k.k_no group by w.region,k.house order by w.region,k.house

@hariikrishnan Ай бұрын

Beautiful!

@Apromit Ай бұрын

Superb Solution😍

@nikhilpurwar697 20 күн бұрын

with cte2 as( select * ,case when attacker_outcome=1 then attacker_king else defender_king end as who_win from battle ) select b.region,k.house ,count(k.k_no) as count from cte2 b left join king k on k.k_no =b.who_win group by b.who_win ,b.region order by count(k_no);

@boppanakishankanna6029 26 күн бұрын

Thank You for the concept. Using case statement in Join condition for the first time😄

@shikharsaxena8984 Ай бұрын

Great 2nd solution 👏

@karthikeyana6490 Ай бұрын

Justice for battle of bastards😅 Great video btw!

@rk-ej9ep Ай бұрын

Awesome..

@prabhatgupta6415 Ай бұрын

Oh Sir i never knew case when in joining thanks

@MukeshNanda-fu8bd Ай бұрын

Hi Ankit, I have not sorted but at least was able to get the output - this is my code------ Select K.house,nj.region,COUNT(nj.battle_number) from( Select case when b.attacker_outcome =1 then b.attacker_king else b.defender_king end as WHO_WON , * from battle b )nj inner join king K on nj.WHO_WON=k.k_no group by K.house,nj.region

@vaibhavverma1340 Ай бұрын

with cte as (select *, (case when attacker_outcome = '1' then attacker_king else defender_king end) res from battle b) , fin_res as (select region, house, count(house)house_cnt, rank() over (partition by region order by count(*)desc) no_of_winners from cte c inner join king k on c.res = k.k_no group by region, house) select * from fin_res where no_of_winners = 1

@sumitsaraswat5884 Ай бұрын

It was great mentos moments.

@namanverma626 Ай бұрын

Just came from fractal interview

@karthikeyana6490 Ай бұрын

Hope it went well

@gokulr7276 11 күн бұрын

What was the role?? Can we connect pls..I want your help

@vinaykumarpatnana Ай бұрын

select a.region, b.house, count(1) cnt, max(cnt)over(partition by a.region) mx from (select * , case when ATTACKER_OUTCOME = 1 then ATTACKER_KING else DEFENDER_KING end win_team from battle) a left join (select * from king) b on a.win_team = b.k_no group by 1,2 qualify mx=cnt order by 1

@user-dw4zx2rn9v Ай бұрын

Mysql Solution: with cte as ( select *, case when attacker_outcome = 0 then defender_king else attacker_king end as winner from battle ) ,cte2 as (select region, house, cnt ,dense_rank() over (partition by region order by cnt desc) as rnk from (select region, house, count(*) as cnt from cte as c inner join king as k on c.winner = k.k_no group by region , house ) as x ) select * from cte2 where rnk = 1

@Deepika1295 Ай бұрын

Thank you sir for this Could you pls make a video on VIEW, INDEX,STORED PROCEDURE, FUNCTIONS these are seems so confusing

@dineshbandi-zf8by Ай бұрын

Hi @Ankit Bansal Please check and confirm if below is correct ? with cte as ( select region, attacker_won, count(attacker_won) as cnt from ( select region ,case when attacker_outcome = 1 then attacker_king else defender_king end as attacker_won from battle) A group by region, attacker_won) ,cte2 as ( select *, RANK() over(partition by region order by region, cnt desc) as rnk from cte) select k.house, c.region,c.cnt as no_of_wins from king k INNER JOIN cte2 c on k.k_no = c.attacker_won where c.rnk = 1

@iamkiri_ 27 күн бұрын

wow

@imom369 26 күн бұрын

HI sir, for first solution method using RANK() function or dense_rank() function which is optimum to use when datasets are large

@harshilvadsara1609 22 күн бұрын

I have passed the first round of the technical test at Fractal Analytics. During the test, I was asked SQL questions, verbal ability, and aptitude questions. The next steps in the process are a 30-minute Technical Round, a 30-minute Managerial Round, and a 30-minute Culture Fitment Round. Can anyone who has gone through this process guide or help me with some tips? Thanks!

@sahilummat8555 12 күн бұрын

;with cte as ( select * ,case when attacker_outcome=1 then attacker_king else defender_king end as winning_king from battle ), cte2 as ( select *, count(c.winning_king)over(partition by region,winning_king) as cnt from cte c left join king k on c.winning_king=k.k_no) select region,house,cnt from ( select *,rank()over(partition by region order by cnt desc) as rnk from cte2)a where rnk=1 group by region,house,cnt

@manishbaburaisingh1985 Ай бұрын

with cte1 as ( select b.region, k.house, Case when b.attacker_outcome=1 then b.attacker_king else b.defender_king End as King_no from battle b join king k on Case when b.attacker_outcome=1 then b.attacker_king else b.defender_king End=k.k_no ), ranked_win as ( select region , house , count(*) as no_of_wins, rank() over(partition by region order by count(*) desc)as rn from cte1 group by region,house ) select region,house,no_of_wins,rn from ranked_win where rn =1

@meghnasoni 22 күн бұрын

SELECT region, house,COUNT( k_no) as wins FROM (SELECT *,CASE WHEN attacker_outcome =1 THEN attacker_king ELSE defender_king END As winning_king FROM battle ) main LEFT JOIN king ON main.winning_king = king.k_no GROUP BY 1,2;

@avinashjadon4989 29 күн бұрын

with a as( SELECT region,attacker_king, case when attacker_outcome=1 = 1 then attacker_king else defender_king end as winner FROM battle ) select * from ( select a.region as region,k.house as house,count(*) as noofwin, rank() over(partition by region order by count(*) desc) as rn from King k inner join a on a.winner=k.k_no group by region,house) A where rn=1;

@mathavansg9227 22 күн бұрын

with cte as (SELECT b.*,k.king as attacker_king_name ,k1.king as defender_king_name from battle as b inner join king as k on b.attacker_king=k.k_no inner join king as k1 on b.defender_king=k1.k_no) ,cte2 as (SELECT cte.region,cte.attacker_king_name,cte.defender_king_name, king.house , case when attacker_outcome=1 then attacker_king_name else defender_king_name end as winner_name, count(case when attacker_outcome=1 then attacker_king_name else defender_king_name end) as wins from cte inner join king on king.king=winner_name group by 1,4 ) ,cte3 as ( SELECT *,dense_rank() over(partition by region order by wins desc) as rn from cte2 ) SELECT * from cte3 where rn==1

@harshitsalecha221 Ай бұрын

WITH cte1 AS (SELECT region, house, count(CASE WHEN attacker_outcome=1 THEN 1 ELSE Null END) wins FROM battle as b INNER JOIN king as k ON b.attacker_king=k.k_no GROUP BY region,house UNION ALL SELECT region, house, count(CASE WHEN attacker_outcome=0 THEN 1 ELSE Null END) wins FROM battle as b INNER JOIN king as k ON b.defender_king=k.k_no GROUP BY region,house) SELECT region,house, total_wins FROM (SELECT region, house, SUM(wins) as total_wins, RANK() OVER(PARTITION BY region ORDER BY Sum(wins) DESC) as RN FROM cte1 GROUP BY region,house) as a WHERE RN=1;

@ankitbansal6 Ай бұрын

Can be simplified 😊

@harshitsalecha221 Ай бұрын

@Ankit Yes we can.

@MuskanGoyal-db7cs Ай бұрын

@ramakumarguntamadugu1299 28 күн бұрын

Great take away from this solution is using case statement in join👍 mentos zindagi😊😊 Please do post python interview questions as well👍