w3resource

SQL Challenges-1: Find active customers

SQL Challenges-1: Exercise-10 with Solution

An active customer is simply someone who has bought company’s product once before and has returned to make another purchase within 10 days.
From the following table, write a SQL query to identify the active customers. Show the list of customer IDs of active customers.

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
1072122cookies 2021-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
1202100cookies2019-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-20
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-12
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 * FROM orders;

SELECT DISTINCT a.customer_id
FROM orders a, orders  b
where (a.customer_id=b.customer_id) AND (a.order_id!=b.order_id) AND (b.order_date - a.order_date) BETWEEN 0 AND 10
ORDER BY customer_id;

Sample Output:

customer_id|
-----------|
       2103|
       2110|
       2111|
       2112|
       2129|
       2130|

Relational Algebra Expression:

Relational Algebra Expression: Consecutive Numbers.

Relational Algebra Tree:

Relational Algebra Tree: Consecutive Numbers.

Solution-1:

SELECT DISTINCT(a.customer_id) FROM orders a
JOIN orders b
ON a.customer_id = b.customer_id AND a.order_id != b.order_id
WHERE b.order_date - a.order_date  BETWEEN 0 AND 3
ORDER BY customer_id;

Solution-2:

select distinct(a.customer_id) from orders a  inner join orders b on
a.customer_id = b.customer_id and 
a.order_id <> b.order_id and  
b.order_date between a.order_date and a.order_date+10

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Highest Sale Amount.
Next: Convert negative numbers to positive and vice verse.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8