By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `customer_status` (
`status_id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`rank_` int DEFAULT NULL,
PRIMARY KEY (`status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `customer_status` VALUES (1,'Contact',1),(2,'Lead',2),(3,'Promising Lead',3),(4,'Prospect',4),(5,'Customer',5);
Records: 5 Duplicates: 0 Warnings: 0
CREATE TABLE `customer` (
`customer_id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`status_id` int DEFAULT NULL,
PRIMARY KEY (`customer_id`),
KEY `fk_customer_status` (`status_id`),
CONSTRAINT `fk_customer_status` FOREIGN KEY (`status_id`) REFERENCES `customer_status` (`status_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `customer` VALUES (1,'Angelika',5),(2,'Venice',5),(3,'Monaliza',5),(4,'Alliah',5),(5,'Allysa',5),(6,'Nathalie',5),(7,'Glen',5),(8,'Daisy',5),(9,'James',5),(10,'Mark',5);
Records: 10 Duplicates: 0 Warnings: 0
CREATE TABLE `sales_team` (
`team_id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`team_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `sales_team` VALUES (1,'Alpha'),(2,'Bravo'),(3,'Charlie');
Records: 3 Duplicates: 0 Warnings: 0
CREATE TABLE `sales_agent` (
`agent_id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`on_probation` tinyint(1) DEFAULT NULL,
`team_id` int DEFAULT NULL,
PRIMARY KEY (`agent_id`),
KEY `fk_sales_team` (`team_id`),
CONSTRAINT `fk_sales_team` FOREIGN KEY (`team_id`) REFERENCES `sales_team` (`team_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `sales_agent` VALUES (1,'Galpo',1,1),(2,'Rosita',1,2),(3,'Harry',0,1),(4,'Erika',0,2),(5,'Reiner',0,NULL);
Records: 5 Duplicates: 0 Warnings: 0
CREATE TABLE `order_` (
`order_id` int NOT NULL AUTO_INCREMENT,
`customer_id` int NOT NULL,
`agent_id` int NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`order_id`),
KEY `fk_customer` (`customer_id`),
KEY `fk_sales_agent` (`agent_id`),
CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_sales_agent` FOREIGN KEY (`agent_id`) REFERENCES `sales_agent` (`agent_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `order_` VALUES (1,1,1,'2020-01-03'),(2,2,3,'2020-01-07'),(3,3,2,'2020-01-08'),(4,4,1,'2020-01-09'),(5,5,4,'2020-01-11'),(6,6,3,'2020-01-15'),(7,7,2,'2020-01-17'),(8,8,3,'2020-01-21'),(9,9,4,'2020-01-29'),(10,10,2,'2020-01-30');
Records: 10 Duplicates: 0 Warnings: 0
CREATE TABLE `product_category` (
`category_id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `product_category` VALUES (1,'Food'),(2,'Non-Food');
Records: 2 Duplicates: 0 Warnings: 0
CREATE TABLE `product` (
`product_id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`price` decimal(10,2) DEFAULT NULL,
`category_id` int NOT NULL,
PRIMARY KEY (`product_id`),
KEY `fk_product_category` (`category_id`),
CONSTRAINT `fk_product_category` FOREIGN KEY (`category_id`) REFERENCES `product_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `product` VALUES (1,'soda',10.00,1),(2,'liquor',120.00,1),(3,'lemon',20.00,1),(4,'mango',35.00,1),(5,'Inhaler',45.00,2);
SELECT * FROM product;
Records: 5 Duplicates: 0 Warnings: 0
| product_id | name | price | category_id |
|---|---|---|---|
| 1 | soda | 10.00 | 1 |
| 2 | liquor | 120.00 | 1 |
| 3 | lemon | 20.00 | 1 |
| 4 | mango | 35.00 | 1 |
| 5 | Inhaler | 45.00 | 2 |
CREATE TABLE `line_item` (
`item_id` int NOT NULL AUTO_INCREMENT,
`order_id` int NOT NULL,
`product_id` int NOT NULL,
`quantity` int NOT NULL,
PRIMARY KEY (`item_id`),
KEY `fk_order_` (`order_id`),
KEY `fk_product` (`product_id`),
CONSTRAINT `fk_order_` FOREIGN KEY (`order_id`) REFERENCES `order_` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_product` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `line_item` VALUES (1,1,1,6),(2,1,5,34),(3,2,3,24),(4,2,1,4),(5,2,4,35),(6,3,1,13),(7,3,2,19),(8,4,4,30),(9,4,2,9),(10,5,2,14),(11,5,3,16),(12,6,4,21),(13,6,1,8),(14,7,5,33),(15,8,3,20),(16,8,5,32),(17,9,4,26),(18,10,2,15),(19,10,3,17),(20,10,5,7);
SELECT * FROM line_item;
Records: 20 Duplicates: 0 Warnings: 0
| item_id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 1 | 6 |
| 2 | 1 | 5 | 34 |
| 3 | 2 | 3 | 24 |
| 4 | 2 | 1 | 4 |
| 5 | 2 | 4 | 35 |
| 6 | 3 | 1 | 13 |
| 7 | 3 | 2 | 19 |
| 8 | 4 | 4 | 30 |
| 9 | 4 | 2 | 9 |
| 10 | 5 | 2 | 14 |
| 11 | 5 | 3 | 16 |
| 12 | 6 | 4 | 21 |
| 13 | 6 | 1 | 8 |
| 14 | 7 | 5 | 33 |
| 15 | 8 | 3 | 20 |
| 16 | 8 | 5 | 32 |
| 17 | 9 | 4 | 26 |
| 18 | 10 | 2 | 15 |
| 19 | 10 | 3 | 17 |
| 20 | 10 | 5 | 7 |
SELECT CONCAT(ranges.lo, ' - ', ranges.hi) Revenue,
COUNT(*) `Count`
FROM line_item
JOIN product USING (product_id)
JOIN ( SELECT 1 lo, 500 hi UNION
SELECT 501 , 1000 UNION
SELECT 1001 , 1500 UNION
SELECT 1501 , 2000 UNION
SELECT 2001 , 2500 ) ranges ON product.price * line_item.quantity BETWEEN ranges.lo AND ranges.hi
GROUP BY ranges.lo, ranges.hi;
| Revenue | Count |
|---|---|
| 1 - 500 | 9 |
| 501 - 1000 | 2 |
| 1001 - 1500 | 5 |
| 1501 - 2000 | 3 |
| 2001 - 2500 | 1 |