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.



Follow us on Facebook and Twitter for latest update.