Tricky SQL Interview Question by a Product Based Company

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

Рет қаралды 7,669

3 ай бұрын

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

Пікірлер: 44

@ankitbansal6 3 ай бұрын

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

@monuoriginal7425 3 ай бұрын

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

@UnrealAdi 3 ай бұрын

@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 3 ай бұрын

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

@aniketpandey4084 3 ай бұрын

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

@ankitbansal6 3 ай бұрын

😊

@Tusharchitrakar 3 ай бұрын

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 3 ай бұрын

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

@Tusharchitrakar 3 ай бұрын

@@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 3 ай бұрын

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

@rockingsurya4993 3 ай бұрын

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

@akashjha7277 3 ай бұрын

You are best in clarity of explanation,thanks

@zainaltaf4935 3 ай бұрын

Nice video and insightful ankit

@MukeshYadav-wb5uo 2 ай бұрын

Thanks

@hii.2306 3 ай бұрын

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

@Satish_____Sharma 3 ай бұрын

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

@KoushikT 3 ай бұрын

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 3 ай бұрын

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

@kedarwalavalkar6861 3 ай бұрын

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 3 ай бұрын

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

@takemepeak1608 27 күн бұрын

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?

@zainaltaf4935 3 ай бұрын

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

@ankitbansal6 3 ай бұрын

Coming soon 🔜

@monuoriginal7425 3 ай бұрын

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

@pateldh23 3 ай бұрын

Sir, make video on banking transaction related videos please

@ankitbansal6 3 ай бұрын

Let me know if you have any questions

@pateldh23 3 ай бұрын

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

@nikhilsharma-jj6vd 3 ай бұрын

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

@ankitbansal6 3 ай бұрын

Practice from here Complex SQL Questions for Interview Preparation: kzfaq.info/sun/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

@adityap9223 2 ай бұрын

you didn't put condition for last 7 days

@nishan7122 3 ай бұрын

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 3 ай бұрын

Just before payment page

@nishan7122 3 ай бұрын

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

@ankitbansal6 3 ай бұрын

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

@user-bk4dy6sw2e 3 ай бұрын

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

@ankitbansal6 3 ай бұрын

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 3 ай бұрын

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

@ethyria7685 10 күн бұрын

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;

@monuoriginal7425 3 ай бұрын

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

@ankitbansal6 3 ай бұрын

Electoral bond ? Please give more context

@monuoriginal7425 3 ай бұрын

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