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

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;

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #googleinterview #analytics

[ankitbansal]

This new year learn Python on my LIVE Bootcamp for data analytics (weekend classes).

Use code NY2024 for massive discount.

[vandanaK-mhzo]

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 ;

[.Counting]

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

[ankushjain]

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

[vikramjitsingh]

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"

[enisertem]

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

[nandan]

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

[ashwingupta]

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

[narutogluffy]

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

[xdtbfgd]

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

[tanmaykumar]

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

[dfkgjdflkg]

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

[shanmukhasrinivas]

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

[AmanRaj-ufwx]

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

[montudeb]

Here is my solution by using MYSQL:
with recursive number as (
select 1 as n
union all
select 1 + n
from number where n <= (select max(char_length(content)) from namaste_python)
)
, words as (
select substring_index(substring_index(content, ' ', n), ' ', -1) as word
from namaste_python
join number on char_length(content) - char_length(replace(content, ' ', '')) >= n - 1
)
select word, count(*) as counts
from words
group by word
having count(*) > 1
order by word;

[sukanyaiyer]

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 <= length ( content ) - length ( replace ( content, ' ' ) ) + 1
)) group by string_part
having count(*) > 1

[KoushikT]

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

[oyenitesh]

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

[akshitdadheech]

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

[shaikmahammadshareef]

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