3
\$\begingroup\$

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:

Bar graph

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 ;
\$\endgroup\$
  • \$\begingroup\$ Because of the large number of "non-programmers" there On MSO/MSE? What makes you think that? \$\endgroup\$ – Mast May 4 '16 at 17:03
  • \$\begingroup\$ @Mast Oops. I meant MSE, but I fixed it. (MSE, as the network wide meta, does have a lot of programmers, but it also has plenty of people from other sites.) \$\endgroup\$ – Laurel May 4 '16 at 17:07
  • \$\begingroup\$ Does this query actually finish? It always times out when I try to run it \$\endgroup\$ – Dannnno Aug 29 at 22:29
  • \$\begingroup\$ @Dannnno It still works for me when I run it on Super User’s dataset. I have the top 500 part there because it will not finish otherwise, which you could decrease further if it’s not working for you. \$\endgroup\$ – Laurel Aug 29 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.