Please help me to write 1 queries in Big Query(SQL) on Google Cloud's Hacker News Data( https://cloud.google.com/bigquery/public-data/hacker-news ) On average which URL produced the best story in 2010?

That

#legacySQL

SELECT

REGEXP_EXTRACT((REGEXP_EXTRACT(stories.url,'http://([^/]+)/')),'([^\\.]+\\.[^\\.]+(?:\\.[a-zA-Z].)?)$') AS stories_url_domain,

COUNT(stories.id) AS stories_count,

//COUNT(CASE WHEN stories.score >= 0 THEN 1 ELSE NULL END) AS storiescountscore_7_plus,

//100.0 * (COUNT(CASE WHEN stories.score >= 7 THEN 1 ELSE NULL END)) / (COUNT(stories.id)) AS stories_percent_7_plus,

SUM (stories.score) AS stories_score,

SUM (stories.score)/COUNT (stories.id) AS avg_stories_score,

AVG (stories.score) AS avg_stories_score_2

FROM

[bigquery-public-data:hacker_news.stories] AS stories

GROUP EACH BY

1

//HAVING

//(COUNT(CASE WHEN stories.score >= 7 THEN 1 ELSE NULL END) >= 6)

ORDER BY

4 DESC

LIMIT

10000

Time:

SELECT URL , COUNT (URL) as COUNT , time_ts FROM [bigquery-public-data:hacker_news.stories]

where YEAR(time_ts) = 2010

GROUP BY URL, time_ts

ORDER BY COUNT DESC

What I did right now-

Schema

id	INTEGER	NULLABLE	Unique story ID
by	STRING	NULLABLE	Username of submitter
score	INTEGER	NULLABLE	Story score
time	INTEGER	NULLABLE	Unix time
time_ts	TIMESTAMP	NULLABLE	Human readable time in UTC (format: YYYY-MM-DD hh:mm:ss)
title	STRING	NULLABLE	Story title
url	STRING	NULLABLE	Story url
text	STRING	NULLABLE	Story text
deleted	BOOLEAN	NULLABLE	Is deleted?
dead	BOOLEAN	NULLABLE	Is dead?
descendants	INTEGER	NULLABLE	Number of story descendants
author	STRING	NULLABLE	Username of author