Tricky SQL Interview Question by a Product Based Company | Ludo King SQL Analytics

In this video we will discuss a very interesting SQL interview problem based on ludo king game. This was asked in a Gameberry Labs Interview for a product analyst position.
Start your data analytics journey: www.namastesql...
Script:
CREATE TABLE user_interactions (
user_id varchar(10),
event varchar(15),
event_date DATE,
interaction_type varchar(15),
game_id varchar(10),
event_time TIME
);
-- Insert the data
INSERT INTO user_interactions
VALUES
('abc', 'game_start', '2024-01-01', null, 'ab0000', '10:00:00'),
('def', 'game_start', '2024-01-01', null, 'ab0000', '10:00:00'),
('def', 'send_emoji', '2024-01-01', 'emoji1', 'ab0000', '10:03:20'),
('def', 'send_message', '2024-01-01', 'preloaded_quick', 'ab0000', '10:03:49'),
('abc', 'send_gift', '2024-01-01', 'gift1', 'ab0000', '10:04:40'),
('abc', 'game_end', '2024-01-01', NULL, 'ab0000', '10:10:00'),
('def', 'game_end', '2024-01-01', NULL, 'ab0000', '10:10:00'),
('abc', 'game_start', '2024-01-01', null, 'ab9999', '10:00:00'),
('def', 'game_start', '2024-01-01', null, 'ab9999', '10:00:00'),
('abc', 'send_message', '2024-01-01', 'custom_typed', 'ab9999', '10:02:43'),
('abc', 'send_gift', '2024-01-01', 'gift1', 'ab9999', '10:04:40'),
('abc', 'game_end', '2024-01-01', NULL, 'ab9999', '10:10:00'),
('def', 'game_end', '2024-01-01', NULL, 'ab9999', '10:10:00'),
('abc', 'game_start', '2024-01-01', null, 'ab1111', '10:00:00'),
('def', 'game_start', '2024-01-01', null, 'ab1111', '10:00:00'),
('abc', 'game_end', '2024-01-01', NULL, 'ab1111', '10:10:00'),
('def', 'game_end', '2024-01-01', NULL, 'ab1111', '10:10:00'),
('abc', 'game_start', '2024-01-01', null, 'ab1234', '10:00:00'),
('def', 'game_start', '2024-01-01', null, 'ab1234', '10:00:00'),
('abc', 'send_message', '2024-01-01', 'custom_typed', 'ab1234', '10:02:43'),
('def', 'send_emoji', '2024-01-01', 'emoji1', 'ab1234', '10:03:20'),
('def', 'send_message', '2024-01-01', 'preloaded_quick', 'ab1234', '10:03:49'),
('abc', 'send_gift', '2024-01-01', 'gift1', 'ab1234', '10:04:40'),
('abc', 'game_end', '2024-01-01', NULL, 'ab1234', '10:10:00'),
('def', 'game_end', '2024-01-01', NULL, 'ab1234', '10:10:00');
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

[@ankitbansal6]

1000 likes on this video and I will share the rest of the 2 questions from Gameberry. All the questions are really good 😊.

[@monuoriginal7425]

brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....

[@UnrealAdi]

@aniktbansal6 Hi, Just an observation: Data provided in the script doesn't match with Data in your database! I think folks may get confused as to why their answer does not match with yours! Thanks for the extraordinary content! Much appreciated!

[@radhikajujjavarapu6971]

@ankitbansal6 Videos are not accessible in your courses. Can you please look into?

[@aniketpandey4084]

Just wrote a zillion lines of code and came again to see your approach and am stunned!! you are the best.

[@ankitbansal6]

😊

[@Tusharchitrakar]

Essentially just wanted to mention a small modification in your beautiful code (great use of the count(distinct case...) since that saves many ctes!), for category 3 and 4 you do not need to give the previous conditions again. Since the way the case statement works is that it goes through each case and escapes as soon as it finds the right case. So the bottom code will work fine too: with cte as ( select game_id, case when count(interaction_type)=0 then 'category1' when count(distinct case when interaction_type is not null then user_id else NULL end)=1 then 'category2' when count(case when interaction_type='custom_typed' then 1 else NULL end)>=1 then 'category4' else 'category3' end as game_type from interview_test_46 group by game_id) select game_type, count(1)*100/(count(1) over ()) as percentage from cte group by game_type order by game_type;

