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

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

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

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

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

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

Nice video and insightful ankit

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

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

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?

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

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

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;

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;

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

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

you didn't put condition for last 7 days

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

Sir, make video on banking transaction related videos please

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 .

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

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

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;