{"action":"closed","issue":{"url":"https://api.github.com/repos/hdengg/MysteryBox_CS304/issues/5","repository_url":"https://api.github.com/repos/hdengg/MysteryBox_CS304","labels_url":"https://api.github.com/repos/hdengg/MysteryBox_CS304/issues/5/labels{/name}","comments_url":"https://api.github.com/repos/hdengg/MysteryBox_CS304/issues/5/comments","events_url":"https://api.github.com/repos/hdengg/MysteryBox_CS304/issues/5/events","html_url":"https://github.com/hdengg/MysteryBox_CS304/issues/5","id":308321848,"number":5,"title":"Implement Subscription","user":{"login":"ReggieLai","id":24737587,"avatar_url":"https://avatars2.githubusercontent.com/u/24737587?v=4","gravatar_id":"","url":"https://api.github.com/users/ReggieLai","html_url":"https://github.com/ReggieLai","followers_url":"https://api.github.com/users/ReggieLai/followers","following_url":"https://api.github.com/users/ReggieLai/following{/other_user}","gists_url":"https://api.github.com/users/ReggieLai/gists{/gist_id}","starred_url":"https://api.github.com/users/ReggieLai/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ReggieLai/subscriptions","organizations_url":"https://api.github.com/users/ReggieLai/orgs","repos_url":"https://api.github.com/users/ReggieLai/repos","events_url":"https://api.github.com/users/ReggieLai/events{/privacy}","received_events_url":"https://api.github.com/users/ReggieLai/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":{"login":"janicelee","id":25831712,"avatar_url":"https://avatars1.githubusercontent.com/u/25831712?v=4","gravatar_id":"","url":"https://api.github.com/users/janicelee","html_url":"https://github.com/janicelee","followers_url":"https://api.github.com/users/janicelee/followers","following_url":"https://api.github.com/users/janicelee/following{/other_user}","gists_url":"https://api.github.com/users/janicelee/gists{/gist_id}","starred_url":"https://api.github.com/users/janicelee/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/janicelee/subscriptions","organizations_url":"https://api.github.com/users/janicelee/orgs","repos_url":"https://api.github.com/users/janicelee/repos","events_url":"https://api.github.com/users/janicelee/events{/privacy}","received_events_url":"https://api.github.com/users/janicelee/received_events","type":"User","site_admin":false},"assignees":[{"login":"janicelee","id":25831712,"avatar_url":"https://avatars1.githubusercontent.com/u/25831712?v=4","gravatar_id":"","url":"https://api.github.com/users/janicelee","html_url":"https://github.com/janicelee","followers_url":"https://api.github.com/users/janicelee/followers","following_url":"https://api.github.com/users/janicelee/following{/other_user}","gists_url":"https://api.github.com/users/janicelee/gists{/gist_id}","starred_url":"https://api.github.com/users/janicelee/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/janicelee/subscriptions","organizations_url":"https://api.github.com/users/janicelee/orgs","repos_url":"https://api.github.com/users/janicelee/repos","events_url":"https://api.github.com/users/janicelee/events{/privacy}","received_events_url":"https://api.github.com/users/janicelee/received_events","type":"User","site_admin":false},{"login":"hdengg","id":26157176,"avatar_url":"https://avatars0.githubusercontent.com/u/26157176?v=4","gravatar_id":"","url":"https://api.github.com/users/hdengg","html_url":"https://github.com/hdengg","followers_url":"https://api.github.com/users/hdengg/followers","following_url":"https://api.github.com/users/hdengg/following{/other_user}","gists_url":"https://api.github.com/users/hdengg/gists{/gist_id}","starred_url":"https://api.github.com/users/hdengg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hdengg/subscriptions","organizations_url":"https://api.github.com/users/hdengg/orgs","repos_url":"https://api.github.com/users/hdengg/repos","events_url":"https://api.github.com/users/hdengg/events{/privacy}","received_events_url":"https://api.github.com/users/hdengg/received_events","type":"User","site_admin":false}],"milestone":null,"comments":0,"created_at":"2018-03-25T04:39:36Z","updated_at":"2018-03-28T06:45:38Z","closed_at":"2018-03-28T06:45:38Z","author_association":"COLLABORATOR","body":"- [x] **given a customer id, find all their subscriptions** \r\nSELECT * FROM Subscription WHERE (username = ?);  \r\n\r\n- [x] **# of subscriptions for a theme**\r\nSELECT mb.theme, COUNT (st.s_id) as total\r\nFROM Subscribes_To st NATURAL INNER JOIN Mystery_Box mb\r\nWHERE mb.theme = ?\r\nGROUP BY mb.theme\r\n\r\n- [x] **max number of subscriptions grouped by theme [nested aggregation query]**\r\nWITH temp_table AS\r\n(SELECT temp.theme, temp.sub_counts\r\nFROM\r\n(SELECT mb.theme, COUNT (st.s_id) as sub_counts\r\nFROM Subscribes_To st NATURAL JOIN Mystery_Box mb\r\nGROUP BY mb.theme) temp)\r\nSELECT * FROM temp_table\r\nWHERE sub_counts = (SELECT MAX(sub_counts) FROM temp_table)\r\n\r\n- [x] **username of ppl who have subscribed from date**\r\nSELECT DISTINCT(username)\r\nFROM Subscription s\r\nWHERE s.s_from > ?\r\n"}}
{"actor":{"display_login":"hdengg"}}