[@ankitbansal6]

You are right. I will pin your comment for others benefit 😊

[@Tusharchitrakar]

@@ankitbansal6 but Ankit Bhai, as an aspiring data scientist, you have taught me so many little tricks specifically aimed at code optimization! Whenever anyone asks me regarding SQL material, i always tell them that your youTube channel is analogous to "leetcode" for programming. Hope your new life post Amazon is going great and sending you as well as your family warmest wishes!

[@mohdtoufique3786]

Hi Ankit! Thanks for thye content My approach WITH flags AS( SELECT game_id, COUNT (DISTINCT CASE WHEN interaction_type IN('emoji1','gift1','preloaded_quick') THEN user_id END) AS no_non_custom, COUNT (DISTINCT CASE WHEN interaction_type='custom_typed' THEN user_id END) AS no_custom, COUNT(game_id) OVER() AS total_games FROM user_interactions GROUP BY game_id), bucket AS( SELECT *, CASE WHEN no_non_custom=0 and no_custom=0 THEN 'No Social Interaction' WHEN no_non_custom=1 and no_custom=1 THEN 'One Sided Interaction' WHEN no_non_custom=2 and no_custom=0 THEN 'Both sided without custom' WHEN no_non_custom=2 and no_custom>=1 THEN 'Both sided with custom from one side' END AS Interaction_bucket FROM flags) SELECT Interaction_bucket,count(*)*1.0/sum(total_games) FROM bucket GROUP BY Interaction_bucket

[@tanmaymodi8284]

HI Ankit, kindly check this: with cte as( select user_id, game_id, x = count(interaction_type), y = count(case when interaction_type = 'custom_typed' then 1 end) from user_interactions group by user_id, game_id ) select cte.game_id, case when max(x) = 0 then 'No interaction' when max(x) 0 and min(x) = 0 then 'One sided Interaction' when max(x) 0 and min(x) 0 and max(y) 0 then 'Both side interaction with custom_typed' when max(x) 0 and min(x) 0 then 'Both side interaction' end as game_interaction from cte group by cte.game_id

[@rockingsurya4993]

Its a very good helper question to practice conditional aggregations. Thanks for it.

[@akashjha7277]

You are best in clarity of explanation,thanks

[@akashgoel601]

awesome man!! i was also able to get the output but the path was way too long.. i dint know this way also case statements can be used.. thanks... cheers!!

[@rahulmehla2014]

My solution :- with cte as( select game_id,user_id, case when count(interaction_type) = 0 then 1 when sum(case when interaction_type = 'custom_typed' then 0 end) = 0 then 0 else 3 end Flag from user_interactions group by game_id,user_id), cte2 as( select game_id, case when sum(flag) = 2 then 'No Social Interaction' when sum(flag) = 1 then 'One Sided Interaction' when sum(flag) = 3 then 'Both Side Interaction with custom_typed_messages from atleast one player' else 'Both Side Interaction without custom_typed_messages' end Type from cte group by game_id) select type,count(1)/(select count(1) from cte2) * 100 as pct from cte2 group by type

[@hii.2306]

I would request you to add sql 100 challenge course for those who have already taken your python and sql course recorded session.

[@MukeshYadav-wb5uo]

Thanks

[@zainaltaf4935]

Nice video and insightful ankit

[@arpanscreations6954]

My solution: with cte as ( select game_id, STRING_AGG(interaction_type, ' ') as interactions, count(distinct case when interaction_type is not null then user_id else null end) as interactive_user_cnt from user_interactions group by game_id ) , games_category as ( select game_id , case when max(interactions) is null then 'no_interaction' when max(interactive_user_cnt) = 1 then 'one_side_interaction' when max(interactive_user_cnt) = 2 and max(interactions) like '%custom_typed%' then 'both_side_interaction_with_custom_typed' when max(interactive_user_cnt) = 2 and max(interactions) not like '%custom_typed%' then 'both_side_interaction_without_custom_typed' end as category from cte group by game_id ) select category, count(category)*1.0/(select count(*) from games_category) as percentage_of_games from games_category group by category

