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

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

[@ankitbansal6]

Don't forget to like the video please 🙏

[@mantisbrains]

select distinct region, house from (select * , max(total_wins) over (partition by region) max_wins from (select *, count(*) over (partition by region, house) as total_wins from (select region,house from (select name,region, case when attacker_outcome = 1 then attacker_king else defender_king end as winner from battle1)a join (select * from king1)b on a.winner = b.k_no)p)u)q where total_wins = max_wins; Thanks Ankit!

[@akashgoel601]

i like the mentos solution :) as i followed similar approach... only diff is the case statement i called before.. and that output i have used to compare the key columns!! cheers! with cte as ( select attacker_king,defender_king, case when attacker_outcome in (1) then attacker_king else defender_king end as outcome,region from battle ) select * from ( select k.house,c.region,count(*) as count, RANK() OVER (partition by c.region order by count(*) desc) as rn from cte c join king k on c.outcome=k.k_no group by k.house,c.region )A where rn=1

[@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

[@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 ,max(temp) from cte2 group by region

[@boppanakishankanna6029]

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

[@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

[@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

[@vanshhans5676]

Hi ankit. Here is my solution - with cte1 as( select region,case when attacker_outcome=1 then attacker_king else defender_king end as battle_winner from battle ), cte2 as( select cte1.region,king.house,count(*) as no_of_wins, rank() over(partition by cte1.region order by count(*) desc) as rn from cte1 inner join king on cte1.battle_winner=king.k_no group by cte1.region,king.house ) select * from cte2 where rn=1

[@Kirankumar-ml1ro]

with cte as ( select battle_number,region, case when attacker_outcome=1 then attacker_king when attacker_outcome=0 then defender_king end as who_won from battle ),cte_2 as ( select c.region,k.house,count(1) as no_of_wins,dense_rank() over(partition by region order by count(1) desc) as drnk from cte c join king k on c.who_won =k.k_no group by region,house ) select region,house,no_of_wins from cte_2 where drnk=1

[@nikhilpurwar697]

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

[@ramakumarguntamadugu1299]

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

[@karthikeyana6490]

Justice for battle of bastards😅 Great video btw!

[@vivekdutta7131]

with cte AS ( select *, case when attacker_outcome = 1 then attacker_king else defender_king end as 'won' from battle --select * from king; ),ctf AS ( select won, region, count(*) as cnt from cte group by won,region ),ctk AS ( select *, dense_rank() over(partition by region order by cnt desc) as rn from ctf ) select region,house,cnt as max_wins from ctk join king k on ctk.won = k.k_no where rn = 1 order by region

[@mayanksatija684]

My solution : with t1 as ( select Name, case when attacker_outcome=1 then attacker_king else defender_king end as winner_king, region from battle), t2 as ( select region,winner_king, k.house , from t1 inner join king k on t1.winner_king=k.k_no ) select * from ( select t2.region, t2.house, count(*) as no_of_wins ,dense_rank() over (partition by Region order by count(*) desc) as rank from t2 group by t2.region,t2.house ) a where a.rank =1

[@AmanRaj-p8w]

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

[@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

[@namanverma626]

Just came from fractal interview

[@karthikeyana6490]

Hope it went well

[@Sneaky08r]

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

[@prabhatgupta6415]

Oh Sir i never knew case when in joining thanks

[@imom369]

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

[@Deepika1295]

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

[@PraveenSinghRathore-df3td]

with cte as (select case when attacker_outcome = 1 then attacker_king else defender_king end as winner_king, region, king, house from battle b join king k on (case when attacker_outcome = 1 then attacker_king else defender_king end) = k.k_no order by winner_king, region, house, king), cte2 as (select count(house) as battle_count,region,king,house from cte group by 2,3,4) select region, house, battle_count from cte2 where (battle_count, region) in (select max(battle_count), region from cte2 group by 2);

[@skkholiya]

with cte_attacker_defender as( select *,if(attacker_outcome=1,attacker_king, defender_king) house from battle ), cte_max_region as( select cad.region,k.house,count(k.house) cnt from cte_attacker_defender cad, king k where cad.house=k.k_no group by cad.region,k.house ) select * from( select *,dense_rank() over(partition by region order by cnt desc) rnk from cte_max_region) a where rnk=1

[@Apromit]

Superb Solution😍

[@shikharsaxena8984]

Great 2nd solution 👏

[@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

[@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

[@hariikrishnan]

Beautiful!

[@sumitsaraswat5884]

It was great mentos moments.

[@harshilvadsara1609]

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!

[@Chathur732]

Hi bro,are you done with the interview. What was the result? can you guide me through the process?

[@harshilvadsara1609]

Hey, bro. I still haven't received an email about the interview schedule. I'm waiting for the technical round to be scheduled. I've forwarded an email regarding this issue, but I haven't had any luck. How about you? Do you know who your HR contact is?

[@rk-ej9ep]

Awesome..

[@meghnasoni]

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;

[@sahilummat8555]

;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

[@iamkiri_]

wow

[@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

[@sumitshrivastava4494]

with cte as ( SELECT *, case when attacker_outcome = 1 then attacker_king else defender_king end as winner from battle ) SELECT * from ( SELECT *, rank() over(partition by region order by no_of_wins desc) as rnk from ( SELECT region, house, count(house) as no_of_wins from ( SELECT c.*, k.* from cte c inner join king k on c.winner = k.k_no) a group by region, house ) b ) c where rnk = 1

[@avinashjadon4989]

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;

[@rahulmehla2014]

with cte as( select b.battle_number, b.name, k.king as attacker_king, k.house as attacker_king_house, k2.king as defender_king, k2.house as defender_king_house, attacker_outcome,region from battle b inner join king k on b.attacker_king = k.k_no inner join king k2 on b.defender_king = k2.k_no), cte2 as( select region,attacker_king_house house,attacker_outcome from cte union all select region,defender_king_house,case when attacker_outcome = 1 then 0 else 1 end attacker_outcome from cte) select * from ( select region,house,sum(attacker_outcome) no_of_wins,rank() over(partition by region order by sum(attacker_outcome) desc) rn from cte2 group by region,house) a where rn = 1

[@ethyria7685]

WITH cte AS (SELECT region, house, DENSE_RANK()over(PARTITION BY region ORDER BY COUNT(house) desc) as rnk, count(house) as no_of_wins FROM battle b LEFT JOIN king k ON CASE WHEN attacker_outcome = 1 THEN attacker_king ELSE defender_king END = k_no GROUP BY region, house) SELECT region, house, no_of_wins FROM cte WHERE rnk = 1

[@mathavansg9227]

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.

[@ramakumarguntamadugu1299]

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