add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE customer_orders (
"order_id" INTEGER,
"customer_id" INTEGER,
"pizza_id" INTEGER,
"exclusions" VARCHAR(4),
"extras" VARCHAR(4),
"order_time" DATETIME
);
INSERT INTO customer_orders
("order_id", "customer_id", "pizza_id", "exclusions", "extras", "order_time")
VALUES
('1', '101', '1', '', '', '2020-01-01 18:05:02'),
('2', '101', '1', '', '', '2020-01-01 19:00:52'),
('3', '102', '1', '', '', '2020-01-02 23:51:23'),
('3', '102', '2', '', NULL, '2020-01-02 23:51:23'),
('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
('4', '103', '2', '4', '', '2020-01-04 13:23:46'),
('5', '104', '1', NULL, '1', '2020-01-08 21:00:29'),
('6', '101', '2', NULL, NULL, '2020-01-08 21:03:13'),
('7', '105', '2', NULL, '1', '2020-01-08 21:20:29'),
('8', '102', '1', NULL, NULL, '2020-01-09 23:54:33'),
('9', '103', '1', '4', '1, 5', '2020-01-10 11:22:59'),
('10', '104', '1', NULL, NULL, '2020-01-11 18:34:49'),
('10', '104', '1', '2, 6', '1, 4', '2020-01-11 18:34:49');

SELECT * FROM customer_orders ORDER BY customer_id, order_id;
order_id customer_id pizza_id exclusions extras order_time
1 101 1 2020-01-01 18:05:02.000
2 101 1 2020-01-01 19:00:52.000
6 101 2 null null 2020-01-08 21:03:13.000
3 102 1 2020-01-02 23:51:23.000
3 102 2 null 2020-01-02 23:51:23.000
8 102 1 null null 2020-01-09 23:54:33.000
4 103 1 4 2020-01-04 13:23:46.000
4 103 1 4 2020-01-04 13:23:46.000
4 103 2 4 2020-01-04 13:23:46.000
9 103 1 4 1, 5 2020-01-10 11:22:59.000
5 104 1 null 1 2020-01-08 21:00:29.000
10 104 1 null null 2020-01-11 18:34:49.000
10 104 1 2, 6 1, 4 2020-01-11 18:34:49.000
7 105 2 null 1 2020-01-08 21:20:29.000
CREATE TABLE runner_orders (
"order_id" INTEGER,
"runner_id" INTEGER,
"pickup_time" VARCHAR(19),
"distance" DECIMAL(5,2) NULL,
"duration" VARCHAR(10),
"cancellation" VARCHAR(23)
);
INSERT INTO runner_orders
("order_id", "runner_id", "pickup_time", "distance", "duration", "cancellation")
VALUES
('1', '1', '2020-01-01 18:15:34', '20', '32', ''),
('2', '1', '2020-01-01 19:10:54', '20', '27', ''),
('3', '1', '2020-01-03 00:12:37', '13.4', '20', NULL),
('4', '2', '2020-01-04 13:53:03', '23.4', '40', NULL),
('5', '3', '2020-01-08 21:10:57', '10', '15', NULL),
('6', '3', NULL, NULL, NULL, 'Restaurant Cancellation'),
('7', '2', '2020-01-08 21:30:45', '25', '25mins', NULL),
('8', '2', '2020-01-10 00:15:02', '23.4', '15', NULL),
('9', '2', NULL, NULL, NULL, 'Customer Cancellation'),
('10', '1', '2020-01-11 18:50:20', '10', '10', NULL);

SELECT * FROM runner_orders
order_id runner_id pickup_time distance duration cancellation
1 1 2020-01-01 18:15:34 20.00 32
2 1 2020-01-01 19:10:54 20.00 27
3 1 2020-01-03 00:12:37 13.40 20 null
4 2 2020-01-04 13:53:03 23.40 40 null
5 3 2020-01-08 21:10:57 10.00 15 null
6 3 null null null Restaurant Cancellation
7 2 2020-01-08 21:30:45 25.00 25mins null
8 2 2020-01-10 00:15:02 23.40 15 null
9 2 null null null Customer Cancellation
10 1 2020-01-11 18:50:20 10.00 10 null
SELECT customer_id,
COUNT(CASE WHEN NOT COALESCE(extras, '') = ''
OR NOT COALESCE(exclusions, '') = '' THEN 1 END) AS num_with_changes,
COUNT(CASE WHEN COALESCE(extras, '') = ''
AND COALESCE(exclusions, '') = '' THEN 1 END) AS num_with_no_changes
FROM customer_orders co
WHERE NOT EXISTS(SELECT 1
FROM runner_orders ro
WHERE co.order_id = ro.order_id
AND NOT COALESCE(cancellation, '') = '')
GROUP BY customer_id
customer_id num_with_changes num_with_no_changes
101 0 2
102 0 3
103 3 0
104 2 1
105 1 0 
Warning: Null value is eliminated by an aggregate or other SET operation.