Tricky SQL Interview Question by a Product Based Company | Ludo King SQL Analytics
HTML-код
- Опубликовано: 16 окт 2024
- 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
1000 likes on this video and I will share the rest of the 2 questions from Gameberry. All the questions are really good 😊.
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....
@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!
@ankitbansal6 Videos are not accessible in your courses. Can you please look into?
Just wrote a zillion lines of code and came again to see your approach and am stunned!! you are the best.
😊
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;
You are right. I will pin your comment for others benefit 😊
@@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!
You are best in clarity of explanation,thanks
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
Its a very good helper question to practice conditional aggregations. Thanks for it.
omg!! i wrote separate query for each questions and here you did all 4 in 1 shot!!
mind blowing :)
😊
I would request you to add sql 100 challenge course for those who have already taken your python and sql course recorded session.
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
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!!
Nice video and insightful ankit
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?
I am waiting, can you share more videos on aws and end to end data engineering projects
Coming soon 🔜
U r champion 🏆
Hi Ankit can you please make some videos on Tableau....
Hello Sir. I have my interview for Amazon BI Engineer role next week . Can you guide how to prepare for SQL?
Practice from here
Complex SQL Questions for Interview Preparation: ruclips.net/p/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb
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
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am begginer also ....
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
Just before payment page
@@ankitbansal6 I tried but it is directly redirecting it to payment option
@@nishan7122 www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
Thanks
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
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;
Is not indicated to hardcore values like you do in your solution-hardcoding the user_id
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 RUclips series .
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 😊
@@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.
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
Sir, make video on banking transaction related videos please
Let me know if you have any questions
@@ankitbansal6 Like balance, received payment, cash withdrawal, interest rate etc
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;
you didn't put condition for last 7 days
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
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....😮💨😮💨😮💨😮💨
Electoral bond ? Please give more context
@@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
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;