Dynamically track co-tags over time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

-- stack(24.04.23)exchange(cotag)series
-- to do: add common baseline to timeseries

DECLARE @past int = ##Years:int?16## -- note: implict + 1 due to window function
DECLARE @norm bit =  ##Normalise:int?0##
DECLARE @char int = ##MinChars:int?0##

DECLARE @hi float = ##Upper:float?1.00##
DECLARE @lo float = ##Lower:float?0.60##

-- setup user variables

DECLARE @tag1 nvarchar(35) = ##Tag1:string?mediawiki-api##
DECLARE @tag2 nvarchar(35) -- = ##Tag2:string?facebook-graph-api##
DECLARE @tag3 nvarchar(35) -- = ##Tag3:string?youtube-api##
DECLARE @tag4 nvarchar(35) -- = ##Tag4:string?google-analytics-api##

-- load tags of interest 

create table #tags (pos int, tag nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS);
insert into #tags (pos, tag) 
values (1, @tag1),
       (2, @tag2),
       (3, @tag3),
       (4, @tag4);

-- retrieve tagIds

create table #target (
  Pos int,TagId int,
  TagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS
);
              
insert into #target (Pos, TagId, TagName)
select top 1 Pos, Id as TagId, TagName 
  from tags left join #tags on #tags.tag = tags.TagName 
  where TagName in (Select tag from #tags)

-- set extent

DECLARE 
  @After smalldatetime, 
  @Today smalldatetime;
  
SET @Today = GETDATE();
SET @After = DATEADD(year, @past * -1, GETDATE()); 

-- run query

with main as (

  SELECT pos, 
  posts.tags as tag,
   CreationDate
    
  FROM #target
   INNER JOIN PostTags ON PostTags.TagId = #target.TagId
   INNER JOIN Posts ON Posts.Id = PostTags.PostId 
     
  
  WHERE Posts.CreationDate > @After
    AND Posts.CreationDate < @Today 
    
),

-- unroll tags to rows

prep as (
  
  select pos,CreationDate,value as tag from main
  CROSS APPLY STRING_SPLIT(replace(replace(replace(tag,'><','|'),'<',''),'>',''), '|')
  where len(value) > @char /*and value not in (
    select top 128 tagName collate SQL_Latin1_General_CP1_CS_AS 
    from tags order by count desc
  )*/

),

-- group by date

freq as (

  SELECT avg(pos) pos, tag,
    DATEADD(year, DATEDIFF(year, 0, CreationDate), 0) AS date,
    COUNT(pos) as mag
    
  from prep p
  left join ( -- anti-join
    select top 128 tagName 
    from tags order by count desc
  ) t on t.tagName = p.tag where t.tagName is null

  GROUP BY tag, /*pos,*/
    DATEADD(year, DATEDIFF(year, 0, CreationDate), 0)
  
),

-- calculate statistics

stat as (
  
  select *,
    (mag)/(sum(mag) over(partition by date)+0.0) pct, 
    count(pos) over (partition by tag) deg 
  from freq f

  
) 

  select date,tag,
   iif(@norm = 1,
     (mag-avg(mag) over(partition by tag))/stdev(mag) over(partition by tag),
   --(pct-avg(pct) over(partition by tag))/stdev(pct) over(partition by tag),
   mag) zScore
  
  from stat 
  where 
    deg between (select max(deg)*@lo from stat) and (select max(deg)*@hi from stat) 
    and date between dateadd(year,2,@after) and dateadd(year,-1,@today)
  order by 
    date,pos,mag desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results