Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Unanswered: Can someone help me with creating queries for the below information at the Earliest ?

    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’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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    please make an effort
    find out what works
    find out what doesn't
    find out how to develop queries

    if you have a problem whilst doing (and by inference learning), then by all means come back
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2016
    Posts
    2

    Can someone help me with creating queries for the below information at the Earliest ?

    Quote Originally Posted by healdem View Post
    please make an effort
    find out what works
    find out what doesn't
    find out how to develop queries

    if you have a problem whilst doing (and by inference learning), then by all means come back
    Hi This is what i could come up with

    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(page_views)
    FROM articles
    WHERE first_comment_created_at IS NOT NULL
    GROUP BY Titles
    ORDER BY number_of_comments DESC

    and


    2. 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

    Could you please help me improve these queries or complete them for me ? Thats the only thing i can come up with

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..and do those queries meet the requirements
    have you actually run them against test data

    do you have nay evidence that they do, or do not, meet the requirements
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •