select country,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) as Median from people group by country

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

nice explain...

------ 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;

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;

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

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

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

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

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

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

Hi, l like your work and your explanations. like python boot camp can you do a SQL course for intermediate to advance level

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

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

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

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

;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

SELECT country, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY age) AS median_age FROM people GROUP BY country;

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)

Could you please explain in Ms SQL server

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 )

Thumbnail makes it look like they've got you in prison brother. Perhaps 30 was too many?