SQL Interview Question Asked in Google for Data Analyst Position

No video

SQL Interview Question Asked in Google for Data Analyst Position | Data Analytics

Рет қаралды 25,579

Күн бұрын

In this video we will discuss a SQL question asked in Google interview for a data analyst position. Here is the script :
create table namaste_python (
file_name varchar(25),
content varchar(200)
);
delete from namaste_python;
insert into namaste_python values ('python bootcamp1.txt','python for data analytics 0 to hero bootcamp starting on Jan 6th')
,('python bootcamp2.txt','classes will be held on weekends from 11am to 1 pm for 5-6 weeks')
,('python bootcamp3.txt','use code NY2024 to get 33 percent off. You can register from namaste sql website. Link in pinned comment')
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 #googleinterview #analytics

Пікірлер: 65

@ankitbansal6 8 ай бұрын

This new year learn Python on my LIVE Bootcamp for data analytics (weekend classes). www.namastesql.com/courses/Namaste-Python-Zero-to-hero---Jan-654f1c5fe4b0a57624c36e31 Use code NY2024 for massive discount.

@vandanaK-mh9zo 8 ай бұрын

I love your content. Usually, first I try to solve ques without watching your solution. My approach(in Snowflake): select a.value as words, count(1) as cnt From namaste_python, lateral split_to_table(content, ' ') as a group by words having cnt>1 ;

@27.Counting 8 ай бұрын

First YT video for 2024. Hopefully a good year ahead. ❤

@ankushjain4128 8 ай бұрын

I love your content usually first i try to solve question without watch your solution. select value,count(*) as cnt from string_split((select STRING_AGG(content,' ') from namaste_python),' ') group by value Having count(*)>1 order by cnt desc

@nandan7755 8 ай бұрын

Really Ankit bhaiya I learn a lot from your video day by day ❤❤❤ great video . 💯

@vikramjitsingh6769 8 ай бұрын

In MYSQL we can do using recursion ,sample code I wrote which works - "with recursive cte as ((select * from (select content, LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) +1 as x from words )y) union all select content , x-1 from cte where x-1> 0) select substring_index((substring_index(content,' ',x)),' ',-1) as word, count(1) from cte group by word order by count(1) desc"

@anirvansen2941 8 ай бұрын

I was looking for this solution , thanks

@pratik903 3 ай бұрын

Amazing solution, thank you

@enisertem9738 8 ай бұрын

with base as (select file_name , content , unnest(string_to_array(content,' ')) strr from ctn) select strr,count(1) from base group by 1 having count(1)>1

@tanmaykumar3250 8 ай бұрын

Glad you took my problem 😄 Thank-you for the solution

@ankitbansal6 8 ай бұрын

Happy to help

@narutogluffy2272 8 ай бұрын

It will be great if u even explain this for Mysql also. Thank you

@ankitbansal6 8 ай бұрын

Noted

@dfkgjdflkg 8 ай бұрын

Hi, thanks you for your videos. They are very inspiring.

@shanmukhasrinivas9473 5 ай бұрын

Thank you Ankit..It looks easy by the way you explain things

@oyenitesh727 7 ай бұрын

Absolute great🙏, use count(1) to improve cost

@DEwithDhairy 8 ай бұрын

PySpark Approach and Solution Explanatio video for this problem : kzfaq.info/get/bejne/j6d6ocVq38_bfKM.html

@ashwingupta4765 3 ай бұрын

with cte as ( select file_name , value as noofoccurence from namaste_python cross apply string_split(content,' ') ) select noofoccurence , count(noofoccurence) as counts from cte group by noofoccurence having count(noofoccurence) > 1 order by noofoccurence desc

@user-xd1tb6fg2d 8 ай бұрын

In snowflake with cte as (select file_name,f.value::varchar as word from namaste_python ,table(flatten(input=>split(content,' '))) f) select word,count(*) cnt from cte group by word having count(*) >1

@medleyworld5549 8 ай бұрын

