{"action":"opened","issue":{"url":"https://api.github.com/repos/derrickus/sql-scratch-capstone-turn-in/issues/1","repository_url":"https://api.github.com/repos/derrickus/sql-scratch-capstone-turn-in","labels_url":"https://api.github.com/repos/derrickus/sql-scratch-capstone-turn-in/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/derrickus/sql-scratch-capstone-turn-in/issues/1/comments","events_url":"https://api.github.com/repos/derrickus/sql-scratch-capstone-turn-in/issues/1/events","html_url":"https://github.com/derrickus/sql-scratch-capstone-turn-in/issues/1","id":338046338,"node_id":"MDU6SXNzdWUzMzgwNDYzMzg=","number":1,"title":"Project Review","user":{"login":"jordanwillis","id":7652259,"node_id":"MDQ6VXNlcjc2NTIyNTk=","avatar_url":"https://avatars2.githubusercontent.com/u/7652259?v=4","gravatar_id":"","url":"https://api.github.com/users/jordanwillis","html_url":"https://github.com/jordanwillis","followers_url":"https://api.github.com/users/jordanwillis/followers","following_url":"https://api.github.com/users/jordanwillis/following{/other_user}","gists_url":"https://api.github.com/users/jordanwillis/gists{/gist_id}","starred_url":"https://api.github.com/users/jordanwillis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jordanwillis/subscriptions","organizations_url":"https://api.github.com/users/jordanwillis/orgs","repos_url":"https://api.github.com/users/jordanwillis/repos","events_url":"https://api.github.com/users/jordanwillis/events{/privacy}","received_events_url":"https://api.github.com/users/jordanwillis/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-07-03T20:37:52Z","updated_at":"2018-07-03T20:37:52Z","closed_at":null,"author_association":"NONE","body":" ## Rubric Score\r\n \r\n### Criteria 1: Report: Are conclusions clear and supported by evidence in all answers?\r\n* _Score Level:_ 3/4\r\n* _Comment(s):_ Overall your report looks pretty good and the conclusions that you provided were clear and supported with either data or queries (or both).  It wasn't very clear if you were confident in your answers on task 5 and 6 with the way the text was worded, but I would have liked to have seem much more data analysis performed for step 6 so that you could draw some actionable insights.  For example, you could answer what's the most purchased model, which model provides the most revenue, is 3 or 5 pairs better, etc.\r\n \r\n### Criteria 2: Query Accuracy: Do queries do what they were supposed to do?\r\n* _Score Level:_ 3/4\r\n* _Comment(s):_ I was not clear what the query in task 6 was really supposed to be doing.  It could be correct but without the context in the presentation describing what's being done, it is hard to know.\r\n\r\nI also saw your note in the query for task 5 about not being able to get `CASE` to work.  Here is an example demonstrating how you could have answered that question, however your approach was also valid.\r\n\r\n``` sql\r\nWITH home_try_on_cte AS \r\n\t(SELECT user_id, \r\n\t\tCASE\r\n\t\tWHEN user_id IS NOT NULL THEN 'True'\r\n\t\tELSE 'False'\r\n\t\tEND AS is_home_try_on\r\n\tFROM home_try_on\r\n\tGROUP BY user_id \r\n\t),\r\n\r\npurchase_cte AS\r\n\t(SELECT user_id, \r\n\t\tCASE \r\n\t\tWHEN user_id IS NOT NULL THEN 'True'\r\n \t\tELSE 'False' \r\n   \tEND AS is_purchase\r\n \tFROM purchase\r\n \tGROUP BY user_id\r\n \t)\r\n\r\nSELECT hto_cte.user_id, \r\n\thto_cte.is_home_try_on, \r\n\thto.number_of_pairs, \r\n\tp_cte.is_purchase\r\nFROM home_try_on_cte hto_cte\r\n    LEFT JOIN home_try_on hto\r\nON hto_cte.user_id = hto.user_id\r\n    LEFT JOIN purchase_cte p_cte \r\nON hto.user_id = p_cte.user_id\r\nLIMIT 10;\r\n```\r\n \r\n### Criteria 3: Query Formatting\r\n* _Score Level:_ 4/4\r\n* _Comment(s):_ Nice work!  All your queries followed to correct formatting rules.\r\n \r\n### Criteria 4: Understanding underlying concepts and terminology\r\n* _Score Level:_ 3/4\r\n* _Comment(s):_ I really think that you understand the concept of funnels and associated data analysis terminology, but without more detail in the last two answers it is difficult to know for sure.  I thought I would share some queries that you could use to analyze some of this data to make conclusions about each funnel.\r\n\r\n``` sql\r\nSELECT purchase.model_name AS 'Model', \r\n\tpurchase.style AS 'Style', \r\n\tCOUNT(purchase.model_name) AS 'Pairs Purchased', \r\n\tpurchase.price AS 'Price', \r\n\t(COUNT(purchase.model_name)*purchase.price) AS 'Revenue'\r\nFROM purchase\r\nGROUP BY 1\r\nORDER BY 4 DESC;\r\n\r\nSELECT purchase.style AS 'Style', \r\n\tCOUNT(purchase.style) AS 'Models Purchased', \r\n\t(COUNT(purchase.style)*purchase.price) AS 'Revenue'\r\nFROM purchase\r\nGROUP BY 1\r\nORDER BY 3; \r\n\r\nSELECT home_try_on.number_of_pairs AS 'Pairs Tried', \r\n\tCOUNT(DISTINCT home_try_on.user_id) AS 'Trial Customers Count', \r\n\tCOUNT(DISTINCT purchase.user_id) AS 'Paid Customers Count', \r\n\tROUND(1.0*COUNT(DISTINCT purchase.user_id)/COUNT(DISTINCT home_try_on.user_id), 2) AS 'Conversion Rate'\r\nFROM home_try_on\r\nLEFT JOIN purchase\r\nON home_try_on.user_id = purchase.user_id\r\nGROUP BY 1;\r\n```\r\n\r\n ### Overall Score: 13/16\r\n\r\nOverall you did a great job putting together a nice presentation that included clear text and proper data and queries for supporting material.  Be sure to read through each note above for ideas on how to continue improving!\r\n\r\nKeep up the great work!\r\n"}}
{"actor":{"display_login":"jordanwillis"}}