{"action":"created","comment":{"url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/comments/40138397","id":40138397,"diff_hunk":"@@ -0,0 +1,105 @@\n+\n+ \n+\n+mysql> CREATE DATABASE store;\n+\n+mysql> USE store;\n+\n+mysql> CREATE TABLE item (id  int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) );\n+\n+mysql> CREATE TABLE transactions (id  int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) );                                                    \n+\n+mysql> SHOW TABLES;\n++-----------------+\n+| Tables_in_store |\n++-----------------+\n+| item            |\n+| transactions    |\n++-----------------+\n+\n+mysql> ALTER TABLE item ADD COLUMN productName VARCHAR(40) NOT NULL;\n+\n+mysql> ALTER TABLE item ADD COLUMN productDescription VARCHAR(255) NULL;\n+\n+mysql> ALTER TABLE item ADD COLUMN manufacturedBy VARCHAR(255) NOT NULL;\n+\n+mysql> ALTER TABLE item ADD COLUMN retailPrice INT(5) NOT NULL;\n+\n+mysql> ALTER TABLE item ADD COLUMN inventoryQuantity INT(11) NOT NULL;\n+\n+mysql> ALTER TABLE item ADD COLUMN lastModified DATETIME NOT NULL;\n+\n+mysql> RENAME TABLE transactions to transaction;\n+\n+mysql> ALTER TABLE transaction ADD COLUMN itemId INT(11) NOT NULL;\n+\n+mysql> ALTER TABLE transaction ADD COLUMN quantity INT(11) NOT NULL;\n+\n+mysql> ALTER TABLE transaction ADD COLUMN purchaseType ENUM(\"sale\", \"refund\") NOT NULL;\n+\n+mysql> ALTER TABLE transaction ADD COLUMN saleType ENUM(\"internal\", \"external\") NOT NULL;\n+\n+mysql> ALTER TABLE transaction ADD COLUMN transactionDate DATETIME NOT NULL;\n+\n+mysql> ALTER TABLE item CHANGE lastModified inventoryLastUpdated DATETIME;\n+\n+mysql> DESCRIBE item;\n++----------------------+--------------+------+-----+---------+----------------+\n+| Field                | Type         | Null | Key | Default | Extra          |\n++----------------------+--------------+------+-----+---------+----------------+\n+| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |\n+| productName          | varchar(40)  | NO   |     | NULL    |                |\n+| productDescription   | varchar(255) | YES  |     | NULL    |                |\n+| manufacturedBy       | varchar(255) | NO   |     | NULL    |                |\n+| retailPrice          | int(5)       | NO   |     | NULL    |                |\n+| inventoryQuantity    | int(11)      | NO   |     | NULL    |                |\n+| inventoryLastUpdated | datetime     | YES  |     | NULL    |                |\n++----------------------+--------------+------+-----+---------+----------------+\n+\n+mysql> DESCRIBE transaction;\n++-----------------+-----------------------------+------+-----+---------+----------------+\n+| Field           | Type                        | Null | Key | Default | Extra          |\n++-----------------+-----------------------------+------+-----+---------+----------------+\n+| id              | int(11)                     | NO   | PRI | NULL    | auto_increment |\n+| itemId          | int(11)                     | NO   |     | NULL    |                |\n+| quantity        | int(11)                     | NO   |     | NULL    |                |\n+| purchaseType    | enum('sale','refund')       | NO   |     | NULL    |                |\n+| saleType        | enum('internal','external') | NO   |     | NULL    |                |\n+| transactionDate | datetime                    | NO   |     | NULL    |                |\n++-----------------+-----------------------------+------+-----+---------+----------------+\n+\n+### Exercise 14 (Workshop Challenge)\n+  * What is the Store's income within a specific date range? \n+        1) Pull list of transactions between two dates\n+        2) Group by transaction type \n+        3) Group by itemId \n+        4) multily quantity by retailPrice (item table)\n+        5) Income = Sum of sale transactions and minus refund transactions\n+  * What is the Store's top selling Inventory product?\n+        1) Group transactions by itemID for only purchaseType=sale\n+        2) Sum quantity sold for each itemID\n+  * Which company produces the top selling Inventory product?\n+        1) check the manufacturedBy column for the top itemID inventory product\n+  * What is the top refunded Inventory product?\n+        1) filter transactions by purchaseType=refund\n+        2) Group by itemID\n+        3) Sum quantity\n+  * Which products should be taken out from the Store's Inventory?\n+        1) group transactions by itemID\n+        2) remove items that:\n+            -have not been sold recently\n+            -have only sold only small quantities\n+            -have a high number of refunds\n+  * Which companies should the Store stop selling products from?\n+        1) check the manufacturedBy corresponding to the itemID in the above example\n+  * What is the amount of Internal (Canadian) vs. External sales?\n+        1) filter tansactions by saleType\n+        2) group by itemID\n+        3) multiply quantity of item sold by retail Price\n+  * How many of a specific product remains in Inventory for a specific date?\n+        a) refer to the inventoryLastUpdated date on the itemID\n+        b) pull all transactions for that itemID between current day and modified time\n+        c) if purchaseType=refund add quantity to inventory\n+        d) if purchaseType=sale remove quantity from inventory\n+        e) update item quantity and timestamp of inventoryLastUpdated\n+            ","path":"exercise-14.txt","position":105,"original_position":105,"commit_id":"e44f0cd8955e108bd7f8bcbebb89e9708ad9c934","original_commit_id":"e44f0cd8955e108bd7f8bcbebb89e9708ad9c934","user":{"login":"nyanofthemoon","id":3919995,"avatar_url":"https://avatars.githubusercontent.com/u/3919995?v=3","gravatar_id":"","url":"https://api.github.com/users/nyanofthemoon","html_url":"https://github.com/nyanofthemoon","followers_url":"https://api.github.com/users/nyanofthemoon/followers","following_url":"https://api.github.com/users/nyanofthemoon/following{/other_user}","gists_url":"https://api.github.com/users/nyanofthemoon/gists{/gist_id}","starred_url":"https://api.github.com/users/nyanofthemoon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nyanofthemoon/subscriptions","organizations_url":"https://api.github.com/users/nyanofthemoon/orgs","repos_url":"https://api.github.com/users/nyanofthemoon/repos","events_url":"https://api.github.com/users/nyanofthemoon/events{/privacy}","received_events_url":"https://api.github.com/users/nyanofthemoon/received_events","type":"User","site_admin":false},"body":"Challenge: could you provide a dataset and some sample queries to validate your model? :)","created_at":"2015-09-22T20:20:13Z","updated_at":"2015-09-22T20:20:13Z","html_url":"https://github.com/DecodeMTL/mysql-workshop-2/pull/106#discussion_r40138397","pull_request_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106","_links":{"self":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/comments/40138397"},"html":{"href":"https://github.com/DecodeMTL/mysql-workshop-2/pull/106#discussion_r40138397"},"pull_request":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106"}}},"pull_request":{"url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106","id":45706617,"html_url":"https://github.com/DecodeMTL/mysql-workshop-2/pull/106","diff_url":"https://github.com/DecodeMTL/mysql-workshop-2/pull/106.diff","patch_url":"https://github.com/DecodeMTL/mysql-workshop-2/pull/106.patch","issue_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues/106","number":106,"state":"open","locked":false,"title":"created store database","user":{"login":"hennigk","id":13035971,"avatar_url":"https://avatars.githubusercontent.com/u/13035971?v=3","gravatar_id":"","url":"https://api.github.com/users/hennigk","html_url":"https://github.com/hennigk","followers_url":"https://api.github.com/users/hennigk/followers","following_url":"https://api.github.com/users/hennigk/following{/other_user}","gists_url":"https://api.github.com/users/hennigk/gists{/gist_id}","starred_url":"https://api.github.com/users/hennigk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hennigk/subscriptions","organizations_url":"https://api.github.com/users/hennigk/orgs","repos_url":"https://api.github.com/users/hennigk/repos","events_url":"https://api.github.com/users/hennigk/events{/privacy}","received_events_url":"https://api.github.com/users/hennigk/received_events","type":"User","site_admin":false},"body":"","created_at":"2015-09-22T19:35:13Z","updated_at":"2015-09-22T20:20:13Z","closed_at":null,"merged_at":null,"merge_commit_sha":"a1212f7e8107bd3b0efb8f2c270540ca84308975","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106/commits","review_comments_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106/comments","review_comment_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/comments{/number}","comments_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues/106/comments","statuses_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/statuses/e44f0cd8955e108bd7f8bcbebb89e9708ad9c934","head":{"label":"hennigk:exercise-14","ref":"exercise-14","sha":"e44f0cd8955e108bd7f8bcbebb89e9708ad9c934","user":{"login":"hennigk","id":13035971,"avatar_url":"https://avatars.githubusercontent.com/u/13035971?v=3","gravatar_id":"","url":"https://api.github.com/users/hennigk","html_url":"https://github.com/hennigk","followers_url":"https://api.github.com/users/hennigk/followers","following_url":"https://api.github.com/users/hennigk/following{/other_user}","gists_url":"https://api.github.com/users/hennigk/gists{/gist_id}","starred_url":"https://api.github.com/users/hennigk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hennigk/subscriptions","organizations_url":"https://api.github.com/users/hennigk/orgs","repos_url":"https://api.github.com/users/hennigk/repos","events_url":"https://api.github.com/users/hennigk/events{/privacy}","received_events_url":"https://api.github.com/users/hennigk/received_events","type":"User","site_admin":false},"repo":{"id":42939446,"name":"mysql-workshop-2","full_name":"hennigk/mysql-workshop-2","owner":{"login":"hennigk","id":13035971,"avatar_url":"https://avatars.githubusercontent.com/u/13035971?v=3","gravatar_id":"","url":"https://api.github.com/users/hennigk","html_url":"https://github.com/hennigk","followers_url":"https://api.github.com/users/hennigk/followers","following_url":"https://api.github.com/users/hennigk/following{/other_user}","gists_url":"https://api.github.com/users/hennigk/gists{/gist_id}","starred_url":"https://api.github.com/users/hennigk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hennigk/subscriptions","organizations_url":"https://api.github.com/users/hennigk/orgs","repos_url":"https://api.github.com/users/hennigk/repos","events_url":"https://api.github.com/users/hennigk/events{/privacy}","received_events_url":"https://api.github.com/users/hennigk/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/hennigk/mysql-workshop-2","description":"","fork":true,"url":"https://api.github.com/repos/hennigk/mysql-workshop-2","forks_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/forks","keys_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/keys{/key_id}","collaborators_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/teams","hooks_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/hooks","issue_events_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/issues/events{/number}","events_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/events","assignees_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/assignees{/user}","branches_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/branches{/branch}","tags_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/tags","blobs_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/git/refs{/sha}","trees_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/git/trees{/sha}","statuses_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/statuses/{sha}","languages_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/languages","stargazers_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/stargazers","contributors_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/contributors","subscribers_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/subscribers","subscription_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/subscription","commits_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/commits{/sha}","git_commits_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/git/commits{/sha}","comments_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/comments{/number}","issue_comment_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/issues/comments{/number}","contents_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/contents/{+path}","compare_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/compare/{base}...{head}","merges_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/merges","archive_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/downloads","issues_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/issues{/number}","pulls_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/pulls{/number}","milestones_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/milestones{/number}","notifications_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/labels{/name}","releases_url":"https://api.github.com/repos/hennigk/mysql-workshop-2/releases{/id}","created_at":"2015-09-22T14:37:27Z","updated_at":"2015-09-20T11:45:48Z","pushed_at":"2015-09-22T20:15:57Z","git_url":"git://github.com/hennigk/mysql-workshop-2.git","ssh_url":"git@github.com:hennigk/mysql-workshop-2.git","clone_url":"https://github.com/hennigk/mysql-workshop-2.git","svn_url":"https://github.com/hennigk/mysql-workshop-2","homepage":null,"size":370,"stargazers_count":0,"watchers_count":0,"language":null,"has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"DecodeMTL:master","ref":"master","sha":"ed04a00342955f3e3d7df36abe185abcdd71be3e","user":{"login":"DecodeMTL","id":14301039,"avatar_url":"https://avatars.githubusercontent.com/u/14301039?v=3","gravatar_id":"","url":"https://api.github.com/users/DecodeMTL","html_url":"https://github.com/DecodeMTL","followers_url":"https://api.github.com/users/DecodeMTL/followers","following_url":"https://api.github.com/users/DecodeMTL/following{/other_user}","gists_url":"https://api.github.com/users/DecodeMTL/gists{/gist_id}","starred_url":"https://api.github.com/users/DecodeMTL/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DecodeMTL/subscriptions","organizations_url":"https://api.github.com/users/DecodeMTL/orgs","repos_url":"https://api.github.com/users/DecodeMTL/repos","events_url":"https://api.github.com/users/DecodeMTL/events{/privacy}","received_events_url":"https://api.github.com/users/DecodeMTL/received_events","type":"Organization","site_admin":false},"repo":{"id":42811011,"name":"mysql-workshop-2","full_name":"DecodeMTL/mysql-workshop-2","owner":{"login":"DecodeMTL","id":14301039,"avatar_url":"https://avatars.githubusercontent.com/u/14301039?v=3","gravatar_id":"","url":"https://api.github.com/users/DecodeMTL","html_url":"https://github.com/DecodeMTL","followers_url":"https://api.github.com/users/DecodeMTL/followers","following_url":"https://api.github.com/users/DecodeMTL/following{/other_user}","gists_url":"https://api.github.com/users/DecodeMTL/gists{/gist_id}","starred_url":"https://api.github.com/users/DecodeMTL/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DecodeMTL/subscriptions","organizations_url":"https://api.github.com/users/DecodeMTL/orgs","repos_url":"https://api.github.com/users/DecodeMTL/repos","events_url":"https://api.github.com/users/DecodeMTL/events{/privacy}","received_events_url":"https://api.github.com/users/DecodeMTL/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/DecodeMTL/mysql-workshop-2","description":"","fork":false,"url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2","forks_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/forks","keys_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/keys{/key_id}","collaborators_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/teams","hooks_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/hooks","issue_events_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues/events{/number}","events_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/events","assignees_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/assignees{/user}","branches_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/branches{/branch}","tags_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/tags","blobs_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/git/refs{/sha}","trees_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/git/trees{/sha}","statuses_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/statuses/{sha}","languages_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/languages","stargazers_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/stargazers","contributors_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/contributors","subscribers_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/subscribers","subscription_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/subscription","commits_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/commits{/sha}","git_commits_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/git/commits{/sha}","comments_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/comments{/number}","issue_comment_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues/comments{/number}","contents_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/contents/{+path}","compare_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/compare/{base}...{head}","merges_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/merges","archive_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/downloads","issues_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues{/number}","pulls_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls{/number}","milestones_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/milestones{/number}","notifications_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/labels{/name}","releases_url":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/releases{/id}","created_at":"2015-09-20T11:45:48Z","updated_at":"2015-09-20T11:45:48Z","pushed_at":"2015-09-22T20:17:57Z","git_url":"git://github.com/DecodeMTL/mysql-workshop-2.git","ssh_url":"git@github.com:DecodeMTL/mysql-workshop-2.git","clone_url":"https://github.com/DecodeMTL/mysql-workshop-2.git","svn_url":"https://github.com/DecodeMTL/mysql-workshop-2","homepage":null,"size":2017,"stargazers_count":0,"watchers_count":0,"language":null,"has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":12,"mirror_url":null,"open_issues_count":34,"forks":12,"open_issues":34,"watchers":0,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106"},"html":{"href":"https://github.com/DecodeMTL/mysql-workshop-2/pull/106"},"issue":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues/106"},"comments":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/issues/106/comments"},"review_comments":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106/comments"},"review_comment":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/pulls/106/commits"},"statuses":{"href":"https://api.github.com/repos/DecodeMTL/mysql-workshop-2/statuses/e44f0cd8955e108bd7f8bcbebb89e9708ad9c934"}}}}