# Query for stddev of number of stars per chatroom message

I am learning SQL today, so I decided to practice by writing some queries for The Nineteenth Byte Data Explorer. This is a PostgreSQL database about messages from The Nineteenth Byte chatroom, created by El'endia Starman. The schema can be found at the bottom of the page.

The code functions correctly (as far as I can tell), but it could surely look better. In particular, I'm interested in knowing more elegant ways to store the result of an expression to be used later in the query. Right now, I'm creating a table containing one value using a WITH clause and then SELECTing * from the table each time I want to reference the number.

The purpose of the code is to, given an SE chat user ID, find the mean and standard deviation of the number of stars on messages posted by that user, among those which have at least one star.

The code can be tested here.

WITH SE_Chat_ID_to_search AS ( SELECT
AS VAL),

internal_id as (
SELECT max(id)
FROM "transcriptAnalyzer_user"
WHERE uid = (SELECT * from SE_Chat_ID_to_search)
),

stats as (
SELECT AVG(stars) as mean, COUNT(*) as num_starred
FROM "transcriptAnalyzer_message"
WHERE user_id = (SELECT * FROM internal_id) AND stars > 0
),

sum_diff_squared as (
SELECT sum(power((SELECT mean from stats) - stars, 2.)) as sds
FROM "transcriptAnalyzer_message"
WHERE user_id = (SELECT * FROM internal_id) AND stars > 0
)

SELECT mean, power(sds / num_starred, 0.5) as stddev from stats, sum_diff_squared

• Btw, PostgreSQL has builtin STDDEV_POP & STDDEV_SAMP aggregate functions. Apr 14, 2017 at 12:00

To answer your primary question directly, in certain SQL engines (for instance, Microsoft SQL Server and MySQL) you can declare query variables. Using plain SQL with Postgres, that is not possible.

You would either need to use plpgsql, which I'm pretty sure can't be done on 19B Data Explorer, or, using plain SQL, use WITH table expressions as you have.

Granted, it's not pretty, but otherwise works fine.

For a start, I would recommend to copy your query into a SQL formatter to format the code more appropriately; this makes it easier to then improve the code.

When using WITH tables, naming things goes a long way. I would first give this value a name (and remove the comment, please, or write a useful one):

WITH SE_Chat_ID_to_search
AS (SELECT
--Enter the user's SE chat user ID here:
169713 AS id),


Which makes it easier to reference later and avoid doing SELECT * FROM SE_Chat_ID_to_search. I would also give a name such as uid to the value obtained within internal_id expression, for the same reason:

 internal_id
AS (SELECT Max(id) AS uid
FROM   "transcriptAnalyzer_user"
WHERE  uid = (SELECT id
FROM   SE_Chat_ID_to_search)),


I would also carry that user ID into your other WITH table expressions, so that they can be joined properly (more on that in a moment), which will require the addition of a GROUP BY clause (or perhaps an aggregate function on it such as MAX(), same result though intention is less clear):

 stats
AS (SELECT user_id,
Avg(stars) AS mean,
Count(*)   AS num_starred
FROM   "transcriptAnalyzer_message"
WHERE  user_id = (SELECT uid
FROM   internal_id)
AND stars > 0
GROUP  BY user_id),
sum_diff_squared
AS (SELECT user_id,
Sum(Power((SELECT mean
FROM   stats) - stars, 2.)) AS sds
FROM   "transcriptAnalyzer_message"
WHERE  user_id = (SELECT uid
FROM   internal_id)
AND stars > 0
GROUP  BY user_id)


That brings us to the query which brings those tables together into the result set:

SELECT mean,
power(sds / num_starred, 0.5) as stddev
from stats,
sum_diff_squared


I mentioned joining tables properly. What you did above is an implicit join without a join condition, which effectively results in a Cartesian join (i.e. a Cartesian product of all rows of each table).

In your case, the impact was not felt because both tables return a single row; however, in reality, a Cartesian join is not only extremely slow, but more often also, unless done with careful intent, wrong.

There are 2 ways we can change this to a proper, non-Cartesian join. The first is by using the older (pre-ANSI92) implicit join syntax:

SELECT s.mean,
Power(sds.sds / s.num_starred, 0.5) AS stddev
FROM   stats AS s,
sum_diff_squared AS sds
WHERE  s.user_id = sds.user_id


Or by using the more modern explicit join syntax, which I always prefer due to its greater clarity:

SELECT s.mean,
Power(sds.sds / s.num_starred, 0.5) AS stddev
FROM   stats AS s
JOIN   sum_diff_squared AS sds
ON s.user_id = sds.user_id


The revised query returns identical results.

• I did understand that the last bit was a Cartesian join. What is the benefit of the join condition on "tables" which would ideally be typed as scalars or (single) rows? Apr 13, 2017 at 4:41
• In your case, I suppose there's not really much of a difference. If I see a join like that in production code though, it raises a big red flag since you said you were learning I didn't know whether you used that by accident or on purpose. Apr 13, 2017 at 4:44