[@Satish_____Sharma]

Here is my solution using MYSQL SELECT game_id,case when sum(interaction_type) is null then 'No interaction' when count(distinct case when interaction_type is not null then user_id end)=2 and count(distinct case when interaction_type='custom_typed' then user_id end)=0 then 'Both side witout c' when count(distinct case when interaction_type is not null then user_id end)=2 and count(distinct case when interaction_type='custom_typed' then user_id end)>=1 then 'Both side with c' when count(distinct case when interaction_type is not null then user_id end)=1 and count(distinct case when interaction_type='custom_typed' then user_id end)>=1 then 'one side with c' end as game_type FROM user_interactions group by game_id

[@takemepeak1608]

Have seen Latest LinkedIn Post, Where you have discussed about How i cracked Walmart as Data analyst & Amazon as Data Engineer. The ask is how the SQL & Problem Solving Skill help you to crack those 2 interviews.Can you help me What is exactly the problem solving skill .Can you make any video on it?

[@kedarwalavalkar6861]

My solution in Mysql : - with cte as ( select *, count(case when interaction_type is not null and user_id = 'abc' then 1 else null end) over(partition by game_id) as interaction_by_pl1 ,count(case when interaction_type is not null and user_id = 'def' then 1 else null end) over(partition by game_id) as interaction_by_pl2 ,count(case when interaction_type = 'custom_typed' then 1 else null end) over(partition by game_id) as custom_msg_cnt from user_interactions ) ,cte2 as ( select distinct game_id ,interaction_by_pl1 ,interaction_by_pl2 ,custom_msg_cnt ,case when interaction_by_pl1 = 0 and interaction_by_pl2 = 0 then 'No Social Interaction' when interaction_by_pl1 0 and interaction_by_pl2 = 0 or interaction_by_pl2 0 and interaction_by_pl1 = 0 then 'One-sided Interaction' when interaction_by_pl1 0 and interaction_by_pl2 0 and custom_msg_cnt 0 then 'Both Sided Interaction with custom message' when interaction_by_pl1 0 and interaction_by_pl2 0 and custom_msg_cnt = 0 then 'Both Sided Interaction without custom message' end as categories from cte ) select categories, round(count(game_id) *1.0/(select count(distinct game_id) from user_interactions)*100,0) as perc from cte2 group by categories;

[@florincopaci6821]

Is not indicated to hardcore values like you do in your solution-hardcoding the user_id

[@edumail1016]

SELECT game_id, CASE WHEN COUNT(interaction_type)=0 THEN 'No user interaction' WHEN COUNT(DISTINCT CASE WHEN interaction_type IS NOT NULL THEN user_id END)=1 THEN 'One sided interaction' WHEN COUNT(DISTINCT CASE WHEN interaction_type IS NOT NULL THEN user_id END)>1 AND SUM(CASE WHEN interaction_type='custom_typed' THEN 1 ELSE 0 END)>0 THEN 'Both sided interactions with custom typed message' WHEN COUNT(DISTINCT CASE WHEN interaction_type IS NOT NULL THEN user_id END)>1 AND SUM(CASE WHEN interaction_type='custom_typed' THEN 1 ELSE 0 END)=0 THEN 'Both sided interactions without custom typed message' END AS game_interaction FROM user_interactions GROUP BY game_id

[@zainaltaf4935]

I am waiting, can you share more videos on aws and end to end data engineering projects

[@ankitbansal6]

Coming soon 🔜

[@KoushikT]

