c# - Hot Topics in 30 days range -

i have on 10k topics in db table, , count page views each topic on session base , store one view/per topic/per user (session time 24 hrs).

id-----topic----------------views 1------love------------------400 2------friends---------------203 3------birthday--------------360 

now want hot topics in last 30 days, means want hot topics on bases of page views in last 30 days. need little direction, on how can achieve this. thanks

you need separate topic table , topicview table if want adapt recent views. current table structure there no idea of how recent view - if have topic spike big-time in week 10 of year, may remain #1 on hot topic list long time (as 'views' column cumulative on all-time).

create table topic (     [id] int not null identity(1,1)     [topic] varchar(255) not null )  create table topicview (     [viewid] int not null identity(1,1),     [topicid] int not null,     [user] varchar(255) not null,     [viewdate] datetime not null ) 

now can check every time user hits page if have logged 'topicview' them. when want see topics hot, execute:

declare @maxresults int = 100  --the maximum number of results show declare @hottopicviewdays int = 30 --how recent want see hot topic activity declare @hottopicviewlimit int = 300 --what amount of views consider hot   select top (@maxresults)     t.[id],     t.[topic],     count(tv.[viewid]) [views] [topic] t     join [topicview] tv     on t.[id] = tv.[topicid] tv.viewdate >= dateadd(day, -(@hottopicviewdays), getdate()) group t.[id],     t.[topic] having count(tv.[viewid]) >= @hottopicviewlimit 

this pretty extensible , allow configure:

1. how many results want return @maxresults
2. how recent views need to factor "hot topic" activity @hottopicviewdays
3. how activity required consider topic "hot" @hottopicviewlimit

let me know if there questions or if sees issue approach.