Hi Ankit, thanks a heap for the wonderful SQL problems :)

@shaikmahammadshareef2806 8 ай бұрын

thanks ankit previous also we have this type of sollution for airbnb problem i got it before you explain the sollution

@vishwassharma3312 4 ай бұрын

Kindly make a separate video on cross apply

@KoushikT 8 ай бұрын

************* Solution in PostgreSQL ************* select words, count(*) as word_count from ( select *, unnest( string_to_array(content, ' ') ) as words from namaste_python ) A group by words having count(file_name) > 1 order by 2 desc

@sukanyaiyer2671 8 ай бұрын

select string_part,count(string_part) from( select file_name,regexp_substr(content,'[^ ]+',1,rn) as string_part from namaste_python cross join lateral(select level rn from dual connect by level 1

@AmanRaj-uf7wx 6 ай бұрын

with recursive cte as ((select * from (select content, length(content) - length(replace(content, ' ', '')) +1 as x from namaste_python) as y) union all select content , x-1 from cte where x-1 >0 ) select substring_index((substring_index(content, ' ', x)),' ', -1) as word, count(1) from cte group by word having count(1) > 1 order by count(1) desc

@akshitdadheech9870 8 ай бұрын

Hey, can you tell us how we can achieve the same in MySQL because there is no such CROSS APPLY function in MYSQL.

@ankitbansal6 8 ай бұрын

Wait for the community post

@Tusharchitrakar 8 ай бұрын

Yes since I use MySQL i had this similar issue earlier, and had to use stored procedure to store strings seperately in a temporary table using the locate and mid functions since string_split does not exist in MySQL too (this is a more programmatic and less SQL based approach). Kindly tell us easier strategies.

@akshayb451 8 ай бұрын

Split part in recursive function

@vikramjitsingh6769 8 ай бұрын

@info781 8 ай бұрын

Who uses SQL server?

@shristisrivastava1324 8 ай бұрын

I know all the queries of my sql but after seeing questions i don't understand which query i have to apply on what type of questions. So if there is any hint about this then please make a videoir write it here I am asking that how we will know that we have to solve that particular query by left, right,inner join and soo on .

@montudeb8060 8 ай бұрын

Here is my solution by using MYSQL: with recursive number as ( select 1 as n union all select 1 + n from number where n = n - 1 ) select word, count(*) as counts from words group by word having count(*) > 1 order by word;

@mcmurder8835 8 ай бұрын

Cross apply and string split aren't ANSI SQL standard, they're specific to T-SQL. Fun question, but seems a bizarre scenario for a Google interview scenario.

@27.Counting 8 ай бұрын

You can use other method to solve this question. No need to go with cross apply. You can use lateral split_to_table also

@milindzuge906 8 ай бұрын

Thanks Ankit for sharing

@ankitbansal6 8 ай бұрын

My pleasure

@definitesquare 10 күн бұрын

Nice

@shraddhadhakad1154 8 ай бұрын

select value as word , COUNT(*) as 'cnt_of_word' from [dbo].[namaste_python] cross apply string_split(content, ' ') group by value having COUNT(*)>1 order by cnt_of_word desc;

@ishusingh9361 4 ай бұрын

I think this solution won't work for e.g; if the 'to' word is present 3 times in in single row or on mutliple row

@Ajay-rd1oi 8 ай бұрын

Bro keep daily such kind of problems plz , plz tell how practise gooogle microsoft apple netflix sql questions were its available how can practise

@Thekingslayer-ig5se 8 ай бұрын

Great video bro.

@ankitbansal6 8 ай бұрын

Glad you enjoyed

@MonkeyDLuffy4885 3 күн бұрын

WITH words AS ( SELECT LOWER(REGEXP_SPLIT_TO_TABLE(content, '\s+')) AS word FROM namaste_python ), word_count AS ( SELECT word, COUNT(*) AS occurrences FROM words GROUP BY word ) SELECT word, occurrences FROM word_count WHERE occurrences > 1 ORDER BY occurrences DESC;