April 17, 2016 at 11:08 am
Try to write the simplest query that fully answers the question, avoiding unnecessary complexity.
These tables from our data warehouse provide enough information to answer (you may not need all of them).
●articles_pageviews
table description: each row of the table contains information about a unique pageview made by a user to articles on our web-site
fields: pageview_timestamp, article_id, visitor_user_id, url
●articles
table description: each row of the table contains information about a unique article published on our web-site
fields: article_id, title, publication_timestamp, author, first_comment_created_at(timestamp), number_of_comments
* if there were no comments on article field first_comment_created_at will be null and field number_of_comments will be equal to 0
** key field to join between 2 tables is “article_id”
1. Visitors to our website read articles that they can comment on, in an ongoing process. Please, write an SQL query that will return a list of articles and number of page views made to those articles while there were still no comments on them (note, you should exclude articles with no comments at all). Please order the output in descending order of the column with number of page views
2. Please write an SQL query that will answer the following question:
What is the probability that article will have comment on it? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
3. Please write an SQL query that will answer the following question:
What’s an average number of comments made per article? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
April 17, 2016 at 11:14 am
djmenon21 (4/17/2016)
Try to write the simplest query that fully answers the question, avoiding unnecessary complexity.These tables from our data warehouse provide enough information to answer (you may not need all of them).
?articles_pageviews
table description: each row of the table contains information about a unique pageview made by a user to articles on our web-site
fields: pageview_timestamp, article_id, visitor_user_id, url
?articles
table description: each row of the table contains information about a unique article published on our web-site
fields: article_id, title, publication_timestamp, author, first_comment_created_at(timestamp), number_of_comments
* if there were no comments on article field first_comment_created_at will be null and field number_of_comments will be equal to 0
** key field to join between 2 tables is “article_id”
1. Visitors to our website read articles that they can comment on, in an ongoing process. Please, write an SQL query that will return a list of articles and number of page views made to those articles while there were still no comments on them (note, you should exclude articles with no comments at all). Please order the output in descending order of the column with number of page views
2. Please write an SQL query that will answer the following question:
What is the probability that article will have comment on it? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
3. Please write an SQL query that will answer the following question:
What’s an average number of comments made per article? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
This looks very much like a homework, trivial challenges here
😎
Can you provide DDL (create table) , sample data as an insert statement and the expected results?
April 17, 2016 at 11:20 am
Hi Thanks for the Prompt reply,
Thats the only information i have got with me im afraid 😛 would that be sufficient ?? To have the answers for those questions as
April 17, 2016 at 11:34 am
djmenon21 (4/17/2016)
Try to write the simplest query that fully answers the question, avoiding unnecessary complexity.These tables from our data warehouse provide enough information to answer (you may not need all of them).
?articles_pageviews
table description: each row of the table contains information about a unique pageview made by a user to articles on our web-site
fields: pageview_timestamp, article_id, visitor_user_id, url
?articles
table description: each row of the table contains information about a unique article published on our web-site
fields: article_id, title, publication_timestamp, author, first_comment_created_at(timestamp), number_of_comments
* if there were no comments on article field first_comment_created_at will be null and field number_of_comments will be equal to 0
** key field to join between 2 tables is “article_id”
1. Visitors to our website read articles that they can comment on, in an ongoing process. Please, write an SQL query that will return a list of articles and number of page views made to those articles while there were still no comments on them (note, you should exclude articles with no comments at all). Please order the output in descending order of the column with number of page views
2. Please write an SQL query that will answer the following question:
What is the probability that article will have comment on it? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
3. Please write an SQL query that will answer the following question:
What’s an average number of comments made per article? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
what have you tried so far?
where are you getting stuck?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 11:40 am
I have given it a thought but couldn't come up with anything..Hence i posted this
Can someone please help me build this ?
I have just pretty basic knowledge of SQL and still in the learning phase.
April 17, 2016 at 11:45 am
djmenon21 (4/17/2016)
I have given it a thought but couldn't come up with anything..Hence i posted thisCan someone please help me build this ?
I have just pretty basic knowledge of SQL and still in the learning phase.
in the first instance then, I would suggest that you fire up SSMS, create the test tables and insert some sample data that you believe is pertinent to the questions being asked.
using these tables you can then start to discover what queries/ filters you will nedd and the join types between the two tables.
if you are still stuck after that...then please read here to understand how to post a question withe relevant data
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 11:39 pm
This is what i came up with for the below questions as per the data given :- Can anyone help me with the 2nd question ?
1. Visitors to our website read articles that they can comment on, in an ongoing process. Please, write an SQL query that will return a list of articles and number of page views made to those articles while there were still no comments on them (note, you should exclude articles with no comments at all). Please order the output in descending order of the column with number of page views
SELECT Title, SUM(number_of_comments)
FROM articles
WHERE first_comment_created_at IS NOT NULL
GROUP BY Titles
ORDER BY number_of_comments DESC
2. Please write an SQL query that will answer the following question:
What is the probability that article will have comment on it? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
3. Please write an SQL query that will answer the following question:
What’s an average number of comments made per article? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
SELECT Title, AVG(number_of_comments)
FROM articles
GROUP BY Titles
April 18, 2016 at 12:25 am
djmenon21 (4/17/2016)
This is what i came up with for the below questions as per the data given :- Can anyone help me with the 2nd question ?2. Please write an SQL query that will answer the following question:
What is the probability that article will have comment on it? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
not sure if this is what you are after, only guessing based on what I think your question means.
I've written the code in a quick dirty way so you can understand what exactly I've done and also so it can make some sense to you otherwise if it was written in another way it will probably confuse you.
Long story short, I've found all articles that have comments, all articles that have no comments, and then total count of both. based on my sample data there are 4/10 that have comments.... to me probability that an article would have a comment is 40% of the time...
DECLARE @Temp TABLE (
Title VARCHAR(20) NOT NULL,
Comment VARCHAR(20) NULL
)
INSERT INTO @temp
(
Title,
Comment
)
SELECT 'title1', 'comment1' UNION ALL
SELECT 'title2', 'comment2' UNION ALL
SELECT 'title3', 'comment3' UNION ALL
SELECT 'title4', 'comment4' UNION ALL
SELECT 'title5', NULL UNION ALL
SELECT 'title6', NULL UNION ALL
SELECT 'title7', NULL UNION ALL
SELECT 'title8', NULL UNION ALL
SELECT 'title9', NULL UNION ALL
SELECT 'title10', NULL
-- titles that have comments
DECLARE @HasComment INT = (
SELECT[HasComment] = COUNT(*)
FROM @temp
WHERE Comment IS NOT NULL
)
-- all titles with no comments
DECLARE @NoComment INT = (
SELECT[NoComment] = COUNT(*)
FROM @temp
WHERE Comment IS NULL
)
-- 10 total comments (this includes comments and no comments)
DECLARE @TotalComments INT = (
SELECT[TotalComments] = COUNT(*)
FROM@temp
)
-- 4 titles out of 10 have comments, therefore 40% of articles have comments.
SELECT (@HasComment * 100)/ NULLIF(@TotalComments,0)
April 19, 2016 at 2:43 am
Hi,
Thanks a ton for those answers and this certainly helped..Can you please try to improve your query for the 3rd question ?
3. Please write an SQL query that will answer the following question:
What’s an average number of comments made per article? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
SELECT Title, AVG(number_of_comments)
FROM articles
GROUP BY Titles
April 19, 2016 at 3:03 am
djmenon21 (4/19/2016)
Can you please try to improve your query for the 3rd question ?3. Please write an SQL query that will answer the following question:
What’s an average number of comments made per article? Please, assume that if article is commented - it is commented during the first week after publication and has no new comments afterwards
SELECT Title, AVG(number_of_comments)
FROM articles
GROUP BY Titles
How about you give it a try? This looks like homework, you're not going to learn much by having others do your work for you.
What do you think needs improving about the third question?
Your first answer, however, is wrong. It asks
Please, write an SQL query that will return a list of articles and number of page views made to those articles while there were still no comments on them
Your query then counts the number of comments. How would you fix it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2016 at 3:14 am
HI,
The reason i have posted this here is ask help..if i really knew how to solve those i would have not come here in the 1st place.
Whatever i knew and could think of i have posted earlier as answers.
Since you mentioned that the 1st one is wrong too..can you post answers to both the 1st and 3rd question ?? This would help me learn and improve
Thanks in advance
April 19, 2016 at 3:27 am
djmenon21 (4/19/2016)
The reason i have posted this here is ask help..if i really knew how to solve those i would have not come here in the 1st place.
We're happy to help, but that doesn't mean giving you the answers outright. Advise, suggest, guide you, sure. Outright answers, no.
Since you mentioned that the 1st one is wrong too..can you post answers to both the 1st and 3rd question ??
Nope. I don't need the practice writing basic queries, and you'll learn a lot faster working through it yourself than getting an answer given to you.
Now, the first question.
It asks for the total number of page_views, not the total number of comments. So, given that, how would you change your query
SELECT Title, SUM(number_of_comments)
FROM articles
WHERE first_comment_created_at IS NOT NULL
GROUP BY Titles
ORDER BY number_of_comments DESC
so that it returns the total number of page views, not the total number of comments?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2016 at 3:36 am
Hi Ok,
So will it be
SELECT Title, SUM(page_views)
FROM articles
WHERE first_comment_created_at IS NOT NULL
GROUP BY Titles
ORDER BY number_of_comments DESC
April 19, 2016 at 3:39 am
Does that query run?
If you haven't created test tables based on the assignment, you should. You need to test queries, it's very hard when you're starting to be able to see syntax errors by eye (hell, it's hard when you're not just starting too)
Hint: Go back to your assignment and look at what fields are in what table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2016 at 3:46 am
I cant figure out 🙁
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply