-19

I don't know how to do SEDE queries, but I would like to explore some of the Earth Science Stack Exchange data.

Once I needed a query it was done by user rene, but I have many queries to do on Earth Science Stack Exchange.

I asked ChatGPT free version (GPT-3.5) to do a SEDE query for me and it gave errors.

But I have bought ChatGPT plus (GPT-4) and I asked it to do rene's query for questions and answers in Earth Science and it did with a different code, obtaining the same resulting graph:

WITH MonthlyQuestions AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    COUNT(*) AS NumberOfQuestions
  FROM
    Posts
  WHERE
    PostTypeId = 1 -- Question
  GROUP BY
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
),
MonthlyAnswers AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    COUNT(*) AS NumberOfAnswers
  FROM
    Posts
  WHERE
    PostTypeId = 2 -- Answer
  GROUP BY
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
)
SELECT
  mq.Month,
  mq.NumberOfQuestions,
  ma.NumberOfAnswers
FROM
  MonthlyQuestions mq
LEFT JOIN
  MonthlyAnswers ma ON mq.Month = ma.Month
ORDER BY
  mq.Month

enter image description here

rene's query graph

Then, can GPT-4 be used to explore Stack Exchange sites data?

Has someone with SEDE knowledge checked it?

1
  • 5
    You could always try it - and possibly compare it against a 'known good' query for the same thing. Commented Mar 24, 2023 at 11:33

3 Answers 3

3
+200

Can GPT-4 be used to explore Stack Exchange sites data?

Given this first evidence, yes. It did give you a starting point to start exploring.

Has someone with SEDE knowledge checked it?

I checked the specific query (I'm not going to or are willing to review every single query that AI is going to generate) and it is correct enough with the difference that I prefer to use the built-in EOMONTH function (End of Month) when I want to aggregate rows (Sum, Count, Average) per month where the ChatGPT query settles to determine the first day of the month.

Beyond that it is basic joining of two sets on that Month value.

Technically the query would miss a datapoint when you have a month where no-one asked a question (but answers were posted) because it uses the MonthlyQuestions query as the "main" query. Here is a version that eliminates that flaw but it would only manifest itself on sites like WindowsPhone.se or other no-traffic sites:

WITH MonthlyQuestions AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    COUNT(*) AS NumberOfQuestions
  FROM
    Posts
  WHERE
    PostTypeId = 1 -- Question
  GROUP BY
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
),
MonthlyAnswers AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    COUNT(*) AS NumberOfAnswers
  FROM
    Posts
  WHERE
    PostTypeId = 2 -- Answer
  GROUP BY
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
),
Months as (
  -- collect all distinct Months we have data for
  SELECT 
     Month
  FROM MonthlyQuestions
  UNION
  SELECT 
     Month
  FROM MonthlyAnswers
)
SELECT
  m.Month,
  mq.NumberOfQuestions,
  ma.NumberOfAnswers
FROM
  Months m
LEFT JOIN
  MonthlyQuestions mq ON m.Month = mq.Month
LEFT JOIN
  MonthlyAnswers ma ON m.Month = ma.Month
ORDER BY
  mq.Month

You can always dissect a complex query in smaller sets to examine the data before applying group and/or aggregate functions over it. For example this query with two result sets.

Keep in mind SEDE is updated once a week on Sunday.
Use the very useful SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

1
  • 1
    Bye rene. I am leaving Earth Science too. AI disallowed Earth Science will die as most of Stack Exchange and Stack Overflow sites if AI's superhuman GPT-5 training continues. I came here for coding answers and GPT-4 is doing that task better than humans and their arrogancy. Good luck for you and yours.
    – user1242306
    Commented Apr 6, 2023 at 10:59
12

It obtains a result. Therefore, you can use it to explore data.

Whether you can trust the result to be what you asked for and draw conclusions based upon it is another matter though. I'd be hesitant to use this site (or any other) as a place to just dump whatever GPT comes up with for you, and ask other users to fix it. Instead, spend some time going through the tutorial and other resources mentioned in the tag info, and you would've been able to make a query like the one GPT gave you now yourself and could've saved yourself a bit of money too.

I'd recommend (in order of importance):

  • to stop wasting money by paying GPT to write queries for you,
  • to spend some time to learn some SEDE basics and become capable of getting some basic data out of SEDE,
  • to just use the search functionality on SEDE and here on MSE to find queries for the more complicated stuff that may already answer your questions.
  • If all of the above fails and you can't find a query that gives you the data you want, to just use chat (see the rooms linked in the tag info) or this site to ask a question about how to get the data you want out of SEDE, and get an answer written by an actual human for free, that's pretty much guaranteed to work.
0
3

Well, the argument 'against' ChatGPT is folks 'cynically' posting whatever it spews out without much thought.

To quote Neil Gaiman, in a recent tweet

ChatGPT doesn't give you information. It gives you information-shaped sentences.

With an SEDE query - which you actually run on SEDE and get results, I guess you can check if you get a result. However, if you don't understand the specific dialect of SQL SE uses, or SQL at all, it’s possible you get valid code, but not a valid answer.

I'd as such argue that besides proper attribution, it’s worth citing that you got the code from ChatGPT and you ran it in case the code is syntactically correct (it runs), but not logically correct. (It doesn't actually give you what you think you asked for.)

In addition, it differentiates you from those folks who're just posting whatever that's there - and you're using it as one tool of many. And actual, valid uses with attribution generating valid data would be a refreshing difference from the folks copy pasting wholesale.

You're also going to need to be prepared for the possibility people are annoyed with LLMs in general at this point, and if you get a logically wrong answer, you look stupid or end up with the wrong idea.

I'd also suggest that if you really need to run a bunch of SEDE queries, it’s worth learning, and using ChatGPT as a learning tool, not a crutch to avoid learning. Review its queries, and try adjusting it yourself. Try to get where the divergences from human and generated queries are and so on. Don't rely on a black box to get things done.

You must log in to answer this question.