SQL Challenges-1: Find the highest daily total order for an item

Last update on December 20 2024 11:59:07 (UTC/GMT +8 hours)

From the following table write a query in SQL to find the highest daily total order for an item between 2019-07-01 to 2019-12-31. Return item description, order date and the total order quantity.

Table: orders

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS orders;
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,
order_qty integer(4) not null,
PRIMARY KEY (ORDER_ID)
);

insert into orders values(101	,2109	,'juice		','2019-07-21',	50);
insert into orders values(102	,2139	,'chocolate	','2019-05-17',	40);
insert into orders values(103	,2120	,'juice		','2019-05-17',	40);
insert into orders values(104	,2108	,'cookies		','2019-05-17',	50);
insert into orders values(105	,2130	,'juice		','2019-10-18',	45);
insert into orders values(106	,2103	,'cake		','2019-07-21',	35);
insert into orders values(107	,2122	,'cookies		','2019-12-17',	40);
insert into orders values(108	,2125	,'cake		','2019-12-17',	38);
insert into orders values(109	,2139	,'cake		','2019-07-21',	40);
insert into orders values(110	,2141	,'cookies		','2019-05-17',	60);
insert into orders values(111	,2116	,'cake		','2019-10-18',	45);
insert into orders values(112	,2128	,'cake		','2019-10-18',	38);
insert into orders values(113	,2146	,'chocolate	','2019-10-18',	55);
insert into orders values(114	,2119	,'cookies		','2019-10-18',	30);
insert into orders values(115	,2142	,'cake		','2019-03-05',	26);
insert into orders values(116	,2122	,'cake		','2019-03-05',	59);
insert into orders values(117	,2128	,'chocolate	','2019-06-19',	45);
insert into orders values(118	,2112	,'cookies		','2019-06-19',	28);
insert into orders values(119	,2149	,'cookies		','2019-10-18',	49);
insert into orders values(120	,2100	,'cookies		','2020-03-14',	76);
insert into orders values(121	,2130	,'juice		','2020-03-14',	20);
insert into orders values(122	,2103	,'juice		','2019-07-21',	27);
insert into orders values(123	,2112	,'cookies		','2019-06-19',	52);
insert into orders values(124	,2102	,'cake		','2019-07-21',	14);
insert into orders values(125	,2120	,'chocolate	','2019-07-21',	85);
insert into orders values(126	,2109	,'cake		','2019-06-19',	18);
insert into orders values(127	,2101	,'juice		','2019-10-18',	64);
insert into orders values(128	,2138	,'juice		','2019-06-19',	55);
insert into orders values(129	,2100	,'juice		','2019-07-21',	45);
insert into orders values(130	,2129	,'juice		','2019-10-18',	35);
insert into orders values(131	,2123	,'juice		','2019-10-18',	25);
insert into orders values(132	,2104	,'chocolate	','2019-10-18',	63);
insert into orders values(133	,2110	,'cake		','2019-12-17',	38);
insert into orders values(134	,2143	,'cake		','2019-06-19',	27);
insert into orders values(135	,2130	,'juice		','2019-05-17',	30);
insert into orders values(136	,2128	,'juice		','2019-10-18',	40);
insert into orders values(137	,2133	,'cookies		','2019-06-19',	51);
insert into orders values(138	,2150	,'cookies		','2019-06-19',	43);
insert into orders values(139	,2120	,'juice		','2019-10-18',	55);
insert into orders values(140	,2109	,'cake		','2019-10-18',	44);
insert into orders values(141	,2110	,'cake		','2019-12-17',	50);
insert into orders values(142	,2140	,'juice		','2019-03-05',	65);
insert into orders values(143	,2112	,'cookies		','2019-10-18',	46);
insert into orders values(144	,2117	,'chocolate	','2019-06-19',	25);
insert into orders values(145	,2137	,'cookies		','2019-07-21',	49);
insert into orders values(146	,2130	,'cake		','2019-12-17',	40);
insert into orders values(147	,2133	,'cake		','2019-07-21',	30);
insert into orders values(148	,2143	,'juice		','2019-03-05',	60);
insert into orders values(149	,2111	,'chocolate	','2019-07-21',	74);
insert into orders values(150	,2150	,'cookies		','2019-10-18',	75);
insert into orders values(151	,2131	,'cake		','2019-07-21',	45);
insert into orders values(152	,2140	,'chocolate	','2019-05-17',	40);
insert into orders values(153	,2147	,'cookies		','2019-07-21',	65);
insert into orders values(154	,2119	,'chocolate	','2019-05-17',	60);
insert into orders values(155	,2116	,'juice		','2019-12-17',	40);
insert into orders values(156	,2141	,'juice		','2019-12-17',	50);
insert into orders values(157	,2143	,'cake		','2019-05-17',	55);
insert into orders values(158	,2105	,'cake		','2019-07-21',	70);
insert into orders values(159	,2149	,'chocolate	','2019-03-05',	50);
insert into orders values(160	,2117	,'cookies		','2019-07-21',	61);
insert into orders values(161	,2150	,'cookies		','2019-07-21',	50);
insert into orders values(162	,2134	,'cake		','2019-03-05',	55);
insert into orders values(163	,2133	,'cookies		','2019-06-19',	54);
insert into orders values(164	,2127	,'juice		','2019-06-19',	55);
insert into orders values(165	,2101	,'juice		','2019-06-19',	45);
insert into orders values(166	,2137	,'chocolate	','2019-12-17',	40);
insert into orders values(167	,2113	,'chocolate	','2019-06-19',	30);
insert into orders values(168	,2141	,'cake		','2019-06-19',	60);
insert into orders values(169	,2112	,'chocolate	','2019-12-17',	25);
insert into orders values(170	,2118	,'juice		','2019-10-18',	70);
insert into orders values(171	,2111	,'juice		','2019-06-19',	60);
insert into orders values(172	,2146	,'chocolate	','2019-12-17',	20);
insert into orders values(173	,2148	,'cookies		','2020-03-14',	55);
insert into orders values(174	,2100	,'cookies		','2019-12-17',	40);
insert into orders values(175	,2105	,'cookies		','2019-03-05',	30);
insert into orders values(176	,2129	,'juice		','2019-10-18',	40);
insert into orders values(177	,2121	,'juice		','2019-05-17',	30);
insert into orders values(178	,2117	,'cake		','2019-07-21',	40);
insert into orders values(179	,2133	,'juice		','2019-07-21',	20);
insert into orders values(180	,2124	,'cake		','2019-07-21',	30);
insert into orders values(181	,2145	,'cake		','2019-12-17',	25);
insert into orders values(182	,2105	,'cookies		','2019-03-05',	20);
insert into orders values(183	,2131	,'juice		','2019-03-05',	50);
insert into orders values(184	,2114	,'chocolate	','2019-10-18',	56);
insert into orders values(185	,2120	,'juice		','2019-12-17',	55);
insert into orders values(186	,2130	,'juice		','2019-12-17',	46);
insert into orders values(187	,2141	,'chocolate	','2019-03-05',	54);
insert into orders values(188	,2147	,'cake		','2020-03-14',	40);
insert into orders values(189	,2118	,'juice		','2019-05-17',	54);
insert into orders values(190	,2136	,'chocolate	','2019-07-21',	58);
insert into orders values(191	,2132	,'cake		','2019-12-17',	50);
insert into orders values(192	,2137	,'chocolate	','2019-07-21',	53);
insert into orders values(193	,2107	,'cake		','2019-10-18',	35);
insert into orders values(194	,2111	,'chocolate	','2019-05-17',	50);
insert into orders values(195	,2100	,'cake		','2019-03-05',	55);
insert into orders values(196	,2106	,'juice		','2019-07-21',	47);
insert into orders values(197	,2114	,'cookies		','2019-06-19',	55);
insert into orders values(198	,2110	,'cake		','2019-06-19',	75);
insert into orders values(199	,2130	,'juice		','2019-05-17',	55);
insert into orders values(200	,2117	,'cake		','2019-12-17',	48);



SELECT item_desc,order_date,SUM(order_qty) dayorder 
FROM orders
WHERE order_date BETWEEN '2019-07-01' AND '2019-12-31'
GROUP BY item_desc,order_date
ORDER BY dayorder DESC
LIMIT 1;

Sample Output:

item_desc|order_date|daysOrder|
---------+----------+---------+
juice    |2019-10-18|      374|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find departments with 6 or more employees.
Next: Count the number of patients treated by each doctor.