My Solution -- Q1 select game_id from user_interactions where event not in ('game_start', 'game_end') group by game_id having count(interaction_type)= 0; --Q2 select game_id, user_id from user_interactions where event not in ('game_start', 'game_end') group by game_id, user_id having count(interaction_type)= 1; -- Q3 select game_id, user_id from user_interactions where event not in ('game_start', 'game_end') and interaction_type not in ('custom_typed') group by game_id, user_id having count(interaction_type)> 1; -- Q4 select game_id, user_id from user_interactions where event not in ('game_start', 'game_end') group by game_id, user_id having count(interaction_type)> 1 and sum( case when interaction_type = 'custom_typed' then 1 else 0 end ) > 0;

[@shubhammishra2225]

Hi Ankit can you please make some videos on Tableau....

[@nikhilsharma-jj6vd]

Hello Sir. I have my interview for Amazon BI Engineer role next week . Can you guide how to prepare for SQL?

[@ankitbansal6]

Practice from here Complex SQL Questions for Interview Preparation: kzbin.info/aero/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

[@user-bk4dy6sw2e]

Hi Ankit I took self paced course in that course you haven't explained different types of subqueries like single row, multi row,multi column,co-related subqueries I was expected keen to learn on subqueries under your teaching somehow I had disappointed about that concept I just add my words "your serving subject to students another level" I just want to learn subquery concepts under your training pls make a full pledged video Sorry if I hurt you I hope I'll get my requesting video. For sake of subqueries I took your course I have been watching your videos from the day you started on KZbin series .

[@ankitbansal6]

You don't need to learn all these terms. Just sub queries taught in course is good enough. Trust me you don't need to know anything else neither for your office work nor for interviews 😊

[@user-bk4dy6sw2e]

@@ankitbansal6 I had watched more than 2 times I'm not able to pick the topics I got struck on subquery concept whatever you discussed earlier topics I had done assignments, my mad I'm not able to picking this topic especially alias table name and you were trying to join the two tables and cte concept any suggestion to learn or pickup the topics I have to learn SQL , I am ready to spend 12hr/day also.

[@pateldh23]

Sir, make video on banking transaction related videos please

[@ankitbansal6]

Let me know if you have any questions

[@pateldh23]

@@ankitbansal6 Like balance, received payment, cash withdrawal, interest rate etc

[@monuoriginal7425]

brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am begginer also ....

[@adityap9223]

you didn't put condition for last 7 days

[@nishan7122]

Hi ankit where can I apply that discount voucher on think sql course. It id not giving any option it is directly redirecting to payment

[@ankitbansal6]

Just before payment page

[@nishan7122]

@@ankitbansal6 I tried but it is directly redirecting it to payment option

[@ankitbansal6]

@@nishan7122 www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354

[@monuoriginal7425]

brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....😮‍💨😮‍💨😮‍💨😮‍💨

[@ankitbansal6]

Electoral bond ? Please give more context

[@monuoriginal7425]

@@ankitbansal6 electoral bond is latest trend in India for upcoming lok sabha elections there is data in 2 silos and I have to join both but I am beginner of you can do once I can learn from that thanks.... Link - www.eci.gov.in/disclosure-of-electoral-bonds

[@ethyria7685]

WITH CTE AS (SELECT user_id, game_id, max(case when interaction_type is not null then 1 else 0 end) as flag1, sum(case when interaction_type = 'custom_typed' then 1 else 0 end) as flag2 FROM game_actions GROUP BY user_id, game_id), cte2 as (SELECT game_id, case when sum(flag1) = 0 and sum(flag2) = 0 THEN 'No Social Interaction' when sum(flag1) = 1 and sum(flag2) = 0 or sum(flag1) = 1 and sum(flag2) = 1 THEN 'One sided interaction' when sum(flag1) = 2 and sum(flag2) = 0 THEN 'Both sided Interaction without custom' when sum(flag1) = 2 and sum(flag2) = 1 THEN 'Both sided Interaction with custom' END as int_flag , count(1)over() as tot_games FROM CTE GROUP BY game_id) SELECT int_flag, ROUND((COUNT(int_flag) * 100.0 / tot_games), 2) as dist FROM cte2 GROUP by int_flag, tot_games;