select country,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) as Median from people group by country
@sapnasaini851Ай бұрын
------ Another Approach ------ with cte as ( select country, age, row_number() over(partition by country order by age) rn1, row_number() over(partition by country order by age desc) rn2 from people) select country, age from cte where rn1 = rn2 or rn1 +1 = rn2 or rn2+1 = rn1 order by 1, 2;
@LuisHinojosaFalcon27 күн бұрын
thanks for the exercises, doing a great job, and just for the sake of increasing only a hair the difficulty and sticking to the actual definition of median, then the data with even number of rows need to be averaged between the two middle rows of the sorted data, so the median for Germany should be (54 + 6) / 2 = 30, India (33 + 38) / 2 = 35.5 and Poland (34 + 45) / 2 = 39.5
@Kadavendis6 ай бұрын
Hi....another diamond in the treasury.....so far most of the concepts are covered....but stored procedure based queries are not covered ....is there any specific reason behind that ......please try to cover if it is just a miss
@utsavkumar716 ай бұрын
with cte as( select *, abs(ROW_NUMBER() over(partition by country order by age) - ROW_NUMBER() over(partition by country order by age desc)) as rn_desc, count(*) over(partition by country order by age range between unbounded preceding and unbounded following) as cnt from people) select country, age as median from cte where rn_desc = 1 or rn_desc = 0;
@muhammadabbas66456 ай бұрын
@utsavkumar71 bro nice solution but didnt understand why did you count as a window function
@gphanisrinivasful5 ай бұрын
This is an interesting approach!
@gouthamstar65585 ай бұрын
this is the best approach compared to @techtfq but tfq's approch is also good
@amzahaan72326 ай бұрын
select * from (select *, row_number() over(partition by country order by age) as rn , count(1) over(partition by country) as ct from people)x where rn between ct*1.0/2 and (ct*1.0/2)+1
@sammail966 ай бұрын
If it was not the problem of age then median : for even records avg of n(no_of_records)/2 and (n/2 + 1)th record , for odd records ((n+1)/2)th record
@kevinwtao5321Ай бұрын
nice explain...
@rohitsharma-mg7hd3 ай бұрын
with cte as ( select *,row_number() over(partition by country order by age),count(age) over(partition by country ) as cnt,(count(age) over(partition by country ))/2 as ind from people ) ,cte_odd as (select * from cte where (id,row_number) in (select id,ind+1 from cte) and cnt%2!=0) -- (select *,case when cnt%2=0 then 'even' else 'odd' end as cs from cte -- where cnt%2!=0) ,cte_even as ( select * from cte where (id,row_number) in (select id,ind+1 from cte) and cnt%2=0 union all (select * from cte where (id,row_number) in (select id,ind from cte) and cnt%2=0) ) select country,age from cte_even a union all select country,age from cte_odd b order by country,age done it with union
@sivakumarisadineni31936 ай бұрын
Hi, l like your work and your explanations. like python boot camp can you do a SQL course for intermediate to advance level
@rohitsethi56965 ай бұрын
@sivakumarisadineni3193 what do you want to about python or ETL
@chetanmaurya85574 ай бұрын
with cte as( select country,age,row_number() over(partition by country order by country,age desc) as rn from people), cte2 as (select *,cast(FIRST_VALUE(rn) over(partition by country order by country,age) as decimal) as cnt from cte) select country,age from cte2 where rn>=cnt/2 and rn
@saiswaroop35706 ай бұрын
with result_set as ( select country,age,rnk,total_count,case when rnk=total_count/2 or rnk=trunc((total_count/2))+1 then 1 else 0 end as flag from ( select country,age,dense_rank()over(partition by country order by age) as rnk, count(id)over(partition by country order by id range between unbounded preceding and unbounded following) as total_count from people ) ) select country,age,rnk as median_position,total_count as total_values from result_set where flag=1
@rohithb656 ай бұрын
with cte as ( select *, (total/2) as t1 , (total/2 + 1) as t2 from (select * , row_number() over(partition by country order by age) rn , count(*) over(partition by country) as total from people order by country,age)x) select country,age from cte where rn between t1 and t2 order by country
@Alexpudow6 ай бұрын
ms sql solution with a as ( select * ,ROW_NUMBER() over(partition by country order by age)*1.0 rn from people), b as ( select country ,case when max(rn) % 2 0 then (max(rn)+1)/ 2 else floor((max(rn) +1)*1.0 / 2) end mrn ,case when max(rn) % 2 0 then (max(rn)+1)/ 2 else ceiling((max(rn) +1)*1.0 / 2) end mrn2 from a group by country) select a.country, a.age from a join b on a.country=b.country and rn between mrn and mrn2
@sourabhkumar68116 ай бұрын
select country,age from (select A.*, total_values/2*1.0 as first_val, (total_values/2*1.0)+1 as second_val, case when rn>=(total_values/2*1.0) and rn
@rajashekharreddy57946 ай бұрын
;with cte as ( select *, case when cnt % 2=0 and rn = cnt/2 then rn when cnt%2 = 0 and rn = cnt/2+1 then rn when cnt%20 and rn = cnt/2+1 then rn end [flag] from ( select * ,ROW_NUMBER() over (partition by country order by age) rn ,COUNT(country) over (partition by country order by age range between unbounded preceding and unbounded following) cnt from people_median_age ) a ) select id,country,age [median_age] from cte where flag is not null
@radhakrishnasabbi49046 ай бұрын
Could you please explain in Ms SQL server
@NabeelKhan-um1zk5 ай бұрын
(with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "INDIA") select * from an where ranks= total/2 OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "poland") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "USA") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "Germany") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "japan") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 )
@mrbartuss16 ай бұрын
SELECT country, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY age) AS median_age FROM people GROUP BY country;
@gowrikm-n5u6 ай бұрын
select country , age from ( select * ,row_number() over( partition by country order by age) as r, count(id) over(partition by country order by age range between unbounded preceding and unbounded following) as cnt from people ) x where r in ((cnt + 1) / 2, (cnt + 2) / 2)
@shivinmehta73684 ай бұрын
with cte as (select *,row_number() over(partition by country order by age asc) as rn, count(id) over(partition by country) as cnt from people ) select country,age from( select *, case when cnt%2=1 then (cnt+1)/2 when cnt%2=0 then round(((cnt/2) +(cnt/2+1))*1.00/2,1) end as flag from cte order by 1 ) x where rn between floor(flag) and ceil(flag) order by 1,2
@NabeelKhan-um1zk5 ай бұрын
for mySQL (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "INDIA") select * from an where ranks= total/2 OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "poland") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "USA") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "Germany") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 ) union all (with an as (SELECT * , row_number() over(partition by country order by age ) as ranks , count(country) over(partition by country) as total from people where country= "japan") select * from an where ranks= ceiling(total/2) OR ranks= total/2+1 )
@mikefenn006 ай бұрын
Thumbnail makes it look like they've got you in prison brother. Perhaps 30 was too many?
@techTFQ6 ай бұрын
Haha my wife felt the same 😅 Just trying have some fun