Please help me to write 1 SQL queries On average which URL produced the best sto
ID: 3805299 • Letter: P
Question
Please help me to write 1 SQL queries
On average which URL produced the best story in 2010?
Schema:
id
Unique story ID
Username of submitter
Story score
Unix time
Human readable time in UTC (format: YYYY-MM-DD hh:mm:ss)
Story title
Story url
Story text
Is deleted?
Is dead?
Number of story descendants
Username of author
I figured it out. Thanks anyway.
SELECT
url,
avg(score) as avg_score,
time_ts as TIME_STAMP
FROM [bigquery-public-data.hacker_news.stories]
where YEAR(time_ts) = 2010
GROUP BY url, TIME_STAMP
ORDER BY avg_score DESC
LIMIT 4
id
INTEGER NULLABLEUnique story ID
by STRING NULLABLEUsername of submitter
score INTEGER NULLABLEStory score
time INTEGER NULLABLEUnix time
time_ts TIMESTAMP NULLABLEHuman readable time in UTC (format: YYYY-MM-DD hh:mm:ss)
title STRING NULLABLEStory title
url STRING NULLABLEStory url
text STRING NULLABLEStory text
deleted BOOLEAN NULLABLEIs deleted?
dead BOOLEAN NULLABLEIs dead?
descendants INTEGER NULLABLENumber of story descendants
author STRING NULLABLEUsername of author
Explanation / Answer
SELECT
url,
avg(score) as avg_score,
time_ts as TIME_STAMP
FROM [bigquery-public-data.hacker_news.stories]
where YEAR(time_ts) = 2010
GROUP BY url, TIME_STAMP
ORDER BY avg_score DESC
LIMIT 4
I think you are not getting this code. Let me explain,
First of all you need to select url,avg score and time stamp hences in that they are menstion in SELECT ststement,FROM means from where you need to retrieve data hence thay given a site. Constions we have to meet is year is 2010, need to group by url and time stamp and. And also need to get deecending order by avg score.