Ask Ubuntu General Room

« first day (2097 days earlier) ← previous day next day → last day (2862 days later) »

10:23 PM

Anyone familiar with SQL?

I wanted to enter this query into the SE Data Explorer, but it tells me "Incorrect syntax near the keyword GROUP":

SELECT TOP 100
  U.Id as [User Link],
  COUNT(P.Id) as "# of Questions",
  DATEDIFF(DAY, MIN(U.CreationDate), GETDATE()) as "Account age (days)",
  ROUND(
    CAST(COUNT(P.Id) AS FLOAT) /
    NULLIF(CAST(DATEDIFF(DAY, MIN(U.CreationDate), GETDATE()) AS FLOAT), 0),
    3)
    as "Q per day"

FROM Users U
JOIN Posts P on U.Id = P.OwnerUserId

WHERE P.PostTypeId = 1
HAVING COUNT(P.Id) > 10

GROUP BY U.Id
ORDER BY 4 DESC

My aim is to list the top askers of a site, their question count, their account age in days and their average questions per day rate.

^ solved it.

Zacharee1

@ByteCommander semi-colons?

Byte Commander

No. HAVING must be placed right after GROUP BY.

Zacharee1

well that does make sense

Byte Commander

I have probably only ever written about 30 lines of SQL - how the heck should I know?

Zacharee1

group this by X when it matches Y

makes more sense for code to put the ind clause before dep

Byte Commander

10:34 PM

Wut?

Anyway, here are my queries:

Zacharee1

English stuff

Byte Commander

Check out Ask Ubuntu's most active askers and most active answerers on the Stack Exchange Data Explorer!

^ could profit from a star...

I counted all users with more than 10 questions and calculated their questions per day ratio. Same for the answers.

Zacharee1

AB is ahead of Video?

wow

Byte Commander

@ParanoidPanda Is clearly leading the asker ranking. He asked 514 questions so far which means a question rate of 0.995 per day. The second rank has only a rate of 0.419, but that account is only 74 days old.

The second highest number of questions in total is from Luis Alvarado (477 questions over 2028 days --> rate of 0.22 Q/day)

Zacharee1

Michael hasn't reappeared since his suspension

Byte Commander

10:43 PM

Michael?

Should I remember him?

Or be happy that I managed to forget him?

What?

Zacharee1

"you could try this:
`stuff`
and it might work fine this way"

Byte Commander