w3resource

SQL Challenges-1: Find customers booked orders more than 3 times

SQL Challenges-1: Exercise-60 with Solution

From the following table find customers who orders more than three times. Return customer ID and number of orders the customer booked.

Input:

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
ORDER_IDint(5)NOPRI
CUSTOMER_IDint(4)NO
ITEM_DESCvarchar(30)NO
ORDER_DATEdateNO

Data:

ORDER_IDCUSTOMER_IDITEM_DESCORDER_DATE
1012109juice2020-03-03
1022139chocolate2019-03-18
1032120juice2019-03-18
1042108cookies2019-03-18
1052130juice2020-03-28
1062103cake2019-03-29
1072122cookies2021-03-07
1082125cake2021-03-13
1092139cake2019-03-30
1102141cookies2019-03-17
1112116cake2020-03-31
1122128cake2021-03-04
1132146chocolate2021-03-04
1142119cookies2020-03-28
1152142cake2019-03-09
1162122cake2019-03-06
1172128chocolate2019-03-24
1182112cookies2019-03-24
1192149cookies2020-03-29
1202100cookies2021-03-18
1212130juice2021-03-16
1222103juice2019-03-31
1232112cookies2019-03-23
1242102cake2020-03-25
1252120chocolate2020-03-21
1262109cake2019-03-22
1272101juice2021-03-01
1282138juice2019-03-19
1292100juice2019-03-29
1302129juice2021-03-02
1312123juice2020-03-31
1322104chocolate2020-03-31
1332110cake2021-03-13
1342143cake2019-03-27
1352130juice2019-03-12
1362128juice2020-03-28
1372133cookies2019-03-21
1382150cookies2019-03-21
1392120juice2020-03-27
1402109cake2021-03-02
1412110cake2021-03-13
1422140juice2019-03-09
1432112cookies2021-03-04
1442117chocolate2019-03-19
1452137cookies2020-03-23
1462130cake2021-03-09
1472133cake2020-03-08
1482143juice2019-03-11
1492111chocolate2020-03-23
1502150cookies2021-03-04
1512131cake2020-03-10
1522140chocolate2019-03-17
1532147cookies2020-03-22
1542119chocolate2019-03-15
1552116juice2021-03-12
1562141juice2021-03-14
1572143cake2019-03-16
1582105cake2020-03-21
1592149chocolate2019-03-11
1602117cookies2020-03-22
1612150cookies2020-03-21
1622134cake2019-03-08
1632133cookies2019-03-26
1642127juice2019-03-27
1652101juice2019-03-26
1662137chocolate2021-03-21
1672113chocolate2019-03-21
1682141cake2019-03-21
1692112chocolate2021-03-14
1702118juice2020-03-30
1712111juice2019-03-19
1722146chocolate2021-03-13
1732148cookies2021-03-14
1742100cookies2021-03-13
1752105cookies2019-03-05
1762129juice2021-03-02
1772121juice2019-03-16
1782117cake2020-03-11
1792133juice2020-03-12
1802124cake2019-03-31
1812145cake2021-03-07
1822105cookies2019-03-09
1832131juice2019-03-09
1842114chocolate2020-03-31
1852120juice2021-03-06
1862130juice2021-03-06
1872141chocolate2019-03-11
1882147cake2021-03-14
1892118juice2019-03-15
1902136chocolate2020-03-22
1912132cake2021-03-06
1922137chocolate2019-03-31
1932107cake2021-03-01
1942111chocolate2019-03-18
1952100cake2019-03-07
1962106juice2020-03-21
1972114cookies2019-03-25
1982110cake2019-03-27
1992130juice2019-03-16
2002117cake2021-03-10

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS orders;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE orders (
ORDER_ID            INTEGER(5) NOT NULL,
CUSTOMER_ID 	            INTEGER(4) NOT NULL,
ITEM_DESC 	            varchar(30) NOT NULL,
ORDER_DATE               date NOT NULL,
PRIMARY KEY (ORDER_ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO orders VALUES(101,2109,'juice','2020-03-03');
INSERT INTO orders VALUES(102,2139,'chocolate','2019-03-18');
INSERT INTO orders VALUES(103,2120,'juice','2019-03-18');
INSERT INTO orders VALUES(104,2108,'cookies','2019-03-18');
INSERT INTO orders VALUES(105,2130,'juice','2020-03-28');
INSERT INTO orders VALUES(106,2103,'cake','2019-03-29');
INSERT INTO orders VALUES(107,2122,'cookies','2021-03-07');
INSERT INTO orders VALUES(108,2125,'cake','2021-03-13');
INSERT INTO orders VALUES(109,2139,'cake','2019-03-30');
INSERT INTO orders VALUES(110,2141,'cookies','2019-03-17');
INSERT INTO orders VALUES(111,2116,'cake','2020-03-31');
INSERT INTO orders VALUES(112,2128,'cake','2021-03-04');
INSERT INTO orders VALUES(113,2146,'chocolate','2021-03-04');
INSERT INTO orders VALUES(114,2119,'cookies','2020-03-28');
INSERT INTO orders VALUES(115,2142,'cake','2019-03-09');
INSERT INTO orders VALUES(116,2122,'cake','2019-03-06');
INSERT INTO orders VALUES(117,2128,'chocolate','2019-03-24');
INSERT INTO orders VALUES(118,2112,'cookies','2019-03-24');
INSERT INTO orders VALUES(119,2149,'cookies','2020-03-29');
INSERT INTO orders VALUES(120,2100,'cookies','2021-03-18');
INSERT INTO orders VALUES(121,2130,'juice','2021-03-16');
INSERT INTO orders VALUES(122,2103,'juice','2019-03-31');
INSERT INTO orders VALUES(123,2112,'cookies','2019-03-23');
INSERT INTO orders VALUES(124,2102,'cake','2020-03-25');
INSERT INTO orders VALUES(125,2120,'chocolate','2020-03-21');
INSERT INTO orders VALUES(126,2109,'cake','2019-03-22');
INSERT INTO orders VALUES(127,2101,'juice','2021-03-01');
INSERT INTO orders VALUES(128,2138,'juice','2019-03-19');
INSERT INTO orders VALUES(129,2100,'juice','2019-03-29');
INSERT INTO orders VALUES(130,2129,'juice','2021-03-02');
INSERT INTO orders VALUES(131,2123,'juice','2020-03-31');
INSERT INTO orders VALUES(132,2104,'chocolate','2020-03-31');
INSERT INTO orders VALUES(133,2110,'cake','2021-03-13');
INSERT INTO orders VALUES(134,2143,'cake','2019-03-27');
INSERT INTO orders VALUES(135,2130,'juice','2019-03-12');
INSERT INTO orders VALUES(136,2128,'juice','2020-03-28');
INSERT INTO orders VALUES(137,2133,'cookies','2019-03-21');
INSERT INTO orders VALUES(138,2150,'cookies','2019-03-20');
INSERT INTO orders VALUES(139,2120,'juice','2020-03-27');
INSERT INTO orders VALUES(140,2109,'cake','2021-03-02');
INSERT INTO orders VALUES(141,2110,'cake','2021-03-13');
INSERT INTO orders VALUES(142,2140,'juice','2019-03-09');
INSERT INTO orders VALUES(143,2112,'cookies','2021-03-04');
INSERT INTO orders VALUES(144,2117,'chocolate','2019-03-19');
INSERT INTO orders VALUES(145,2137,'cookies','2020-03-23');
INSERT INTO orders VALUES(146,2130,'cake','2021-03-09');
INSERT INTO orders VALUES(147,2133,'cake','2020-03-08');
INSERT INTO orders VALUES(148,2143,'juice','2019-03-11');
INSERT INTO orders VALUES(149,2111,'chocolate','2020-03-23');
INSERT INTO orders VALUES(150,2150,'cookies','2021-03-04');
INSERT INTO orders VALUES(151,2131,'cake','2020-03-10');
INSERT INTO orders VALUES(152,2140,'chocolate','2019-03-17');
INSERT INTO orders VALUES(153,2147,'cookies','2020-03-22');
INSERT INTO orders VALUES(154,2119,'chocolate','2019-03-15');
INSERT INTO orders VALUES(155,2116,'juice','2021-03-12');
INSERT INTO orders VALUES(156,2141,'juice','2021-03-14');
INSERT INTO orders VALUES(157,2143,'cake','2019-03-16');
INSERT INTO orders VALUES(158,2105,'cake','2020-03-21');
INSERT INTO orders VALUES(159,2149,'chocolate','2019-03-11');
INSERT INTO orders VALUES(160,2117,'cookies','2020-03-22');
INSERT INTO orders VALUES(161,2150,'cookies','2020-03-21');
INSERT INTO orders VALUES(162,2134,'cake','2019-03-08');
INSERT INTO orders VALUES(163,2133,'cookies','2019-03-26');
INSERT INTO orders VALUES(164,2127,'juice','2019-03-27');
INSERT INTO orders VALUES(165,2101,'juice','2019-03-26');
INSERT INTO orders VALUES(166,2137,'chocolate','2021-03-12');
INSERT INTO orders VALUES(167,2113,'chocolate','2019-03-21');
INSERT INTO orders VALUES(168,2141,'cake','2019-03-21');
INSERT INTO orders VALUES(169,2112,'chocolate','2021-03-14');
INSERT INTO orders VALUES(170,2118,'juice','2020-03-30');
INSERT INTO orders VALUES(171,2111,'juice','2019-03-19');
INSERT INTO orders VALUES(172,2146,'chocolate','2021-03-13');
INSERT INTO orders VALUES(173,2148,'cookies','2021-03-14');
INSERT INTO orders VALUES(174,2100,'cookies','2021-03-13');
INSERT INTO orders VALUES(175,2105,'cookies','2019-03-05');
INSERT INTO orders VALUES(176,2129,'juice','2021-03-02');
INSERT INTO orders VALUES(177,2121,'juice','2019-03-16');
INSERT INTO orders VALUES(178,2117,'cake','2020-03-11');
INSERT INTO orders VALUES(179,2133,'juice','2020-03-12');
INSERT INTO orders VALUES(180,2124,'cake','2019-03-31');
INSERT INTO orders VALUES(181,2145,'cake','2021-03-07');
INSERT INTO orders VALUES(182,2105,'cookies','2019-03-09');
INSERT INTO orders VALUES(183,2131,'juice','2019-03-09');
INSERT INTO orders VALUES(184,2114,'chocolate','2020-03-31');
INSERT INTO orders VALUES(185,2120,'juice','2021-03-06');
INSERT INTO orders VALUES(186,2130,'juice','2021-03-06');
INSERT INTO orders VALUES(187,2141,'chocolate','2019-03-11');
INSERT INTO orders VALUES(188,2147,'cake','2021-03-14');
INSERT INTO orders VALUES(189,2118,'juice','2019-03-15');
INSERT INTO orders VALUES(190,2136,'chocolate','2020-03-22');
INSERT INTO orders VALUES(191,2132,'cake','2021-03-06');
INSERT INTO orders VALUES(192,2137,'chocolate','2019-03-31');
INSERT INTO orders VALUES(193,2107,'cake','2021-03-01');
INSERT INTO orders VALUES(194,2111,'chocolate','2019-03-18');
INSERT INTO orders VALUES(195,2100,'cake','2019-03-07');
INSERT INTO orders VALUES(196,2106,'juice','2020-03-21');
INSERT INTO orders VALUES(197,2114,'cookies','2019-03-25');
INSERT INTO orders VALUES(198,2110,'cake','2019-03-27');
INSERT INTO orders VALUES(199,2130,'juice','2019-03-16');
INSERT INTO orders VALUES(200,2117,'cake','2021-03-10');


SELECT customer_id,COUNT(*) customer_appears  
FROM orders
GROUP BY customer_id
HAVING customer_appears>3;

Sample Output:

customer_id|customer_appears|
-----------+----------------+
       2120|               4|
       2130|               6|
       2141|               4|
       2112|               4|
       2100|               4|
       2133|               4|
       2117|               4|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Managers who can ordered more than four employees.
Next: Find departments with 6 or more employees.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/challenges-1/sql-challenges-1-exercise-60.php