SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE runners ( "runner_id" INTEGER, "registration_date" DATE ); INSERT INTO runners ("runner_id", "registration_date") VALUES (1, '2021-01-01'), (2, '2021-01-03'), (3, '2021-01-08'), (4, '2021-01-15'); CREATE TABLE customer_orders ( "order_id" INTEGER, "customer_id" INTEGER, "pizza_id" INTEGER, "exclusions" VARCHAR(4), "extras" VARCHAR(10), "order_time" TIMESTAMP ); 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'); CREATE TABLE runner_orders ( "order_id" INTEGER, "runner_id" INTEGER, "pickup_time" VARCHAR(19), "distance" VARCHAR(7), "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', '20km', '32 minutes', ''), ('2', '1', '2020-01-01 19:10:54', '20km', '27 minutes', ''), ('3', '1', '2020-01-03 00:12:37', '13.4km', '20 mins', 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', '25km', '25mins', 'null'), ('8', '2', '2020-01-10 00:15:02', '23.4 km', '15 minute', 'null'), ('9', '2', 'null', 'null', 'null', 'Customer Cancellation'), ('10', '1', '2020-01-11 18:50:20', '10km', '10minutes', 'null'); CREATE TABLE pizza_names ( "pizza_id" INTEGER, "pizza_name" TEXT ); INSERT INTO pizza_names ("pizza_id", "pizza_name") VALUES (1, 'Meatlovers'), (2, 'Vegetarian'); CREATE TABLE pizza_recipes ( "pizza_id" INTEGER, "toppings" TEXT ); INSERT INTO pizza_recipes ("pizza_id", "toppings") VALUES (1, '1, 2, 3, 4, 5, 6, 8, 10'), (2, '4, 6, 7, 9, 11, 12'); CREATE TABLE pizza_toppings ( "topping_id" INTEGER, "topping_name" TEXT ); INSERT INTO pizza_toppings ("topping_id", "topping_name") VALUES (1, 'Bacon'), (2, 'BBQ Sauce'), (3, 'Beef'), (4, 'Cheese'), (5, 'Chicken'), (6, 'Mushrooms'), (7, 'Onions'), (8, 'Pepperoni'), (9, 'Peppers'), (10, 'Salami'), (11, 'Tomatoes'), (12, 'Tomato Sauce'); update customer_orders set exclusions = 0 where exclusions = '' or exclusions ilike '%null%'; update customer_orders set extras = 0 where extras = '' or extras ilike '%null%' or exclusions ~* '(null)'; update customer_orders set extras = 0 where extras is NULL; update runner_orders set distance = 0 where distance is null or distance ilike '%null%'; update runner_orders set cancellation = '' where cancellation is NULL or cancellation ilike '%null%'; update runner_orders set duration = 0 where duration = 'null'; --select pizza_id,unnest(string_to_array(toppings,',')) from pizza_recipes; with result as( select distinct customer_id,pizza_name,count(customer_id) over(partition by customer_id,pizza_name) as pizza_count from customer_orders c join pizza_names p on c.pizza_id=p.pizza_id order by 1) update runner_orders set pickup_time = '0000-00-00 00:00:00' where pickup_time = 'null'; --select extract(MINUTE FROM order_time) as ordertime,extract(MINUTE FROM pickup_time) as pickuptime --from customer_orders co join runner_orders ro on co.order_id=ro.order_id; select date_part('minute',order_time::TIMESTAMP),date_part('minute',pickup_time::TIMESTAMP) from customer_orders co join runner_orders ro on co.order_id=ro.order_id; select NULLIF(pickup_time,'0000-00-00 00:00:00')::timestamp from runner_orders;