6

I've been wondering if there was a way to graph something in SEDE other than a line graph, because line graphs are boring. I figured out how to do a scatter plot, but it's still not as cool as a bar graph would be.

There's no built-in way to do this. What code would work to emulate a bar graph?

(Note: I'm self-answering with my own solution. I found a trick which, although flawed, gives cool looking results. I just wanted to share with everyone so that you can make cool graphs too.)

9
  • 1
    FWIW, adding more powerful graphing support is on the todo list, so I'd be interested in hearing different use cases and suggested ways of picking options
    – Tim Stone
    May 2, 2016 at 21:06
  • @TimStone Is there a place where they announced that? (I didn't see anything when I searched earlier...)
    – Laurel
    May 2, 2016 at 21:10
  • @PatrickHofman I was pretty sure of that. I was just curious if there's a list of what they plan to add. (I made at least two suggestions on how they could improve it, one being zoomable graphs, and the other being a way to hide the label.)
    – Laurel
    May 2, 2016 at 21:28
  • @PatrickHofman Unfortunately, no. Tim is the primary maintainer for the Data Explorer, though.
    – Adam Lear StaffMod
    May 2, 2016 at 21:29
  • @PatrickHofman There's no hiring/employment involved.
    – Adam Lear StaffMod
    May 2, 2016 at 21:32
  • So what is the relationship of him with SE and SEDE? May 2, 2016 at 21:33
  • 1
    @PatrickHofman He does a lot of work on SEDE (it's just an open source project) and we really really appreciate it.
    – Adam Lear StaffMod
    May 2, 2016 at 21:43
  • @AdamLear Who programmed the UI for the website? Is it part of that project?
    – Laurel
    May 2, 2016 at 22:07
  • @Laurel Yeah, the UI is part of it. As for who wrote it... it's been a joint effort over many years. :)
    – Adam Lear StaffMod
    May 2, 2016 at 22:16

1 Answer 1

2

I found a solution that creates a bar graph like this:

Bar Graph

(The data is from Super User.)

I think it's really cool, and it's a good way to present visual information from searches. It's technically just a really cool version of a scatter plot, because each dot is a point.

You can remove the label by hacking the HTML. Simply delete <div class="legend"> (and all the HTML inside that node).

This is the code I used (it finds and sorts possible recommendation questions):

Select top 500
(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
), 
left(title,25) --as [Post Link]

,ROW_NUMBER() OVER (

  PARTITION 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
  )
  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

from posts

where (title like '%library%'
   or title like '%tool%'
   or title like '%book%'
   or title like '%tutorial%')
and closeddate is null
and deletiondate is null

Note: This post has been updated with a more perfect bar graph method (although the code is more redundant now). This is the original graph and this is the original query.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .