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 postsfrom posts--This provides stricter criteria for the graph as a wholewhere (title like '%library%'    or title like '%tool%'    or title like '%book%'    or title like '%tutorial%')and closeddate   is nulland deletiondate is null ;
Data Explorer query that makes bar graphs
sql;graph;stackexchange;t sql;data visualization