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

[@LuisHinojosaFalcon]

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

[@petermugo9204]

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

[@Kadavendis]

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

[@kevinwtao5321]

nice explain...

[@sivakumarisadineni3193]

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

[@rohitsethi5696]

@sivakumarisadineni3193 what do you want to about python or ETL

[@sammail96]

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

[@utsavkumar71]

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;

[@muhammadabbas6645]

@utsavkumar71 bro nice solution but didnt understand why did you count as a window function

[@gphanisrinivasful]

This is an interesting approach!

[@gouthamstar6558]

this is the best approach compared to @techtfq but tfq's approch is also good

[@anudeepreddy5559]

🔥

[@rohitsharma-mg7hd]

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

[@amzahaan7232]

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

[@saiswaroop3570]

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

[@chetanmaurya8557]

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

[@rohithb65]

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

[@Alexpudow]

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

[@sourabhkumar6811]

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

[@radhakrishnasabbi4904]

Could you please explain in Ms SQL server

[@NabeelKhan-um1zk]

(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 )

[@rajashekharreddy5794]

;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

[@gowrikm-n5u]

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)

[@mrbartuss1]

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

[@mikefenn00]

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

[@techTFQ]

Haha my wife felt the same 😅
Just trying have some fun

[@shivinmehta7368]

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-um1zk]

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 )