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
* 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 169713 -- your query here 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