SQL Interview Questions & Answer - Part 59 | Google SQL Question

Подписаться 13 тыс.

Просмотров 2,3 тыс.

50% 1

SQL Interview Questions & Answer - Part 59 | Google SQL Question | Median Google Search Frequency 🔥🔥
Google's marketing team is making a Superbowl commercial and needs a simple statistic to put on their TV ad: the median number of searches a person made last year.
However, at Google scale, querying the 2 trillion searches is too costly. Luckily, you have access to the summary table which tells you the number of searches made last year and how many Google users fall into that bucket.
Write a query to report the median of searches made by a user. Round the median to one decimal point.
-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
🔴 Instagram :
/ itjunction4all
🔴 Twitter:
/ sunilkr5672
-------------------------------------------------------------------------
🔴 Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table search_frequency (
searches int,
num_users int
)
Insert into search_frequency Values(1,2)
Insert into search_frequency Values(4,1)
Insert into search_frequency Values(2,2)
Insert into search_frequency Values(3,3)
Insert into search_frequency Values(6,1)
Insert into search_frequency Values(5,3)
Insert into search_frequency Values(7,2)
#GoogleDataEngineer #GoogleInterview #GoogleSQLInterview #FAANG #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #ITJunction4all

Опубликовано:

30 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 10

@premanandramesh8969 Месяц назад

As always, great question from Sunil. My approach without using the inbuilt percentile_cont function:- Declare @total int; Set @total = (Select sum(num_users) from search_frequency) ; with cte_rec_median as ( Select searches, num_users, 1 as step from search_frequency union all select searches, num_users, 1+step from cte_rec_median where step < num_users ) Select CASE WHEN @total%2 = 0 THEN CAST(sum(CAST(searches as Decimal(38,1))/2) as Decimal(38,1)) ELSE sum(searches) END as median from ( Select searches, CASE WHEN @total%2 = 0 THEN CASE WHEN rn = @total/2 or rn = @total/2+1 THEN 1 ELSE 0 END ELSE CASE WHEN rn = @total/2+1 THEN 1 ELSE 0 END END as Indicator from ( Select searches, num_users, step, row_number() over(order by searches, num_users) as rn from cte_rec_median )sub )sub2 where Indicator = 1

@arunkumarn8710 Год назад

with cte as (select searches, num_users, 1 as temp from search_frequency union all select searches, num_users, temp +1 from cte where temp+1

@reachrishav Год назад

Another approach. SELECT AVG(1.*searches) median FROM ( SELECT searches, row_number() over(order by searches) rn, count(*) over() cnt FROM search_frequency f CROSS APPLY GENERATE_SERIES(1, f.num_users) ) x WHERE rn BETWEEN 1.*cnt/2 AND 1.*cnt/2 + 1

@akashgoel601 Год назад

thanks for this, can you consider posting videos in 720p or more.. this resolution is too old now.

@ItJunction4all Год назад

Hi Akash, I have made this video in 4k pixel. Probably you will need to change the settings . You will have to go to settings and then Advance and then choose the pixel. Currently I can see 2160 p is available to watch.

@akashgoel601 Год назад

@@ItJunction4all thanks for that, I am not sure what happened before while I was trying to access, it was just showing 360p as an option. Looks good now.

@ItJunction4all Год назад

Ok . Np Akash :)

@akashgoel601 Год назад

@@ItJunction4all anyways missed your new video! 😊 Thanks for posting one now

@renganathanmutthiah9150 Год назад

Awesome Sunil!! Below is another solution... with temp1 as /* holds exact median value of the sum is odd; else holds two values whose avg is median */ (select * from ( select case when sum(num_users)%2 =0 then sum(num_users)/2 end as col from search_frequency union select case when sum(num_users)%2 =0 then (sum(num_users)/2)+1 end as col from search_frequency union select case when sum(num_users)%2 =1 then sum(num_users)/2 end as col from search_frequency ) as a where col is not null ) ,temp2 as /* calcs running total of num_users and a lag of prev running total */ (select searches ,num_users ,cumulative_sum ,COALESCE(lag(cumulative_sum,1) over(),-1) as prev_cumulative_sum from ( select searches ,num_users ,sum(num_users) over(order by searches rows between unbounded preceding and current row) as cumulative_sum from search_frequency order by searches ) as a ) select round(avg(searches),1) as median from ( select searches ,case when ( (select min(col) from temp1) between prev_cumulative_sum+1 and cumulative_sum OR (select max(col) from temp1) between prev_cumulative_sum+1 and cumulative_sum ) then 'Y' else 'N' end as choose from temp2 ) A where choose = 'Y'

@ItJunction4all Год назад

Thanks Renga for your query. I just went through your query and executed. It threw error. Looks like you have used Order by clause in subquery which is not allowed. Another issue is that you have kept OVER clause empty in lag function where ORDER BY clause is mandatory. Let me know if I missed to understand your code.