Data Explorer query that makes bar graphs

Question

I wrote a query for SEDE that I think is pretty cool. It uses the currently available graphing capabilities (scatter plots) to create a bar graph. I have used it as the subject for a self-answer on MSE.

This query finds potential "recommendation questions" based on the words in the title.

The graph looks like this when I ran the query for Super User:

_{Side note: they are planning to expand the graphing capabilities in the future. Right now they're very limited, so I had to work within the limitations of the system. I'm also aware that my bar graph looks a lot different from the "average" bar graph, but I'm ok with it.}

My code right now isn't very long or complex because I'm just starting to teach myself SQL (mostly to make SEDE queries).

The main things I want to know are:

• Can I refactor it so that the list of when like then statements occurs only once? (Preferably without complicating the code too much.)

  Right now, the first time, it assigns the number for the X axis. The second time, it orders it so that all of the points for each bar are continuous.

• Are there any optimizations I can make? So far, I've been using top # statements to keep things from taking too long.

I have prettied up my code for review, but most of the comments are from when I posted it to MSE. Because of the large number of "non-programmers" there (and people without SQL experience), I included a lot of details that are superfluous if you know the basics of SQL (ignore it if you want).

--This limits the results to the first 500
--It's sorting by the bar number, 
-- so the query may get cut off like this one,
-- which has 4 bars instead of 6.
select top 500

--Here is the X axis. 
--Each number is the bar that the result will be put in.
(case 
    when lower(title) like '%open source%' then 0
    when lower(title) like '%recommend%'   then 1    
    when lower(title) like '%free%'        then 2
    when lower(title) like '%good%'        then 2
    when lower(title) like '%best%'        then 2
    when lower(title) like '%better%'      then 2
    when lower(title) like '%any%'         then 2
    when lower(title) like '%what%should%' then 3
    when lower(title) like '%which%should%'then 3
    when lower(title) like '%is there%'    then 3
    when lower(title) like '%looking for%' then 4
    else 5 end
) 

--This is the first 25 characters of the title
,left(title,25)

--Here is the Y axis. It sorts by the X axis,
-- and then just numbers upwards
,row_number() over (order by 
  (case 
    when lower(title) like '%open source%' then 0
    when lower(title) like '%recommend%'   then 1    
    when lower(title) like '%free%'        then 2
    when lower(title) like '%good%'        then 2
    when lower(title) like '%best%'        then 2
    when lower(title) like '%better%'      then 2
    when lower(title) like '%any%'         then 2
    when lower(title) like '%what%should%' then 3
    when lower(title) like '%which%should%'then 3
    when lower(title) like '%is there%'    then 3
    when lower(title) like '%looking for%' then 4
    else 5 end
  )
) as Number

--This query searches posts
from posts

--This provides stricter criteria for the graph as a whole
where (title like '%library%'
    or title like '%tool%'
    or title like '%book%'
    or title like '%tutorial%')
and closeddate   is null
and deletiondate is null ;

Answer 1

I'll answer your main questions:

• Can I refactor it so that the list of when like then statements occurs only once? (Preferably without complicating the code too much.)

Yes, but not at the expense of complicating the code. In SEDE we unfortunately don't have the permission needed to create user-defined functions.

I have introduced two concepts. One is a string with the comma separated words to select the posts your interested in. This string can then be STRING_SPLIT and each value gets transformed in the right hand side of the LIKE expression.

The other concept is a temporary table that holds the phrase and a bar value. That allows for a join on Posts.Title with a LIKE expression to add the bar value column to a title from the posts row.

By introducing common table expressions the functional steps to obtain the right sets can be broken up, making the final projection a breeze.

• Are there any optimizations I can make? So far, I've been using top # statements to keep things from taking too long.

On Super User this doesn't run too long, the execution plan looks like this:

and I can't think of any optimizations within the limited options we have within SEDE. I did add an extra where clause when selecting from the Posts table to make sure only questions are queried, as they only have titles.

Without further ado here is my optimized version of the query

-- selection of titles, comma separated 
declare @selection nvarchar(250) = 'library,tool,book,tutorial'

-- table to hold the phrases to go in each bar
create table #titles (phrase nvarchar(50), value int);
-- add the phrase and which bar
insert into #titles values ('open source',0)
insert into #titles values ('recommend',1)   
insert into #titles values ('free',2)
insert into #titles values ('good',2)
insert into #titles values ('best',2)
insert into #titles values ('better',2)
insert into #titles values ('any',2)
insert into #titles values ('what%should',3)
insert into #titles values ('which%should', 3)
insert into #titles values ('is there',3)
insert into #titles values ('looking for',4)

-- the Common Table Expressions to make our life easier
;with 
-- the questions to be considered from the posts table
questions as (
  select distinct 
         id
       , title
  from posts
  inner join string_split(@selection,',') -- gives us a table
     -- do a case insensitive like with a proper collate
     on title like concat('%',value ,'%') collate SQL_Latin1_General_CP1_CI_AS
  where posttypeid = 1 -- only questions (this reduces the set drastically)
  and closeddate is null
  and deletiondate is null
),
-- for each qualifying row add the bars to with it
titlecategories as (
  select distinct id
       , title
       , coalesce(
           value, 
           (select max(value) + 1 from #titles) -- title has no matching value
         ) value
  from questions q 
  left outer join #titles t 
    -- do a case insensitive like
    on title like concat('%', t.phrase ,'%') collate SQL_Latin1_General_CP1_CI_AS
),
-- make sure each post only appears once
categories as (
  select id
       , min(title) title
       , min(value) value -- lower is better is idea ....
  from titlecategories
  group by id
)

-- final projection, partition and order by over value
select top 1500 
       value
     , left(title, 25) title
     , ROW_NUMBER() OVER (
        PARTITION BY(value)
        ORDER BY (value)
       ) AS Number
from categories
order by value
       , Number

I did notice that if I leave out the top statement, I get some glitches in the graph but I'm unable to pinpoint what is causing that. For now I write that off on the used library on the Stack Exchange Data Explorer.