CREATE TABLE `business` (
`bid` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
`state` varchar(3) NOT NULL,
PRIMARY KEY (`bid`)
)
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(1, 'Abby Rappoport, LAC, CMQ', '1616 Chapala St, Ste 2', 'Santa Barbara', 'CA', 7);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(2, 'The UPS Store', '87 Grasso Plaza Shopping Center', 'Affton', 'MO', 15);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(3, 'Target', '5255 E Broadway Blvd', 'Tucson', 'AZ', 22);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(4, 'St Honore Pastries', '935 Race St', 'Philadelphia', 'PA', 80);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(5, 'Perkiomen Valley Brewery', '101 Walnut St', 'Green Lane', 'PA', 13);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(6, 'Sonic Drive-In', '615 S Main St', 'Ashland City', 'TN', 6);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(7, 'Famous Footwear', '8522 Eager Road, Dierbergs Brentwood Point', 'Brentwood', 'MO', 13);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(8, 'Temple Beth-El', '400 Pasadena Ave S', 'St. Petersburg', 'FL', 5);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(9, 'Tsevi''s Pub And Grill', '8025 Mackenzie Rd', 'Affton', 'MO', 19);
INSERT INTO businessbid, name, address, city, state, review_count)VALUES(10, 'Sonic Drive-In', '2312 Dickerson Pike', 'Nashville', 'TN', 10);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(11, 'Marshalls', '21705 Village Lakes Sc Dr', 'Land O'' Lakes', 'FL', 6);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(12, 'Vietnamese Food Truck', '', 'Tampa Bay', 'FL', 10);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(13, 'Denny''s', '8901 US 31 S', 'Indianapolis', 'IN', 28);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(14, 'Adams Dental', '15 N Missouri Ave', 'Clearwater', 'FL', 10);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(15, 'Zio''s Italian Market', '2575 E Bay Dr', 'Largo', 'FL', 100);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(16, 'Tuna Bar', '205 Race St', 'Philadelphia', 'PA', 245);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(17, 'Arizona Truck Outfitters', '625 N Stone Ave', 'Tucson', 'AZ', 10);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(18, 'Herb Import Co', '712 Adams St', 'New Orleans', 'LA', 5);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(19, 'Nifty Car Rental', '1241 Airline Dr', 'Kenner', 'LA', 14);
INSERT INTO business(bid, name, address, city, state, review_count)VALUES(20, 'BAP', '1224 South St', 'Philadelphia', 'PA', 205);
CREATE TABLE `user` (
`uid` int NOT NULL AUTO_INCREMENT,
`user_id` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`review_count` int NOT NULL,
PRIMARY KEY (`uid`)
);
INSERT INTO user(uid, user_id, name, review_count)VALUES(1, 'qVc8ODYU5SZjKXVBgXdI7w', 'Walker', 585);
INSERT INTO user(uid, user_id, name, review_count)VALUES(2, 'j14WgRoU_-2ZE1aw1dXrJg', 'Daniel', 4333);
INSERT INTO user(uid, user_id, name, review_count)VALUES(3, '2WnXYQFK0hXEoTxPtV2zvg', 'Steph', 665);
INSERT INTO user(uid, user_id, name, review_count)VALUES(4, 'SZDeASXq7o05mMNLshsdIA', 'Gwen', 224);
INSERT INTO user(uid, user_id, name, review_count)VALUES(5, 'hA5lMy-EnncsH4JoR-hFGQ', 'Karen', 79);
INSERT INTO user(uid, user_id, name, review_count)VALUES(6, 'q_QQ5kBBwlCcbL1s4NVK3g', 'Jane', 1221);
INSERT INTO user(uid, user_id, name, review_count)VALUES(7, 'cxuxXkcihfCbqt5Byrup8Q', 'Rob', 12);
INSERT INTO user(uid, user_id, name, review_count)VALUES(8, 'E9kcWJdJUHuTKfQurPljwA', 'Mike', 358);
INSERT INTO user(uid, user_id, name, review_count)VALUES(9, 'lO1iq-f75hnPNZkTy3Zerg', 'Rachelle', 40);
INSERT INTO user(uid, user_id, name, review_count)VALUES(10, 'AUi8MPWJ0mLkMfwbui27lg', 'John', 109);
INSERT INTO user(uid, user_id, name, review_count)VALUES(11, 'iYzhPPqnrjJkg1JHZyMhzA', 'Chris', 4);
INSERT INTO user(uid, user_id, name, review_count)VALUES(12, 'xoZvMJPDW6Q9pDAXI0e_Ww', 'Ryan', 535);
INSERT INTO user(uid, user_id, name, review_count)VALUES(13, 'vVukUtqoLF5BvH_VtQFNoA', 'Charlene', 37);
INSERT INTO user(uid, user_id, name, review_count)VALUES(14, '_crIokUeTCHVK_JVOy-0qQ', 'Kenny', 11);
INSERT INTO user(uid, user_id, name, review_count)VALUES(15, '1McG5Rn_UDkmlkZOrsdptg', 'Teresa', 7);
INSERT INTO user(uid, user_id, name, review_count)VALUES(16, 'SgiBkhXeqIKl1PlFpZOycQ', 'Eugene', 682);
INSERT INTO user(uid, user_id, name, review_count)VALUES(17, 'fJZO_skqpnhk1kvomI4dmA', 'Jennifer', 25);
INSERT INTO user(uid, user_id, name, review_count)VALUES(18, 'x7YtLnBW2dUnrrpwaofVQQ', 'Ronskee', 37);
INSERT INTO user(uid, user_id, name, review_count)VALUES(19, 'QF1Kuhs8iwLWANNZxebTow', 'Catherine', 607);
INSERT INTO user(uid, user_id, name, review_count)VALUES(20, 'VcLRGCG_VbAo8MxOm76jzA', 'AJ', 133);
CREATE TABLE `category` (
`id` int NOT NULL AUTO_INCREMENT,
`business_id` varchar(255) NOT NULL,
`category_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO category(id, business_id, category_name)VALUES(1, 'Pns2l4eNsfO8kk83dixA6A', 'Doctors');
INSERT INTO category(id, business_id, category_name)VALUES(2, 'Pns2l4eNsfO8kk83dixA6A', 'Traditional Chinese Medicine');
INSERT INTO category(id, business_id, category_name)VALUES(3, 'Pns2l4eNsfO8kk83dixA6A', 'Naturopathic/Holistic');
INSERT INTO category(id, business_id, category_name)VALUES(4, 'Pns2l4eNsfO8kk83dixA6A', 'Acupuncture');
INSERT INTO category(id, business_id, category_name)VALUES(5, 'Pns2l4eNsfO8kk83dixA6A', 'Health & Medical');
INSERT INTO category(id, business_id, category_name)VALUES(6, 'Pns2l4eNsfO8kk83dixA6A', 'Nutritionists');
INSERT INTO category(id, business_id, category_name)VALUES(7, 'mpf3x-BjTdTEA3yCZrAYPw', 'Shipping Centers');
INSERT INTO category(id, business_id, category_name)VALUES(8, 'mpf3x-BjTdTEA3yCZrAYPw', 'Local Services');
INSERT INTO category(id, business_id, category_name)VALUES(9, 'mpf3x-BjTdTEA3yCZrAYPw', 'Notaries');
INSERT INTO category(id, business_id, category_name)VALUES(10, 'mpf3x-BjTdTEA3yCZrAYPw', 'Mailbox Centers');
INSERT INTO category(id, business_id, category_name)VALUES(11, 'mpf3x-BjTdTEA3yCZrAYPw', 'Printing Services');
INSERT INTO category(id, business_id, category_name)VALUES(12, 'tUFrWirKiKi_TAnsVWINQQ', 'Department Stores');
INSERT INTO category(id, business_id, category_name)VALUES(13, 'tUFrWirKiKi_TAnsVWINQQ', 'Shopping');
INSERT INTO category(id, business_id, category_name)VALUES(14, 'tUFrWirKiKi_TAnsVWINQQ', 'Fashion');
INSERT INTO category(id, business_id, category_name)VALUES(15, 'tUFrWirKiKi_TAnsVWINQQ', 'Home & Garden');
INSERT INTO category(id, business_id, category_name)VALUES(16, 'tUFrWirKiKi_TAnsVWINQQ', 'Electronics');
INSERT INTO category(id, business_id, category_name)VALUES(17, 'tUFrWirKiKi_TAnsVWINQQ', 'Furniture Stores');
INSERT INTO category(id, business_id, category_name)VALUES(18, 'MTSW4McQd7CbVtyjqoe9mw', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(19, 'MTSW4McQd7CbVtyjqoe9mw', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(20, 'MTSW4McQd7CbVtyjqoe9mw', 'Bubble Tea');
INSERT INTO category(id, business_id, category_name)VALUES(21, 'MTSW4McQd7CbVtyjqoe9mw', 'Coffee & Tea');
INSERT INTO category(id, business_id, category_name)VALUES(22, 'MTSW4McQd7CbVtyjqoe9mw', 'Bakeries');
INSERT INTO category(id, business_id, category_name)VALUES(23, 'mWMc6_wTdE0EUBKIGXDVfA', 'Brewpubs');
INSERT INTO category(id, business_id, category_name)VALUES(24, 'mWMc6_wTdE0EUBKIGXDVfA', 'Breweries');
INSERT INTO category(id, business_id, category_name)VALUES(25, 'mWMc6_wTdE0EUBKIGXDVfA', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(26, 'CF33F8-E6oudUQ46HnavjQ', 'Burgers');
INSERT INTO category(id, business_id, category_name)VALUES(27, 'CF33F8-E6oudUQ46HnavjQ', 'Fast Food');
INSERT INTO category(id, business_id, category_name)VALUES(28, 'CF33F8-E6oudUQ46HnavjQ', 'Sandwiches');
INSERT INTO category(id, business_id, category_name)VALUES(29, 'CF33F8-E6oudUQ46HnavjQ', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(30, 'CF33F8-E6oudUQ46HnavjQ', 'Ice Cream & Frozen Yogurt');
INSERT INTO category(id, business_id, category_name)VALUES(31, 'CF33F8-E6oudUQ46HnavjQ', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(32, 'n_0UpQx1hsNbnPUSlodU8w', 'Sporting Goods');
INSERT INTO category(id, business_id, category_name)VALUES(33, 'n_0UpQx1hsNbnPUSlodU8w', 'Fashion');
INSERT INTO category(id, business_id, category_name)VALUES(34, 'n_0UpQx1hsNbnPUSlodU8w', 'Shoe Stores');
INSERT INTO category(id, business_id, category_name)VALUES(35, 'n_0UpQx1hsNbnPUSlodU8w', 'Shopping');
INSERT INTO category(id, business_id, category_name)VALUES(36, 'n_0UpQx1hsNbnPUSlodU8w', 'Sports Wear');
INSERT INTO category(id, business_id, category_name)VALUES(37, 'n_0UpQx1hsNbnPUSlodU8w', 'Accessories');
INSERT INTO category(id, business_id, category_name)VALUES(38, 'qkRM_2X51Yqxk3btlwAQIg', 'Synagogues');
INSERT INTO category(id, business_id, category_name)VALUES(39, 'qkRM_2X51Yqxk3btlwAQIg', 'Religious Organizations');
INSERT INTO category(id, business_id, category_name)VALUES(40, 'k0hlBqXX-Bt0vf1op7Jr1w', 'Pubs');
INSERT INTO category(id, business_id, category_name)VALUES(41, 'k0hlBqXX-Bt0vf1op7Jr1w', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(42, 'k0hlBqXX-Bt0vf1op7Jr1w', 'Italian');
INSERT INTO category(id, business_id, category_name)VALUES(43, 'k0hlBqXX-Bt0vf1op7Jr1w', 'Bars');
INSERT INTO category(id, business_id, category_name)VALUES(44, 'k0hlBqXX-Bt0vf1op7Jr1w', 'American (Traditional)');
INSERT INTO category(id, business_id, category_name)VALUES(45, 'k0hlBqXX-Bt0vf1op7Jr1w', 'Nightlife');
INSERT INTO category(id, business_id, category_name)VALUES(46, 'k0hlBqXX-Bt0vf1op7Jr1w', 'Greek');
INSERT INTO category(id, business_id, category_name)VALUES(47, 'bBDDEgkFA1Otx9Lfe7BZUQ', 'Ice Cream & Frozen Yogurt');
INSERT INTO category(id, business_id, category_name)VALUES(48, 'bBDDEgkFA1Otx9Lfe7BZUQ', 'Fast Food');
INSERT INTO category(id, business_id, category_name)VALUES(49, 'bBDDEgkFA1Otx9Lfe7BZUQ', 'Burgers');
INSERT INTO category(id, business_id, category_name)VALUES(50, 'bBDDEgkFA1Otx9Lfe7BZUQ', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(51, 'bBDDEgkFA1Otx9Lfe7BZUQ', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(52, 'UJsufbvfyfONHeWdvAHKjA', 'Department Stores');
INSERT INTO category(id, business_id, category_name)VALUES(53, 'UJsufbvfyfONHeWdvAHKjA', 'Shopping');
INSERT INTO category(id, business_id, category_name)VALUES(54, 'UJsufbvfyfONHeWdvAHKjA', 'Fashion');
INSERT INTO category(id, business_id, category_name)VALUES(55, 'eEOYSgkmpB90uNA7lDOMRA', 'Vietnamese');
INSERT INTO category(id, business_id, category_name)VALUES(56, 'eEOYSgkmpB90uNA7lDOMRA', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(57, 'eEOYSgkmpB90uNA7lDOMRA', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(58, 'eEOYSgkmpB90uNA7lDOMRA', 'Food Trucks');
INSERT INTO category(id, business_id, category_name)VALUES(59, 'il_Ro8jwPlHresjw9EGmBg', 'American (Traditional)');
INSERT INTO category(id, business_id, category_name)VALUES(60, 'il_Ro8jwPlHresjw9EGmBg', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(61, 'il_Ro8jwPlHresjw9EGmBg', 'Diners');
INSERT INTO category(id, business_id, category_name)VALUES(62, 'il_Ro8jwPlHresjw9EGmBg', 'Breakfast & Brunch');
INSERT INTO category(id, business_id, category_name)VALUES(63, 'jaxMSoInw8Poo3XeMJt8lQ', 'General Dentistry');
INSERT INTO category(id, business_id, category_name)VALUES(64, 'jaxMSoInw8Poo3XeMJt8lQ', 'Dentists');
INSERT INTO category(id, business_id, category_name)VALUES(65, 'jaxMSoInw8Poo3XeMJt8lQ', 'Health & Medical');
INSERT INTO category(id, business_id, category_name)VALUES(66, 'jaxMSoInw8Poo3XeMJt8lQ', 'Cosmetic Dentists');
INSERT INTO category(id, business_id, category_name)VALUES(67, '0bPLkL0QhhPO5kt1_EXmNQ', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(68, '0bPLkL0QhhPO5kt1_EXmNQ', 'Delis');
INSERT INTO category(id, business_id, category_name)VALUES(69, '0bPLkL0QhhPO5kt1_EXmNQ', 'Italian');
INSERT INTO category(id, business_id, category_name)VALUES(70, '0bPLkL0QhhPO5kt1_EXmNQ', 'Bakeries');
INSERT INTO category(id, business_id, category_name)VALUES(71, '0bPLkL0QhhPO5kt1_EXmNQ', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(72, 'MUTTqe8uqyMdBl186RmNeA', 'Sushi Bars');
INSERT INTO category(id, business_id, category_name)VALUES(73, 'MUTTqe8uqyMdBl186RmNeA', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(74, 'MUTTqe8uqyMdBl186RmNeA', 'Japanese');
INSERT INTO category(id, business_id, category_name)VALUES(75, 'rBmpy_Y1UbBx8ggHlyb7hA', 'Automotive');
INSERT INTO category(id, business_id, category_name)VALUES(76, 'rBmpy_Y1UbBx8ggHlyb7hA', 'Auto Parts & Supplies');
INSERT INTO category(id, business_id, category_name)VALUES(77, 'rBmpy_Y1UbBx8ggHlyb7hA', 'Auto Customization');
INSERT INTO category(id, business_id, category_name)VALUES(78, 'M0XSSHqrASOnhgbWDJIpQA', 'Vape Shops');
INSERT INTO category(id, business_id, category_name)VALUES(79, 'M0XSSHqrASOnhgbWDJIpQA', 'Tobacco Shops');
INSERT INTO category(id, business_id, category_name)VALUES(80, 'M0XSSHqrASOnhgbWDJIpQA', 'Personal Shopping');
INSERT INTO category(id, business_id, category_name)VALUES(81, 'M0XSSHqrASOnhgbWDJIpQA', 'Vitamins & Supplements');
INSERT INTO category(id, business_id, category_name)VALUES(82, 'M0XSSHqrASOnhgbWDJIpQA', 'Shopping');
INSERT INTO category(id, business_id, category_name)VALUES(83, '8wGISYjYkE2tSqn3cDMu8A', 'Automotive');
INSERT INTO category(id, business_id, category_name)VALUES(84, '8wGISYjYkE2tSqn3cDMu8A', 'Car Rental');
INSERT INTO category(id, business_id, category_name)VALUES(85, '8wGISYjYkE2tSqn3cDMu8A', 'Hotels & Travel');
INSERT INTO category(id, business_id, category_name)VALUES(86, '8wGISYjYkE2tSqn3cDMu8A', 'Truck Rental');
INSERT INTO category(id, business_id, category_name)VALUES(87, 'ROeacJQwBeh05Rqg7F6TCg', 'Korean');
INSERT INTO category(id, business_id, category_name)VALUES(88, 'ROeacJQwBeh05Rqg7F6TCg', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(89, 'WKMJwqnfZKsAae75RMP6jA', 'Coffee & Tea');
INSERT INTO category(id, business_id, category_name)VALUES(90, 'WKMJwqnfZKsAae75RMP6jA', 'Food');
INSERT INTO category(id, business_id, category_name)VALUES(91, 'WKMJwqnfZKsAae75RMP6jA', 'Cafes');
INSERT INTO category(id, business_id, category_name)VALUES(92, 'WKMJwqnfZKsAae75RMP6jA', 'Bars');
INSERT INTO category(id, business_id, category_name)VALUES(93, 'WKMJwqnfZKsAae75RMP6jA', 'Wine Bars');
INSERT INTO category(id, business_id, category_name)VALUES(94, 'WKMJwqnfZKsAae75RMP6jA', 'Restaurants');
INSERT INTO category(id, business_id, category_name)VALUES(95, 'WKMJwqnfZKsAae75RMP6jA', 'Nightlife');
INSERT INTO category(id, business_id, category_name)VALUES(96, 'qhDdDeI3K4jy2KyzwFN53w', 'Shopping');
INSERT INTO category(id, business_id, category_name)VALUES(97, 'qhDdDeI3K4jy2KyzwFN53w', 'Books');
INSERT INTO category(id, business_id, category_name)VALUES(98, 'qhDdDeI3K4jy2KyzwFN53w', 'Mags');
Query 1:
For each user having at least 500 restaurant reviews in total (hint: filter on category = 'Restaurants'), list the user's uid, name, total number of reviews, and the number of distinct states they reviewed restaurants in. Sort the listed users in decreasing order of the number of distinct states. When tied for the number of distinct states, sort in decreasing order of the number of reviews. Limit results to the top 3 rows.
My effort:
SELECT id, name, review_count, state
FROM (
SELECT id , name , review_count, state, rank ( )OVER (PARTITION BY state ORDER BY review_countDESC)
FROM (
SELECT b.bid, b.name, count (*), b.state
FROM business AS b, user AS u
WHERE b.bid = u.uid
AND b.bid IN (
SELECT id
FROM category
WHERE category_name = 'Restaurants'
)
The result should resemble the following:
uid name num_states num_reviews
4852 Peter 7 666
38905 Craig 7 567
198824 Michelle 6 1144
Query 2:
For each of the 3 users you found in subpart a, what is their home state (the state having the most number of restaurant reviews by that user)?
To answer this question, need to write a SQL query to report the total number of restaurant reviews each of these 3 users wrote in each state. Sort the results by uid (ascending order) and then by the number of reviews (in descending order). The result should resemble the following (with a lot more rows):
uid state num_reviews
4852 IN 625
4852 FL 19
... ... ...
... ... ...