SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE orders ( order_id INT PRIMARY KEY, created_at TIMESTAMP, merchant_name VARCHAR(255), payment_method VARCHAR(255), total_spent NUMERIC, order_source VARCHAR(255), browser_ip VARCHAR(255), email VARCHAR(255), credit_card_bin VARCHAR(255), customer_id VARCHAR(255) ); CREATE TABLE products ( order_id INT, product_title VARCHAR(255), product_id VARCHAR(255), quantity NUMERIC, unit_price NUMERIC, FOREIGN KEY (order_id) REFERENCES orders(order_id) ); CREATE TABLE logs ( order_id INT, log_created_at TIMESTAMP, raw_log VARCHAR(255), FOREIGN KEY (order_id) REFERENCES orders(order_id) ); INSERT INTO orders (order_id, created_at, merchant_name, payment_method, total_spent, order_source, browser_ip, email, credit_card_bin, customer_id) VALUES (123, "2021-07-05 02:45:21", "merchant_111", "credit_card", 2398.67, "", "184.166.176.14", "don_draper@gmail.com", "427961", "1458690481"), (456, "2021-07-05 13:10:10", "merchant_111", "credit_card", 803.1, "desktop_web", "99.100.136.239", "michael.scott@yahoo.com", "453352", "4052154883"), (789, "2021-07-06 04:17:13", "merchant_125", "credit_card", 231.24, "", "69.46.227.226", "oliviapope123@gmail.com", "376748", "1512350021"), (567, "2021-07-06 05:40:45", "merchant_125", "paypal", 56.93, "mobile_web", "96.232.92.72", "q8b86ca8uq@privaterelay.appleid.com", "", "5253484495"), (345, "2021-07-06 11:20:23", "merchant_125", "credit_card", 67.08, "", "69.46.227.226", "walter_white@yahoo.com", "410064", "5200453016"), (987, "2021-07-06 17:24:45", "merchant_125", "giftcard", 798.16, "", "173.93.32.231", "sheldon.cooper345@gmail.com", "", "5116924222"), (902, "2021-07-01 13:17:34", "merchant_129", "paypal", 2412.21, "", "69.46.227.226", "larrydavid890@gmail.com", "", "4052154883"), (678, "2021-07-01 10:34:24", "merchant_129", "paypal", 202.34, "mobile_app", "96.240.128.181", "k2re2qdhct@privaterelay.appleid.com", "", "4319279403"), (907, "2021-07-07 05:46:39", "merchant_392", "credit_card", 164.53, "desktop_web", "98.148.216.218", "leslie_knope@gmail.com", "473522", "1512350021"), (478, "2021-07-07 05:37:27", "merchant_392", "credit_card", 59.76, "", "69.46.227.226", "tina.belcher@hotmail.com", "552302", "5253484495"), (637, "2021-07-08 06:23:11", "merchant_392", "credit_card", 738.82, "mobile_app", "74.73.225.210", "5ws425xdfg@privaterelay.appleid.com", "474437", "1512350021"); INSERT INTO products (order_id, product_title, product_id, quantity, unit_price) VALUES (123, "Queen Split Wood Box Spring", "4435206223", 2, 330.7), (123, "Standard Classic Bean Bag", "4825116219", 1, 67.08), (123, "Buena Park Sleigh Bed", "4825124229", 1, 2000.89), (456, "Plastic Adirondack Chair", "4907287661", 3, 803.1), (789, "Cement Pot Planter", "4691786229", 1, 78.61), (789, "Fortuna Pub Table", "4829868402", 1, 152.63), (567, "7 Piece Abner Picture Frame Set", "5001413696", 1, 56.93), (345, "Standard Classic Bean Bag", "4404169351", 1, 67.08), (987, "Flight Flat Crib Diaper Stacker", "4412875021", 2, 59.34), (987, "Newtown Desk", "3718692343", 1, 738.82), (902, "Emrich Outdoor Reclining Chaise Lounge", "4827012931", 2, 411.32), (902, "Buena Park Sleigh Bed", "4826754112", 1, 2000.89), (678, "Kinsella Coffee Table with Storage", "4876324581", 1, 202.34), (907, "Jared Hand-Tufted Wool Grey Area Rug", "4534590661", 1, 164.53), (478, "Boisvert Printed Damask Sheer Grommet Curtain Panel", "4681320789", 2, 59.76), (637, "Newtown Desk", "4693039385", 1, 738.82); INSERT INTO logs (order_id, log_created_at, raw_log) VALUES (123, '2021-07-05 02:46:10','order approved'), (456, '2021-07-05 13:07:36','order declined'), (456, '2021-07-05 13:08:11','email updated'), (456, '2021-07-05 13:10:10','order declined'), (567, '2021-07-06 05:30:10','order approved'), (567, '2021-07-06 05:39:10','shipping city updated'), (567, '2021-07-06 05:40:45','order approved'), (345, '2021-07-06 11:21:23','order declined'), (987, '2021-07-06 17:21:10','order declined'), (987, '2021-07-06 17:23:00','email updated'), (987, '2021-07-06 17:24:45','order approved'), (902, '2021-07-01 13:17:34','order declined'), (478, '2021-07-07 05:38:30','order approved'), (907, '2021-07-07 05:47:56','order approved'), (637, '2021-07-08 06:24:10','order declined'); select * from orders o full outer join products p on p.order_id = o.order_id where o.order_id is null or p.order_id is null /* select l.* from orders o join ( select order_id ,raw_log ,rank() over(partition by order_id order by log_created_at desc) rnk from logs where raw_log like '%approved%' or raw_log like '%declined%' ) l on l.order_id = o.order_id and l.rnk = 1 */
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear