Query for stddev of number of stars per chatroom message
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 &gt; 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 &gt; 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.