0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

drop table if exists #tmp;
create table #tmp (
	id int
	, sales int
	, views int
)
;

insert into #tmp (
	id
	, sales
	, views
)
values
	(1, 4, 3)
	, (1, 1, 1)
	, (1, 2, 2)
	, (2, 7, 8)
	, (2, 6, 6)
	, (2, 7, 7)
;

with CacheAvg as (
	select id
    , avg(cast(sales as float)) over(partition by id) as AvgSales
    , sales
    , views
    from #tmp
)

select id
, max(views) as MaxViews
from CacheAvg
where sales > AvgSales
group by id
;

fork query

edited nov 16 anonymous

created nov 15 OwlsSleeping