1000 likes on this video and I will share the rest of the 2 questions from Gameberry. All the questions are really good 😊.
@monuoriginal74255 ай бұрын
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....
@UnrealAdi5 ай бұрын
@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!
@radhikajujjavarapu69715 ай бұрын
@ankitbansal6 Videos are not accessible in your courses. Can you please look into?
@aniketpandey40845 ай бұрын
Just wrote a zillion lines of code and came again to see your approach and am stunned!! you are the best.
@ankitbansal65 ай бұрын
😊
@Tusharchitrakar5 ай бұрын
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;
@ankitbansal65 ай бұрын
You are right. I will pin your comment for others benefit 😊
@Tusharchitrakar5 ай бұрын
@@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!
@akashjha72774 ай бұрын
You are best in clarity of explanation,thanks
@mohdtoufique37865 ай бұрын
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
@tanmaymodi82844 күн бұрын
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
@rockingsurya49935 ай бұрын
Its a very good helper question to practice conditional aggregations. Thanks for it.
@hii.23065 ай бұрын
I would request you to add sql 100 challenge course for those who have already taken your python and sql course recorded session.
@rahulmehla20149 күн бұрын
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
@zainaltaf49355 ай бұрын
Nice video and insightful ankit
@MukeshYadav-wb5uo3 ай бұрын
Thanks
@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
@zainaltaf49355 ай бұрын
I am waiting, can you share more videos on aws and end to end data engineering projects
@ankitbansal65 ай бұрын
Coming soon 🔜
@takemepeak16082 ай бұрын
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?
@Satish_____Sharma4 ай бұрын
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
@edumail101624 күн бұрын
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
@KoushikT5 ай бұрын
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;
@kedarwalavalkar68615 ай бұрын
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;
@florincopaci68215 ай бұрын
Is not indicated to hardcore values like you do in your solution-hardcoding the user_id
@nikhilsharma-jj6vd5 ай бұрын
Hello Sir. I have my interview for Amazon BI Engineer role next week . Can you guide how to prepare for SQL?
@ankitbansal65 ай бұрын
Practice from here Complex SQL Questions for Interview Preparation: kzfaq.info/sun/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb
@shubhammishra22255 ай бұрын
Hi Ankit can you please make some videos on Tableau....
@adityap92233 ай бұрын
you didn't put condition for last 7 days
@monuoriginal74255 ай бұрын
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am begginer also ....
@pateldh235 ай бұрын
Sir, make video on banking transaction related videos please
@ankitbansal65 ай бұрын
Let me know if you have any questions
@pateldh235 ай бұрын
@@ankitbansal6 Like balance, received payment, cash withdrawal, interest rate etc
@user-bk4dy6sw2e5 ай бұрын
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 .
@ankitbansal65 ай бұрын
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-bk4dy6sw2e5 ай бұрын
@@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.
@nishan71225 ай бұрын
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
@ankitbansal65 ай бұрын
Just before payment page
@nishan71225 ай бұрын
@@ankitbansal6 I tried but it is directly redirecting it to payment option
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....😮💨😮💨😮💨😮💨
@ankitbansal65 ай бұрын
Electoral bond ? Please give more context
@monuoriginal74255 ай бұрын
@@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;